Hank
Reputation: 2616
Issue on trying to query GeoJson multipolygons in SQL Server 2016
I am trying to run a query (which I gleened from here) against GeoJson multipolygon data. It processes many of the spatial records but is stopping on a some.
I get the following error in the query messages:
Msg 6522, Level 16, State 1, Line 10
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException: 24142: Expected "(" at position 15. The input has ",".
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)
This is my statement with some test data:
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:\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
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
The query will run and produce some results but when it hits records where the NAME = 'Fairfax' and 'Roanoke', it stops with the above error.
Can anyone see the issue and know how to resolve this?
[EDIT 2019-05-31 17:35] GeoJson Header:
{
"type": "FeatureCollection",
"name": "USCounty_Simplify_01",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:EPSG::4269" } },
"features": [
...
Upvotes: 4
Views: 2726
Answers (3)
Chris Schaller
Reputation: 16554
Thanks @Hank for taking this query for a spin! @Vitaly-Borisov has highlighted the specific offending geometry, this has helped immensely in solving the issue.
In the original Query, there is a CROSS APPLY query that creates a result set called PolygonData, immediately after that is an OUTER APLY that creates a result set called MultigonData.
Notice that MultigonData only has results WHERE LEN(Path) = 0
The expectation here was that all non polygon geometry would yield a null value for Path and so we only attempt to resolve the MultiPolygon WKT if the geometry was indeed a MultiPolygon type.
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!
Updated Query, notice the where clauses within the PolygonData and MutigonData queries and the use of COALESCE in shapeDef
These minor changes should make it easier to adopt this logic for other geography types.
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
Upvotes: 5
Michael Entin
Reputation: 7724
The failing multipolygons are those that contain holes. You can detect them by searching for ]],.
Multipolygon is collection of polygons. Each of them is collection of loops - first loop is polygon shell and other loops are holes. Each loop is collection of points.
The script tries to convert GeoJSON to WKT by unpacking these arrays, then concatenating them wrapping using WKT's ( and ), but fails to handle polygon with holes case. I suspect the bug is in Poly.value - this does not handle array case, but I'm not OPENJSON expert.
I guess it is possible to continue making this complicated SQL even more complex, and handle holes case as well, but I think it is more practical to convert GeoJSON to WKT strings using something with real GeoJSON parser and WKT formatter, e.g. ogr2ogr tool.
Upvotes: 1
Vitaly Borisov
Reputation: 1193
I've reduced testing data to:
{"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]]]] } }
]}
I've commented out last outer apply and select WKT to see what's in.
SELECT GEOID, GNAME, STATEFP, COUNTYFP, 10--, Geo=fixed
,WKT
WKT returns:
MULTIPOLYGON(( , ))
So "geography::STGeomFromText(WKT,4326).MakeValid()" cannot build geometry out of this data and fails with the error.
Upvotes: 1
Related Questions
- OPENJSON does not work in SQL Server?
- Where clause on json data in Sql Server 2016
- Spatial Datatype (geometry) to GeoJSON
- Select Json string with Select
- SQL - Json query
- Transforming Data from GeoJSON to SQL Geography
- OpenJson in SQL server 2017 : The multi-part identifier could not be bound
- Getting wrong spatial result in SQL server + management studio (2014) when parse from MultiPolygon WKT
- SQL Query to JSON
- Querying JSON nested array object in SQL