Проблема при попытке запросить мультиполигоны GeoJson в SQL Server 2016

Я сократил данные тестирования до:

{"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

ВКТ возвращает:

MULTIPOLYGON(( ,   ))

Таким образом, "geography::STGeomFromText(WKT,4326).MakeValid()" не может построить геометрию из этих данных и завершается с ошибкой.

Неудачные мультиполигоны — это те, которые содержат дыры. Вы можете обнаружить их, выполнив поиск ]],.

Мультиполигон — это набор полигонов. Каждый из них представляет собой набор петель - первая петля представляет собой полигональную оболочку, а остальные петли - это отверстия. Каждый цикл представляет собой набор очков.

Скрипт пытается преобразовать GeoJSON в WKT, распаковывая эти массивы, а затем объединяя их, используя WKT ( и ), но не может обработать полигон с дырками. Я подозреваю, что ошибка в Poly.value - это не обрабатывает регистр массива, но я не эксперт OPENJSON.

Я предполагаю, что можно продолжать делать этот сложный SQL еще более сложным, а также обрабатывать случай с дырами, но я думаю, что более практично преобразовывать строки GeoJSON в строки WKT, используя что-то с реальным парсером GeoJSON и средством форматирования WKT, например. инструмент ogr2ogr.

Спасибо @Hank за то, что взялся за этот запрос! @Виталий-Борисов выделил конкретную оскорбительную геометрию, это очень помогло в решении проблемы.

В исходном запросе есть запрос CROSS APPLY, который создает набор результатов с именем PolygonData, сразу после него идет запрос OUTER APLY, создающий набор результатов с именем MultigonData.

Обратите внимание, что у MultigonData есть только результаты WHERE LEN(Path) = 0
Здесь ожидалось, что вся неполигональная геометрия даст нулевое значение для Path, поэтому мы пытаемся разрешить MultiPolygon WKT только в том случае, если геометрия действительно имеет тип MultiPolygon.

This query only supports Polygon and MultiPolygon types of geometry, it was a broad enough assumption that suited at that time.

Instead of using the failure to resolve a Polygon (WHERE LEN(Path) = 0) to determine that the geometry is in fact a MultiPolygon, we can use the type definition itself!

Обновленный запрос, обратите внимание на предложения where в запросах PolygonData и MutigonData и использование COALESCE в shapeDef
Эти небольшие изменения должны упростить применение этой логики для других типов географии.

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:\MapData\countiesEdited.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

UPDATE: The feature collection header has been modified to match OP.
SRID now comes from the feature collection header and is not hard coded