尝试在 SQL Server 2016 中查询 GeoJson 多多边形时出现问题

Question

我正在尝试对GeoJson多多边形数据运行查询(我从这里收集到的)。它处理许多空间记录，但在某些空间记录上停止。

我在查询消息中收到以下错误：

Msg 6522，级别 16，状态 1，第 10 行
执行用户定义的例程或聚合"地理"期间发生 .NET Framework 错误：
System.FormatException： 24142：位置 15 处的预期"("。输入具有"，"。System.FormatException：at Microsoft.SqlServer.Types.WellKnownTextReader.RecognizeToken(Char token) at Microsoft.SqlServer.Types.WellKnownTextReader.ParseLineStringText()at Microsoft.SqlServer.Types.WellKnownTextReader.ParsePolygonText()at
Microsoft.SqlServer.Types.WellKnownTextReader.ParseMultiPolygonText(


)
at Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type)
at Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type， Int32 srid)at Microsoft.SqlServer.Types.SqlGeography.ParseText(OpenGisType type， SqlChars taggedText， Int32 srid)at Microsoft.SqlServer.Types.SqlGeography.GeographyFromText(OpenGisType type， SqlChars taggedText， Int32 srid)

这是我的陈述和一些测试数据：

declare @CountiesGeoJson nvarchar(max) = '{"type":"FeatureCollection","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": "620", "COUNTYNS": "01498424", "GEOID": "51620", "NAME": "Franklin" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-76.963021, 36.704696], [-76.912221, 36.698408], [-76.909672, 36.648175], [-76.962339, 36.667515], [-76.949327, 36.67873], [-76.968027, 36.682971], [-76.963021, 36.704696]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "595", "COUNTYNS": "01498422", "GEOID": "51595", "NAME": "Emporia" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.557359, 36.713166], [-77.510273, 36.718229], [-77.509246, 36.682998], [-77.556524, 36.674874], [-77.557359, 36.713166]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "670", "COUNTYNS": "01498428", "GEOID": "51670", "NAME": "Hopewell" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.334549, 37.312468], [-77.273379, 37.316116], [-77.253234, 37.295668], [-77.31081, 37.261451], [-77.328291, 37.271748], [-77.334549, 37.312468]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "510", "COUNTYNS": "01498415", "GEOID": "51510", "NAME": "Alexandria" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.140872, 38.826905], [-77.110799, 38.843446], [-77.087805, 38.827357], [-77.085609, 38.84419], [-77.044487611898006, 38.839598699716696], [-77.040372554526996, 38.785355394851003], [-77.143135, 38.805321], [-77.140872, 38.826905]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "059", "COUNTYNS": "01480119", "GEOID": "51059", "NAME": "Fairfax" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.53576, 38.847474], [-77.32828109319901, 39.057794871536501], [-77.246003, 39.024909], [-77.255703, 39.002409], [-77.234803, 38.97631], [-77.148179, 38.965002], [-77.119759, 38.934343], [-77.172276, 38.893245], [-77.194712, 38.899073], [-77.194234, 38.886146], [-77.149701, 38.87567], [-77.110799, 38.843446], [-77.140872, 38.826905], [-77.143135, 38.805321], [-77.040372554526996, 38.785355394851003], [-77.042298, 38.718515], [-77.122001, 38.685816], [-77.1302, 38.635017], [-77.204302, 38.617817], [-77.277032, 38.695026], [-77.355884, 38.725425], [-77.370878, 38.710433], [-77.442956, 38.803796], [-77.492801, 38.797821], [-77.50834, 38.841032], [-77.53576, 38.847474]], [[-77.334852, 38.853737], [-77.318689, 38.834651], [-77.27052, 38.840902], [-77.270597, 38.866075], [-77.334852, 38.853737]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "680", "COUNTYNS": "01498429", "GEOID": "51680", "NAME": "Lynchburg" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-79.26745, 37.44744], [-79.183302, 37.465908], [-79.120451, 37.398311], [-79.085244, 37.394743], [-79.214924, 37.33258], [-79.257188, 37.355608], [-79.231933, 37.394911], [-79.256628, 37.406573], [-79.244548, 37.419941], [-79.268727, 37.431028], [-79.26745, 37.44744]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "037", "COUNTYNS": "01492442", "GEOID": "51037", "NAME": "Charlotte" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-78.902227, 37.026799], [-78.824209, 37.205361], [-78.808198, 37.201852], [-78.778353, 37.228639], [-78.734351, 37.220256], [-78.681477, 37.24969], [-78.692432, 37.203673], [-78.644621, 37.194323], [-78.634927, 37.170064], [-78.583642, 37.140635], [-78.547128, 37.150431], [-78.480891, 37.085196], [-78.443644, 37.079371], [-78.493028, 36.89122], [-78.648541, 36.697909], [-78.691328, 36.741847], [-78.666145, 36.764363], [-78.682652, 36.797222], [-78.666995, 36.811822], [-78.686878, 36.824331], [-78.666125, 36.840482], [-78.671463, 36.857951], [-78.749727, 36.920065], [-78.727686, 36.937454], [-78.743891, 37.005439], [-78.756549, 37.00717], [-78.748358, 36.987057], [-78.774715, 36.980976], [-78.785452, 36.952783], [-78.817455, 36.98402], [-78.892702, 36.976694], [-78.902227, 37.026799]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "099", "COUNTYNS": "01480137", "GEOID": "51099", "NAME": "King George" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.330384, 38.334951], [-77.286201505089011, 38.347024428844499], [-77.240072, 38.331598], [-77.17934, 38.341915], [-77.138224, 38.367917], [-77.08481, 38.368297], [-77.043526, 38.400548], [-77.011827, 38.374554], [-77.030683, 38.311623], [-76.99767, 38.278047], [-77.044799, 38.260407], [-77.048482, 38.182444], [-77.073796, 38.141331], [-77.084596, 38.160231], [-77.115997, 38.149931], [-77.2143, 38.197029], [-77.254001, 38.18943], [-77.242001, 38.210729], [-77.251801, 38.229628], [-77.229757, 38.245208], [-77.275302, 38.247528], [-77.291402, 38.223729], [-77.290302, 38.238728], [-77.309419, 38.234737], [-77.345728, 38.26139], [-77.330384, 38.334951]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "161", "COUNTYNS": "01480165", "GEOID": "51161", "NAME": "Roanoke" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-80.261984, 37.340694], [-80.200161, 37.378568], [-80.147625, 37.382342], [-80.073985, 37.422305], [-79.865099, 37.328417], [-79.840367, 37.302681], [-79.858058, 37.267138], [-79.847082, 37.225468], [-79.962301, 37.137099], [-80.030562, 37.17481], [-80.088553, 37.160482], [-80.128119, 37.122912], [-80.172264, 37.108156], [-80.189835, 37.233476], [-80.25852, 37.308344], [-80.261984, 37.340694]], [[-80.088062, 37.282334], [-80.050761, 37.252635], [-80.033464, 37.26289], [-80.02883, 37.24365], [-79.998113, 37.252193], [-79.950384, 37.211274], [-79.924496, 37.224634], [-79.900048, 37.257297], [-79.909493, 37.278054], [-79.879276, 37.304047], [-79.974023, 37.335419], [-80.022226, 37.308338], [-80.033596, 37.324293], [-80.12049, 37.287053], [-80.088062, 37.282334]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "135", "COUNTYNS": "01493928", "GEOID": "51135", "NAME": "Nottoway" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-78.238161, 37.194822], [-78.231234, 37.29622], [-77.795926, 37.192592], [-77.805982, 37.175933], [-77.900254, 37.143885], [-77.889813, 36.988818], [-77.932749, 36.984246], [-77.968114, 36.987292], [-78.003639, 37.022756], [-78.082023, 37.011121], [-78.172207, 37.055963], [-78.170716, 37.076188], [-78.224286, 37.089685], [-78.239354, 37.120089], [-78.238161, 37.194822]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "683", "COUNTYNS": "01498430", "GEOID": "51683", "NAME": "Manassas" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.526457, 38.732466], [-77.475711, 38.781214], [-77.44751, 38.761115], [-77.451642, 38.738337], [-77.471425, 38.728152], [-77.507299, 38.740479], [-77.506167, 38.705741], [-77.526457, 38.732466]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "630", "COUNTYNS": "01498425", "GEOID": "51630", "NAME": "Fredericksburg" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.530283, 38.309175], [-77.506782, 38.325925], [-77.467053, 38.31866], [-77.447126, 38.284614], [-77.457837, 38.271108], [-77.492045, 38.273637], [-77.530283, 38.309175]]]] } },
{ "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]]]] } },
]}';
--DECLARE @@CountiesGeoJson nvarchar(max)
--SELECT @@CountiesGeoJson = BulkColumn
--FROM OPENROWSET
--(BULK 'C:MapDatacountiesEdited.json', SINGLE_CLOB)
--as JSON
--Insert Into [Tox].[dbo].[GeoCounties] (GEOID, GEO_NAME, FIPS_ST_CD, FIPS_CNTY_CD, RESOLUTION_ACCURACY, GEO_OBJECT)
select GEOID, GNAME, STATEFP, COUNTYFP, 10, 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
cross 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]
) 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 LEN(Path) = 0
) MultigonData
cross apply (
SELECT concat(upper(GeoData.[type]),'((',IsNull(NULLIF(path,''),multi),'))') WKT
) shapeDef
outer apply (
select geography::STGeomFromText(WKT,4326).MakeValid()/*.ReorientObject()*/ as geom
) geography
outer apply (
select CASE WHEN geom.EnvelopeAngle() > 90 THEN geom.ReorientObject() ELSE geom END as fixed
) fixes

