我曾经有同样的需求。我们有一个包含SQL Server中GEOMETRY列的大型数据库。我认为,从存储过程中获取单个字符串对象作为结果并包含GeoJson将更加理想。我编写了一个函数,它将几何实例作为对象,并返回一个GeoJson字符串。
CREATE FUNCTION [dbo].[geomToGeoJSON] (@geom GEOMETRY)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @geoJSON VARCHAR(MAX)
DECLARE @Ngeom GEOMETRY
DECLARE @ptCounter INT
DECLARE @numPt INT
DECLARE @ringCounter INT
DECLARE @numRing INT
DECLARE @gCounter INT
DECLARE @numGeom INT
DECLARE @handled BIT = 0
DECLARE @extRing GEOMETRY
DECLARE @intRing GEOMETRY
SET @geom = @geom.STUnion(@geom.STPointN(1)).MakeValid()
IF (@geom.STGeometryType() = 'Point')
BEGIN
SET @geoJSON = '{ "type": "Point", "coordinates": [' + LTRIM(RTRIM(STR(@geom.STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STY, 38, 8))) + '] }'
SET @handled = 1
END
IF (
@handled = 0
AND @geom.STGeometryType() = 'MultiPoint'
)
BEGIN
SET @gCounter = 1
SET @numGeom = @geom.STNumGeometries()
SET @geoJSON = '{ "type": "MultiPoint", "coordinates": ['
WHILE @gCounter <= @numGeom
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@geom.STGeometryN(@gCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STGeometryN(@gCounter).STY, 38, 8))) + '], '
SET @gCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
SET @handled = 1
END
IF (
@handled = 0
AND @geom.STGeometryType() = 'LineString'
)
BEGIN
SET @ptCounter = 1
SET @numPt = @geom.STNumPoints()
SET @geoJSON = '{ "type": "LineString", "coordinates": ['
WHILE @ptCounter <= @numPt
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@geom.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@geom.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ' ] }'
SET @handled = 1
END
IF (
@handled = 0
AND @geom.STGeometryType() = 'MultiLineString'
)
BEGIN
SET @gCounter = 1
SET @numGeom = @geom.STNumGeometries()
SET @geoJSON = '{ "type": "MultiLineString", "coordinates": ['
WHILE @gCounter <= @numGeom
BEGIN
SET @Ngeom = @geom.STGeometryN(@gCounter)
SET @geoJSON += '['
SELECT
@ptCounter = 1
,@numPt = @Ngeom.STNumPoints()
WHILE @ptCounter <= @numPt
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@Ngeom.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@Ngeom.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '],'
SET @gCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
SET @handled = 1
END
IF (
@handled = 0
AND @geom.STGeometryType() = 'Polygon'
)
BEGIN
SET @extRing = @geom.STExteriorRing()
SET @geoJSON = '{ "type": "Polygon", "coordinates": [['
SELECT
@ptCounter = 1
,@numPt = @extRing.STNumPoints()
WHILE @ptCounter <= @numPt
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'
SET @ringCounter = 1
SET @numRing = @geom.STNumInteriorRing()
WHILE @ringCounter <= @numRing
BEGIN
SET @geoJSON += ',['
SET @intRing = @geom.STInteriorRingN(@ringCounter)
SET @intRing = @intRing.STUnion(@intRing.STPointN(1)).MakeValid()
SELECT
@ptCounter = @intRing.STNumPoints()
WHILE @ptCounter > 0
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter -= 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'
SET @ringCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']] }'
SET @handled = 1
END
IF (
@handled = 0
AND @geom.STGeometryType() = 'MultiPolygon'
)
BEGIN
SELECT
@gCounter = 1
,@numGeom = @geom.STNumGeometries()
SET @geoJSON = '{ "type": "MultiPolygon", "coordinates": ['
WHILE @gCounter <= @numGeom
BEGIN
SET @Ngeom = @geom.STGeometryN(@gCounter)
SET @extRing = @Ngeom.STExteriorRing()
SET @geoJSON += '[['
SELECT
@ptCounter = 1
,@numPt = @extRing.STNumPoints()
WHILE @ptCounter <= @numPt
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@extRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'
SET @ringCounter = 1
SET @numRing = @Ngeom.STNumInteriorRing()
WHILE @ringCounter <= @numRing
BEGIN
SET @geoJSON += ',['
SET @intRing = @Ngeom.STInteriorRingN(@ringCounter)
SET @intRing = @intRing.STUnion(@intRing.STPointN(1)).MakeValid()
SELECT
@ptCounter = @intRing.STNumPoints()
WHILE @ptCounter > 0
BEGIN
SET @geoJSON += '[' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STX, 38, 8))) + ', ' + LTRIM(RTRIM(STR(@intRing.STPointN(@ptCounter).STY, 38, 8))) + '], '
SET @ptCounter -= 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + ']'
SET @ringCounter += 1
END
SET @geoJSON += '],'
SET @gCounter += 1
END
SET @geoJSON = LEFT(@geoJSON, LEN(@geoJSON) - 1) + '] }'
SET @handled = 1
END
IF (@handled = 0)
BEGIN
SET @geoJSON = '{"type": "' + @geom.STGeometryType() + '", "coordinates": []}'
END
RETURN @geoJSON
END
然后我可以像这样选择单个的GeoJSON对象:
SELECT dbo.geomToGeoJSON(GEOMCOLNAME) FROM DB.gis.PARCEL WHERE PARCEL = 'R1525750900'
并且获得一个看起来像这样的结果。
{
"type": "Polygon",
"coordinates": [
[
[-116.27593761, 43.62939598],
[-116.27558219, 43.62939633],
[-116.27558253, 43.62955520],
[-116.27582493, 43.62955445],
[-116.27582534, 43.62963010],
[-116.27593893, 43.62962975],
[-116.27593761, 43.62939598]
]
]
}
或者我可以像这样将整个对象集打包成一个FeatureCollection:
DECLARE @GeoJSON VARCHAR(MAX)
SET @GeoJSON = '{"type": "FeatureCollection", "features": ['
SELECT
@GeoJSON += '{"type": "Feature", "geometry": ' + sde_apps.dbo.geomToGeoJSON(SHAPE) + ', "properties": { "Parcel": "' + PARCEL + '"}},'
FROM
db.gis.PARCEL
WHERE
SUBNM LIKE @subnm
SET @GeoJSON = LEFT(@GeoJSON, LEN(@GeoJSON) - 1) + ']}'
SELECT
@GeoJSON
查询性能取决于几何图形的复杂度和数量,但通常我可以在约0.2秒内得到结果。
我使用来自MSDN的示例几何图形进行验证,然后将生成的GeoJSON输入到http://geojsonlint.com/中进行验证。我知道这已经有一年了,但我仍然需要它,并且我认为任何没有地图服务器的人都可以使用类似这样的东西生成自己的简单地图服务器,以在Bing Maps等上绘制图层。