空间数据类型(几何)到GeoJSON [英] Spatial Datatype (geometry) to GeoJSON
本文介绍了空间数据类型(几何)到GeoJSON的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将 geom ( geometry )数据类型转换为GeoJSON.我该怎么办?
I want to convert geom (geometry) datatype to GeoJSON. How could I do that?
例如，WKT中的几何:
For example, the geometry in WKT:
POLYGON((455216.346127297 4288433.28426224,455203.386722146 4288427.76317716,455207.791765017 4288417.51116228,455220.784166744 4288423.30230044,455216.346127297 4288433.28426224))
到以下GeoJSON:
To the following GeoJSON:
{ "type": "Polygon",
"coordinates": [
[ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0] ],
[ [100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2] ]
]
}
推荐答案
我也有同样的需求.我们有一个大型数据库，其中包含SQL Server中带有GEOMETRY列的表.我认为，从包含GeoJson的存储过程中获取单个字符串对象将是更可取的.我编写了一个将几何实例作为对象并返回GeoJson字符串的函数.
I had this same need. We have a large database containing tables with GEOMETRY columns in SQL Server. I felt that it would be more desirable to be able to get a single string object as a result from a stored proceedure that would contain the GeoJson. I wrote a function that takes a geometry instance as an object and returns a GeoJson string.
CREATE FUNCTION [dbo].[geomToGeoJSON] (@geom GEOMETRY)
RETURNS VARCHAR(MAX)
AS
BEGIN
-- Declare the return variable here
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
-- fix bad geometries and enforce ring orientation
SET @geom = @geom.STUnion(@geom.STPointN(1)).MakeValid()
-- Point ----------------------------
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
-- MultiPoint ---------------------------------------------
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
-- LineString ---------------------------------------------
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
-- MultiLineString ---------------------------------------------
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
-- Polygon ---------------------------------------------
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 the ring orientation so that they are consistent
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
-- MultiPolygon ---------------------------------------------
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()
-- add the exterior ring points to the json
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()
-- add any internal ring points to the json
WHILE @ringCounter <= @numRing
BEGIN
SET @geoJSON += ',['
SET @intRing = @Ngeom.STInteriorRingN(@ringCounter)
-- make sure the ring orientation is the same every time
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对象:
Then I can either just select an individual GeoJSON object like this:
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中:
Or I can package an entire set of objects into a FeatureCollection like this:
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
查询性能取决于几何的复杂性和数量，但是我得到的结果通常是十分之二秒.
Query performance depends on the complexity and number of geometries, but I typically get a result in ~2 tenths of a second.
我已经通过使用MSDN中的示例几何进行了验证，然后将生成的GeoJSON输入到 http://geojsonlint.com/.我知道这已经一岁了，但是我仍然有需要，我怀疑没有地图服务器的人都可以使用像这样的东西在Bing Maps上绘制图层等来生成自己的简单地图服务器.
I have validated by using example geometries from MSDN and then entering the resulting GeoJSON into http://geojsonlint.com/. I know this is a year old but I still have a need and I suspect anyone without a mapserver could generate their own simple mapserver using something like this to draw layers on Bing Maps, etc.
这篇关于空间数据类型(几何)到GeoJSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
