I have some SQL Server 2017 tables which I want to use to display points, lines & regions on maps using LeafletJS. To do this I need to store as JSON files.
The latest SQL Server has commands to do this:
For points:
select 'Feature' AS type, 'Point' as [geometry.type], JSON_QUERY (FORMATMESSAGE('[%s,%s]', FORMAT(WGS84Loc.Long, N'0.#####'), FORMAT(WGS84Loc.Lat, N'0.#####'))) as [geometry.coordinates], rtrim(TargetName) as [properties.name] FROM Targets FOR JSON PATH
For lines:
select 'Feature' AS type, 'LineString' as [geometry.type], JSON_QUERY (FORMATMESSAGE('[[%s,%s],[%s,%s]]', FORMAT(WGS84Loc.STPointN(1).Long, N'0.#####'), FORMAT(WGS84Loc.STPointN(1).Lat, N'0.#####'),FORMAT(WGS84Loc.STPointN(2).Long, N'0.#####'), FORMAT(WGS84Loc.STPointN(2).Lat, N'0.#####'))) as [geometry.coordinates], ID as [properties.id], rtrim(Name) as [properties.name] FROM TrailLines Where VDateTime > '2017-09-06 00:00:00' FOR JSON PATH
For regions/polygons:
select 'Feature' AS type, 'Polygon' as [geometry.type], JSON_QUERY (?????????) as [geometry.coordinates], rtrim(Name) as [properties.name] FROM Regions FOR JSON PATH
The first 2 work fine but the last one has me stumped. I have a standard Geography field which contains the polygon but I can't figure out the syntax for the JSON_QUERY to take the coordinates of all the vertices of the polygon & put them into a list. I figure it might have something to do with [WGS84Loc].STAsText but can't get it correct.
Would anyone know the correct SQL syntax?