geojson 导入mysql_导入GeoJSON数据到SQL Server数据库中

导入GeoJSON数据到SQL Server数据库中

GeoJSON是GIS行业里一种常见的数据交换格式，能够存储结构化的空间地理信息。因为SQL Server从2008版开始提供了空间数据类型geometry与geography的支持，所以我也试着将项目中用到的地图数据转换到数据库中，方便之后的调用。因为中途遇到了不少坑，所以写了这篇文章作为备忘。

事前准备：了解GeoJSON与SQL Server的空间数据结构

在转换前，我们需要对GeoJSON与SQL Server的空间数据结构有所了解。

下面给出了一篇GeoJSON文档的范例。可以看到，GeoJSON就本质而言其实是我们常见的JSON格式的一种变体，只不过由于语言规范的限制，GeoJSON的结构相对固定。一个完整的GeoJSON就是一个包含类型信息type、以及对象集合features两个成员的Javascript对象，主要空间地理数据存储在这个对象features集合下的geometry、properties成员里。因此要将GeoJSON导入到SQL Server中，其实就是把geometry、properties两个成员里的有效信息解析出来，插入到数据库表中

{"type":"FeatureCollection",

"features":[{"type":"Feature",

"id":"56679924",

"geometry":{"type":"Point",

"coordinates":[-77.0592213018017,38.90222845310455]},

"properties":{"OBJECTID":56679924,"ID":72,

"ADDRESS":"Georgetown Harbor / 30th St NW",

"TERMINAL_NUMBER":"31215",

"LATITUDE":38.902221,"LONGITUDE":-77.059219,

"INSTALLED":"YES","LOCKED":"NO",

"INSTALL_DATE":"2010-10-05T13:43:00.000Z",

"REMOVAL_DATE":null,

"TEMPORARY_INSTALL":"NO",

"NUMBER_OF_BIKES":15,

"NUMBER_OF_EMPTY_DOCKS":4,

"X":394863.27537199,"Y":137153.4794371,

"SE_ANNO_CAD_DATA":null}

}]

针对空间地理数据，MSSQL提供了geometry和geography两种数据存储类型，都能够支持Point、 MultiPoint、 LineString、 CircularString、 MultiLineString、 CompoundCurve、 Polygon、 CurvePolygon、 MultiPolygon等常用的空间数据类型。这两者非常相似，主要区别在于geometry采用(欧几里得)平面坐标系，geography采用地理坐标系。我们要导入的数据如果是投影在平面上的，应该存储在geometry类型里，而 GPS经纬度之类的椭圆体数据应存储于geography类型下。

插入点数据

MSSQL从2016版开始正式原生支持JSON格式，所以如今我们可以很方便地利用openjson函数把GeoJSON导入到数据库中。下面这块代码就从JSON中解析出了bikeShares中的点数据

declare @bikeShares nvarchar(max) =

'{"type":"FeatureCollection",

"features":[{"type":"Feature",

"id":"56679924",

"geometry":{"type":"Point",

"coordinates":[-77.0592213018017,38.90222845310455]},

"properties":{"OBJECTID":56679924,"ID":72,

"ADDRESS":"Georgetown Harbor / 30th St NW",

"X":394863.27537199,"Y":137153.4794371,

"SE_ANNO_CAD_DATA":null}

}]}'

SELECT geography::STGeomFromText('POINT ('+long + ' ' + lat + ')', 4326),

ObjectId

from OPENJSON(@bikeShares, '$.features')

WITH (

long varchar(100) '$.geometry.coordinates[0]',

lat varchar(100) '$.geometry.coordinates[1]',

ObjectId int '$.properties.OBJECTID',

Address nvarchar(200) '$.properties.ADDRESS'

)

代码先从JSON中读取了点的经纬度，然后将他们组合为‘POINT (‘+long + ‘ ‘ + lat + ‘)‘，再用geography::STGeomFromText方法转化到EPSG4326球型坐标系下

