GeoJSON是GIS行业里一种常见的数据交换格式，能够存储结构化的空间地理信息。因为SQL Server从2008版开始提供了空间数据类型geometry与geography的支持，所以我也试着将项目中用到的地图数据转换到数据库中，方便之后的调用。因为中途遇到了不少坑，所以写了这篇文章作为备忘。
在转换前，我们需要对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), ObjectIdfrom 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=fixedfrom 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 GeoDataOUTER 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') PolygonDataOUTER 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') MultigonDatacross apply ( SELECT concat(upper(GeoData.[type]),'((',COALESCE(PolygonData.path, MultigonData.multi),'))') WKT) shapeDefouter apply ( select ID = Substring(name, CharIndex('::', name) + 2, LEN(name) - CharIndex('::', name)) from openjson (@CountiesGeoJson, '$.crs.properties') with ( name varchar(100) '$.name')) SRIDouter apply ( select geography::STGeomFromText(WKT,IsNull(SRID.ID, 4326)).MakeValid()/*.ReorientObject()*/ as geom) geographyouter apply ( select CASE WHEN geom.EnvelopeAngle() > 90 THEN geom.ReorientObject() ELSE geom END as fixed) fixes