查询将运行并生成一些结果，但是当它命中 NAME = 'Fairfax' 和 'Roanoke' 的记录时，它会因上述错误而停止。

任何人都可以看到问题并知道如何解决这个问题吗？

[编辑2019-05-31 17：35] GeoJson 标头：

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

Answer 1

感谢@Hank接受此查询！ @Vitaly-鲍里索夫强调了具体的违规几何形状，这对解决问题有很大帮助。

在原始查询中，有一个CROSS APPLY查询，用于创建名为PolygonData的结果集，紧随其后的是创建名为MultigonData的结果集的OUTER APLY。

请注意，MultigData 只有结果WHERE LEN(Path) = 0
此处的期望是所有非多边形几何都将为Path生成空值，因此我们仅在几何确实是多多边形类型时才尝试解析多多边形 WKT。

此查询仅支持多边形和多多边形类型的几何，这是一个足够广泛的假设，适合当时。

与其使用解析多边形失败(WHERE LEN(Path) = 0)来确定几何体实际上是多多边形，我们可以使用类型定义本身！

更新了查询，请注意PolygonData和MutigonData查询中的 where 子句以及在shapeDef
中使用COALESCE这些细微的更改应该可以更轻松地将此逻辑用于其他地理类型。

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": "620", "COUNTYNS": "01498424", "GEOID": "51620", "NAME": "Franklin" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-76.963021, 36.704696], [-76.912221, 36.698408], [-76.909672, 36.648175], [-76.962339, 36.667515], [-76.949327, 36.67873], [-76.968027, 36.682971], [-76.963021, 36.704696]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "595", "COUNTYNS": "01498422", "GEOID": "51595", "NAME": "Emporia" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.557359, 36.713166], [-77.510273, 36.718229], [-77.509246, 36.682998], [-77.556524, 36.674874], [-77.557359, 36.713166]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "670", "COUNTYNS": "01498428", "GEOID": "51670", "NAME": "Hopewell" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.334549, 37.312468], [-77.273379, 37.316116], [-77.253234, 37.295668], [-77.31081, 37.261451], [-77.328291, 37.271748], [-77.334549, 37.312468]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "510", "COUNTYNS": "01498415", "GEOID": "51510", "NAME": "Alexandria" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.140872, 38.826905], [-77.110799, 38.843446], [-77.087805, 38.827357], [-77.085609, 38.84419], [-77.044487611898006, 38.839598699716696], [-77.040372554526996, 38.785355394851003], [-77.143135, 38.805321], [-77.140872, 38.826905]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "059", "COUNTYNS": "01480119", "GEOID": "51059", "NAME": "Fairfax" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.53576, 38.847474], [-77.32828109319901, 39.057794871536501], [-77.246003, 39.024909], [-77.255703, 39.002409], [-77.234803, 38.97631], [-77.148179, 38.965002], [-77.119759, 38.934343], [-77.172276, 38.893245], [-77.194712, 38.899073], [-77.194234, 38.886146], [-77.149701, 38.87567], [-77.110799, 38.843446], [-77.140872, 38.826905], [-77.143135, 38.805321], [-77.040372554526996, 38.785355394851003], [-77.042298, 38.718515], [-77.122001, 38.685816], [-77.1302, 38.635017], [-77.204302, 38.617817], [-77.277032, 38.695026], [-77.355884, 38.725425], [-77.370878, 38.710433], [-77.442956, 38.803796], [-77.492801, 38.797821], [-77.50834, 38.841032], [-77.53576, 38.847474]], [[-77.334852, 38.853737], [-77.318689, 38.834651], [-77.27052, 38.840902], [-77.270597, 38.866075], [-77.334852, 38.853737]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "680", "COUNTYNS": "01498429", "GEOID": "51680", "NAME": "Lynchburg" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-79.26745, 37.44744], [-79.183302, 37.465908], [-79.120451, 37.398311], [-79.085244, 37.394743], [-79.214924, 37.33258], [-79.257188, 37.355608], [-79.231933, 37.394911], [-79.256628, 37.406573], [-79.244548, 37.419941], [-79.268727, 37.431028], [-79.26745, 37.44744]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "037", "COUNTYNS": "01492442", "GEOID": "51037", "NAME": "Charlotte" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-78.902227, 37.026799], [-78.824209, 37.205361], [-78.808198, 37.201852], [-78.778353, 37.228639], [-78.734351, 37.220256], [-78.681477, 37.24969], [-78.692432, 37.203673], [-78.644621, 37.194323], [-78.634927, 37.170064], [-78.583642, 37.140635], [-78.547128, 37.150431], [-78.480891, 37.085196], [-78.443644, 37.079371], [-78.493028, 36.89122], [-78.648541, 36.697909], [-78.691328, 36.741847], [-78.666145, 36.764363], [-78.682652, 36.797222], [-78.666995, 36.811822], [-78.686878, 36.824331], [-78.666125, 36.840482], [-78.671463, 36.857951], [-78.749727, 36.920065], [-78.727686, 36.937454], [-78.743891, 37.005439], [-78.756549, 37.00717], [-78.748358, 36.987057], [-78.774715, 36.980976], [-78.785452, 36.952783], [-78.817455, 36.98402], [-78.892702, 36.976694], [-78.902227, 37.026799]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "099", "COUNTYNS": "01480137", "GEOID": "51099", "NAME": "King George" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.330384, 38.334951], [-77.286201505089011, 38.347024428844499], [-77.240072, 38.331598], [-77.17934, 38.341915], [-77.138224, 38.367917], [-77.08481, 38.368297], [-77.043526, 38.400548], [-77.011827, 38.374554], [-77.030683, 38.311623], [-76.99767, 38.278047], [-77.044799, 38.260407], [-77.048482, 38.182444], [-77.073796, 38.141331], [-77.084596, 38.160231], [-77.115997, 38.149931], [-77.2143, 38.197029], [-77.254001, 38.18943], [-77.242001, 38.210729], [-77.251801, 38.229628], [-77.229757, 38.245208], [-77.275302, 38.247528], [-77.291402, 38.223729], [-77.290302, 38.238728], [-77.309419, 38.234737], [-77.345728, 38.26139], [-77.330384, 38.334951]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "161", "COUNTYNS": "01480165", "GEOID": "51161", "NAME": "Roanoke" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-80.261984, 37.340694], [-80.200161, 37.378568], [-80.147625, 37.382342], [-80.073985, 37.422305], [-79.865099, 37.328417], [-79.840367, 37.302681], [-79.858058, 37.267138], [-79.847082, 37.225468], [-79.962301, 37.137099], [-80.030562, 37.17481], [-80.088553, 37.160482], [-80.128119, 37.122912], [-80.172264, 37.108156], [-80.189835, 37.233476], [-80.25852, 37.308344], [-80.261984, 37.340694]], [[-80.088062, 37.282334], [-80.050761, 37.252635], [-80.033464, 37.26289], [-80.02883, 37.24365], [-79.998113, 37.252193], [-79.950384, 37.211274], [-79.924496, 37.224634], [-79.900048, 37.257297], [-79.909493, 37.278054], [-79.879276, 37.304047], [-79.974023, 37.335419], [-80.022226, 37.308338], [-80.033596, 37.324293], [-80.12049, 37.287053], [-80.088062, 37.282334]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "135", "COUNTYNS": "01493928", "GEOID": "51135", "NAME": "Nottoway" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-78.238161, 37.194822], [-78.231234, 37.29622], [-77.795926, 37.192592], [-77.805982, 37.175933], [-77.900254, 37.143885], [-77.889813, 36.988818], [-77.932749, 36.984246], [-77.968114, 36.987292], [-78.003639, 37.022756], [-78.082023, 37.011121], [-78.172207, 37.055963], [-78.170716, 37.076188], [-78.224286, 37.089685], [-78.239354, 37.120089], [-78.238161, 37.194822]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "683", "COUNTYNS": "01498430", "GEOID": "51683", "NAME": "Manassas" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.526457, 38.732466], [-77.475711, 38.781214], [-77.44751, 38.761115], [-77.451642, 38.738337], [-77.471425, 38.728152], [-77.507299, 38.740479], [-77.506167, 38.705741], [-77.526457, 38.732466]]]] } },
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "630", "COUNTYNS": "01498425", "GEOID": "51630", "NAME": "Fredericksburg" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.530283, 38.309175], [-77.506782, 38.325925], [-77.467053, 38.31866], [-77.447126, 38.284614], [-77.457837, 38.271108], [-77.492045, 38.273637], [-77.530283, 38.309175]]]] } },
{ "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]]]] } },
]}';
--DECLARE @@CountiesGeoJson nvarchar(max)
--SELECT @@CountiesGeoJson = BulkColumn
--FROM OPENROWSET
--(BULK 'C:MapDatacountiesEdited.json', SINGLE_CLOB)
--as JSON
--Insert Into [Tox].[dbo].[GeoCounties] (GEOID, GEO_NAME, FIPS_ST_CD, FIPS_CNTY_CD, RESOLUTION_ACCURACY, GEO_OBJECT)
select GEOID, GNAME, STATEFP, COUNTYFP, 10, 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
-- Extract the SRID from the feature collection header.
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

