导入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