插入线与面数据

线和面数据的解析因为结构问题，要复杂很多，琢磨了很久还没搞定，幸好在stackoverflow上有相关的问题，抄作业了。

declare @CountiesGeoJson nvarchar(max) = '{ "type": "FeatureCollection", "name": "USCounty_Simplify_01",

"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::4269" } }, "features": [

{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "700", "COUNTYNS": "01498555", "GEOID": "51700", "NAME": "Newport News" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-76.622252, 37.142146], [-76.577819, 37.187025], [-76.591432, 37.214721], [-76.565092, 37.220645], [-76.458516, 37.100661], [-76.435519, 37.094882], [-76.451274, 37.076878], [-76.442269, 37.018448], [-76.424757, 37.025107], [-76.387711304409194, 36.989671332859004], [-76.411768, 36.962847], [-76.428869, 36.969947], [-76.464471, 37.027547], [-76.518242, 37.055351], [-76.536875, 37.083942], [-76.564219, 37.077507], [-76.618252, 37.119347], [-76.622252, 37.142146]]]] } },

{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "610", "COUNTYNS": "01498423", "GEOID": "51610", "NAME": "Falls Church" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.194712, 38.899073], [-77.172276, 38.893245], [-77.149701, 38.87567], [-77.189719, 38.87801], [-77.194712, 38.899073]]]] } }

]}';

select GEOID, GNAME, STATEFP, COUNTYFP, Geo=fixed

from openjson (@CountiesGeoJson, '$.features')

with

(

GEOID char(5) '$.properties.GEOID',

GNAME varchar(40) '$.properties.NAME',

STATEFP char(2) '$.properties.STATEFP',

COUNTYFP char(3) '$.properties.COUNTYFP',

[type] Varchar(64) '$.geometry.type',

[coordinates] nvarchar(max) '$.geometry.coordinates' as json

)

as GeoData

OUTER APPLY (

select

stuff(

(

select concat(', ', json_value(Value,'$[0]'),' ',json_value(Value,'$[1]'))

from openjson(GeoData.coordinates,'$[0]')

order by cast([key] as int)

for xml path('')

),1,3,'') [path]

WHERE GeoData.[type] = 'Polygon'

) PolygonData

OUTER APPLY (

SELECT STUFF(

(

SELECT CONCAT(', ', polygon)

FROM OPENJSON(GeoData.coordinates) as Poly

CROSS APPLY OPENJSON(Poly.value) as Shape

CROSS APPLY (

SELECT '(' + stuff(

(

select concat(', ', json_value(Value,'$[0]'),' ',json_value(Value,'$[1]'))

from OPENJSON(Shape.value)

order by cast([key] as int)

for xml path('')

),1,3,'')+')' polygon

) Polygons

for xml path('')

),1,3,'') multi

WHERE GeoData.[type] = 'MultiPolygon'

) MultigonData

cross apply (

SELECT concat(upper(GeoData.[type]),'((',COALESCE(PolygonData.path, MultigonData.multi),'))') WKT

) shapeDef

outer apply (

select ID = Substring(name, CharIndex('::', name) + 2, LEN(name) - CharIndex('::', name)) from openjson (@CountiesGeoJson, '$.crs.properties')

with ( name varchar(100) '$.name')

) SRID

outer apply (

select geography::STGeomFromText(WKT,IsNull(SRID.ID, 4326)).MakeValid()/*.ReorientObject()*/ as geom

) geography

outer apply (

select CASE WHEN geom.EnvelopeAngle() > 90 THEN geom.ReorientObject() ELSE geom END as fixed

) fixes

资料来源

https://docs.microsoft.com/zh-cn/archive/blogs/sqlserverstorageengine/loading-geojson-data-into-sql-server

https://stackoverflow.com/questions/56371128/issue-on-trying-to-query-geojson-multipolygons-in-sql-server-2016