将数据从GeoJSON转换为SQL地理 [英] Transforming Data from GeoJSON to SQL Geography

问题描述

首先，服务器是MS SQL Server 2016开发人员版，我正在将存储在NvarChar(max)列中作为GeoJson的县形数据转换为sql地理字段，以实现更好的存储和空间索引.现在，数据存储为Blob，我正在尝试优化存储并创建空间索引以加快位置比较(例如:X高速公路与Y县是否相交).

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).

有很多关于从sql地理数据到Geo-JSON的文档，但是关于反向操作的文档并不多.我的工作大部分都可以进行，但是在我看来，必须有一种更简单的方法来进行这种转换.这是我到目前为止的内容:

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)

我必须使用Replace(多次)来正确格式化JSON数据，使其成为SQL可以使用的格式，这使查询的这一部分变得混乱，但基本上我摆脱了[和]]，添加了一个空格在坐标的中间而不是逗号，然后将]更改为逗号.对于具有多重垄断的县，我将不得不提出一些更详尽的说明.然后，我不得不使用动态sql来使stgeomfromtext起作用.这似乎是一种非常糟糕的方法，有人知道更好的方法吗?

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.

这是犹他州Piute县的Geo-json(因为它很小而被选中):

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.

推荐答案

也许是这样的:

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

这篇关于将数据从GeoJSON转换为SQL地理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！