更新：功能集合标头已修改以匹配 OP.SRID
现在来自功能集合标头，并且不是硬编码的

Answer 2

我将测试数据简化为：

{"type":"FeatureCollection","features":[
{ "type": "Feature", "properties": { "STATEFP": "51", "COUNTYFP": "059", "COUNTYNS": "01480119", "GEOID": "51059", "NAME": "Fairfax" }, "geometry": { "type": "MultiPolygon", "coordinates": [[[[-77.53576, 38.847474], [-77.32828109319901, 39.057794871536501], [-77.246003, 39.024909], [-77.255703, 39.002409], [-77.234803, 38.97631], [-77.148179, 38.965002], [-77.119759, 38.934343], [-77.172276, 38.893245], [-77.194712, 38.899073], [-77.194234, 38.886146], [-77.149701, 38.87567], [-77.110799, 38.843446], [-77.140872, 38.826905], [-77.143135, 38.805321], [-77.040372554526996, 38.785355394851003], [-77.042298, 38.718515], [-77.122001, 38.685816], [-77.1302, 38.635017], [-77.204302, 38.617817], [-77.277032, 38.695026], [-77.355884, 38.725425], [-77.370878, 38.710433], [-77.442956, 38.803796], [-77.492801, 38.797821], [-77.50834, 38.841032], [-77.53576, 38.847474]], [[-77.334852, 38.853737], [-77.318689, 38.834651], [-77.27052, 38.840902], [-77.270597, 38.866075], [-77.334852, 38.853737]]]] } }
]}

我已经注释掉了最后一个外部应用并选择 WKT 以查看其中的内容。

SELECT GEOID, GNAME, STATEFP, COUNTYFP, 10--, Geo=fixed
,WKT

WKT返回：

MULTIPOLYGON(( ,   ))

所以"geography：：STGeomFromText(WKT，4326)。MakeValid()" 无法根据此数据构建几何图形，并因错误而失败。

Answer 3

失败的多重多边形是包含孔的多边形。您可以通过搜索来检测它们]],.

多面是多边形的集合。它们中的每一个都是循环的集合 - 第一个循环是多边形壳，其他循环是孔。每个循环都是点的集合。

该脚本试图通过解压缩这些数组，然后使用 WKT 的(和)将它们连接起来，将 GeoJSON 转换为 WKT，但无法处理带有孔大小写的多边形。我怀疑该错误在Poly.value- 这不处理数组大小写，但我不是 OPENJSON 专家。

我想有可能继续使这个复杂的SQL变得更加复杂，并处理漏洞情况，但我认为使用真正的GeoJSON解析器和WKT格式化程序将GeoJSON转换为WKT字符串更实用，例如ogr2ogr工具。