The basics first, server is MS SQL Server 2016 developer edition, and I'm transforming county shape data that is stored in a NvarChar(max) column as GeoJson to a sql geography field for better storage and spatial indexing. Right now the data is stored as blobs, and I'm trying to optimize storage and create spatial indexes to speed up location comparison (EG: Does X highway intersect with Y county).
There is a lot of documentation on going from sql geography data to Geo-JSON, but not so much on doing the reverse. I've got it mostly working, but it occurred to me that there has to be an easier way to do this style of transformation. Here is what I have so far:
declare @Silly varchar(max) = (select CountyJSON from [dbo].[Counties] where name = 'Piute')
declare @GeoString nvarchar(max) =
(select
'''' + upper(ShapeType) + '((' +
replace(
replace(
RePlace(
replace(Shape, '[', '')
, ',', ' ')
, ']]]', ''),
']', ',')
+ '))' + ''''
from
openjson(@Silly)
with (ShapeType Varchar(64) '$.type',
Shape nvarchar(max) '$.coordinates' as json)
)
declare @String nvarchar(max) = ( select 'select geography::STGeomFromText(' + @GeoString + ', 4326) as geodata')
exec (@String)
I have to use Replace (multiple times) to correctly format the JSON data as something SQL can use, which makes that part of the query muddy, but basically I'm getting rid of [ and ]]], adding a space in the middle of the coordinates instead of a comma, and changing ] into a comma. I will have to come up with something more elaborate for counties that are multi-polys. I then had to use dynamic sql to get the stgeomfromtext to function. This seems like a really bad way to do this, does anyone know of a better way?
I would just download the geo values for the counties but I was told to preserve the existing data, so it's an odd request that turns into a lot of work.
here is the Geo-json for Piute county Utah (selected because it's small and simple):
{"type":"Polygon","coordinates":[[[-111.766741,38.501977],[-111.823854,38.457266],[-111.836911,38.428477],[-111.848871,38.425001],[-111.8428,38.15128],[-111.951449,38.151083],[-111.951349,38.145983],[-112.021169,38.145776],[-112.060929,38.14928],[-112.443821,38.149942],[-112.422573,38.170683],[-112.424117,38.181411],[-112.357074,38.224502],[-112.381019,38.249974],[-112.352591,38.279368],[-112.335369,38.332955],[-112.343404,38.347312],[-112.361492,38.355579],[-112.380363,38.379892],[-112.403869,38.394836],[-112.402939,38.415581],[-112.417117,38.419483],[-112.436247,38.402077],[-112.452491,38.428824],[-112.512674,38.468696],[-112.510259,38.488097],[-112.518504,38.509852],[-112.257811,38.512044],[-112.062533,38.510513],[-112.062501,38.50195],[-111.766741,38.501977]]]}
Thanks in advance for any help you guys can give me.
Perhaps something like this:
declare @Silly nvarchar(max) = '{"type":"Polygon","coordinates":[[[-111.766741,38.501977],[-111.823854,38.457266],[-111.836911,38.428477],[-111.848871,38.425001],[-111.8428,38.15128],[-111.951449,38.151083],[-111.951349,38.145983],[-112.021169,38.145776],[-112.060929,38.14928],[-112.443821,38.149942],[-112.422573,38.170683],[-112.424117,38.181411],[-112.357074,38.224502],[-112.381019,38.249974],[-112.352591,38.279368],[-112.335369,38.332955],[-112.343404,38.347312],[-112.361492,38.355579],[-112.380363,38.379892],[-112.403869,38.394836],[-112.402939,38.415581],[-112.417117,38.419483],[-112.436247,38.402077],[-112.452491,38.428824],[-112.512674,38.468696],[-112.510259,38.488097],[-112.518504,38.509852],[-112.257811,38.512044],[-112.062533,38.510513],[-112.062501,38.50195],[-111.766741,38.501977]]]}';
with q as
(
select 1 ShapeId, ShapeType,
stuff(
(
select concat(', ', json_value(Value,'$[0]'),' ',json_value(Value,'$[1]'))
from openjson(s.Shape,'$[0]')
order by cast([key] as int)
for xml path('')
),1,3,'') path
from
openjson(@Silly)
with (ShapeType Varchar(64) '$.type',
Shape nvarchar(max) '$.coordinates' as json) s
), q2 as
(
select ShapeId, concat(upper(ShapeType),'((',path,'))') WKT
from q
)
select ShapeId, geography::STGeomFromText(WKT,4326) as geodata from q2