Returning spatial data in GeoJSON format - Part 1

GeoJSON is popular format used to represent spatial data. An example of GeoJSON text that represents one point is shown in the following example:

 {
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [125.6, 10.1]
 },
 "properties": {
 "name": "Dinagat Islands"
 }
}

In 'geometry' object are placed type of the spatial data and coordinates. In "property" object can be placed various custom properties such as address line, town, postcode and other information that describe object. SQL Server stores spatial information as geometry or geography types, and also stores additional properties in standard table columns.

Since GeoJSON is JSON, it can be formatted using new FOR JSON clause in SQL Server. 

In this example, we are going to format content of Person.Address table that has spatial column SpatialLocation in GeoJSON format using FOR JSON clause. Person.Address contains set of point so in this example I will generate an array of GeoJSON point.

Query looks like:

 select TOP 20 
 'Feature' AS type,
 'Point' as [geometry.type],
 JSON_QUERY
 ( FORMATMESSAGE('[%s,%s]',
 FORMAT(SpatialLocation.Long, N'0.##################################################'),
 FORMAT(SpatialLocation.Lat, N'0.##################################################'))
 ) as [geometry.coordinates],
 AddressID as [properties.id], AddressLine1 as [properties.address], 
 City as [properties.city], PostalCode as [properties.postcode]
 FROM Person.Address
 FOR JSON PATH
 

First I have selected 'Feature' literal as 'type' because this is the first key:value pair in GeoJSON example above. Then I need to select 'Point' literal and place it on geometry.type path in JSON. These are just fixed values that must exist in the GoeJSON output.

Now we have tricky part - how to format latitude and longitude as JSON coordinates? I have used FORMATMESSAGE and FORMAT functions to create JSON array from longitude/latitude information from spatial column. If I just return this string, FOR JSON will see it as a plain text are wrap it with double quotes, e.g.:

 "coordinates": "[-122.164644615406, 47.7869921906598]"

However, I don't want coordinates to be outputted as string - I need JSON array without surrounding double quotes, i.e.:

 "coordinates": [-122.164644615406, 47.7869921906598]

Therefore I need to pass this string to JSON_QUERY function. JSON_QUERY will return input string because there is no selector as second parameter. However, FOR JSON will know that the output of JSON_QUERY is valid JSON text and it will not escape the content.

The last part is easy - I will just return columns from the table and format them in property object. FOR JSON ATH will format JSON using prefixes in the column aliases.

The final output might look like:

 [{
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [-122.164644615406, 47.7869921906598]
 },
 "properties": {
 "id": 1,
 "address": "1970 Napa Ct.",
 "city": "Bothell",
 "postcode": "98011"
 }
 }, {
 "type": "Feature",
 "geometry": {
 "type": "Point",
 "coordinates": [-122.250185528911, 47.6867097047995]
 },
 "properties": {
 "id": 2,
 "address": "9833 Mt. Dias Blv.",
 "city": "Bothell",
 "postcode": "98011"
 }
}]

As you can see with new FOR JSON clause we can easily output any JSON format such as GeoJSON.

In this post I have exported simple spatial points. In the Returning spatial data in GeoJSON format - Part 2 you can see how to export other spatial types.