SQLTeam.com | Weblogs | Forums

GeoJSON Format

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]]] ] }

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/

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]]] ] }

hi

how about

cast ( column_nvarchar as SQL GEOGRAPHY)

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

What version of SQL Server are you working in?

It is 2017

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

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