gpt4 book ai didi

sql-server - 空间数据类型(几何)到 GeoJSON

转载 作者:行者123 更新时间:2023-12-02 22:18:45 25 4
gpt4 key购买 nike

我想将 geom (geometry) 数据类型转换为 GeoJSON。我怎样才能做到这一点?

例如WKT中的几何图形:

    POLYGON((455216.346127297 4288433.28426224,455203.386722146 4288427.76317716,455207.791765017 4288417.51116228,455220.784166744 4288423.30230044,455216.346127297 4288433.28426224))

到以下 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 字符串。

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 对象,如下所示:

    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

查询性能取决于几何图形的复杂性和数量,但我通常会在大约十分之二秒内得到结果。

我已经通过使用 MSDN 中的示例几何图形进行了验证,然后将生成的 GeoJSON 输入到 http://geojsonlint.com/ 中。 。我知道这已经有一年了,但我仍然有需要,我怀疑没有 map 服务器的任何人都可以使用类似的东西生成自己的简单 map 服务器,以在 Bing map 等上绘制图层。

关于sql-server - 空间数据类型(几何)到 GeoJSON,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36141323/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com