GeoJSON Format

Transact-SQL
1

Can you please let me know how to convert data stored at a NVARCHAR column to SQL GEOGRAPHY ? Column Name is FeatureJSON

CREATE TABLE ZoneFeature (Id SMALLINT, FeatureJSON NVARCHAR(MAX))

One value on FeatureJSON

{ "type": "MultiPolygon", "coordinates": [ [[[102.0, 2.0], [103.0, 2.0], [103.0, 3.0], [102.0, 3.0], [102.0, 2.0]]], [[[100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]], [[100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2]]] ] }

2

hi hope these links help :slight_smile:

http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx

https://www.sqlshack.com/sql-server-data-type-conversion-methods-performance-comparison/

3

Thank you for your time. None of the articles is helpful. My value in the table looks like { "type": "MultiPolygon", "coordinates": [ [[[102.0, 2.0], [103.0, 2.0], [103.0, 3.0], [102.0, 3.0], [102.0, 2.0]]], [[[100.0, 0.0], [101.0, 0.0], [101.0, 1.0], [100.0, 1.0], [100.0, 0.0]], [[100.2, 0.2], [100.8, 0.2], [100.8, 0.8], [100.2, 0.8], [100.2, 0.2]]] ] }

4

hi

how about

cast ( column_nvarchar as SQL GEOGRAPHY)

5

that wont work either. Geography column requires a the coordinates to only have long lats.

https://docs.microsoft.com/en-us/sql/t-sql/spatial-geography/spatial-types-geography?view=sql-server-ver15

6

What version of SQL Server are you working in?

7

It is 2017

8

your array of arrays in your FeatureJSON column will be very hard to work with in SQL Server. It will require a lot of parsing. I am not sure SQL is the best place to do that.

Lets get the ball rolling with at least the following.

;with src
as
(
	select x.*
	  from #ZoneFeature z
	 cross apply OPENJSON(z.FeatureJSON) x
)
select * 
 From src
 where type = 4

--the sampling of long lats are coming from your FeatureJSON column which I manually stripped of the brackets.

 Declare @polygon geometry
 SET @polygon = geometry::STGeomFromText('POLYGON ((102.0 2.0, 103.0 2.0, 103.0 3.0, 102.0 3.0, 102.0 2.0))', 4326)

 select @polygon

Will this be a one time migration to port this json to geometry?

Also check this thread out

9

Thank you very much for your time with all this information, I will spend time with the information you provided.
Much appreciated.