From GeoJSON to WKB and Back

Spatial data, unlike other types requires a slightly greater attention to detail. Representation of spatial data can be in many forms in which may provide an advantage depending on the environment.

On the web —the GeoJSON format provides a malleable structure that can be easily generated and mutated due to it’s format which derives from JSON.

Online editors such as geojson.io assists in the formatting of spatial data following the RFC 7946 specification for GeoJSON.

As part of a recent project, a task of saving spatial data was required. The data was to come in as GeoJSON and to be stored within our Microsoft SQL Server database.

Unlike PostgreSQL with PostGIS as an extension — SQL Server does not understand GeoJSON geometry natively. Instead, SQL Server stores geometries in Well-Known Binary (WKB) format. By serializing the geometry, the data becomes portable as it’s representation is now defined as a stream of bytes. Due to it’s serialization, geometries in WKT format are unreadable.

000000000140000000000000004010000000000000

Our project written in JavaScript provides us access to npm modules that simplifies storing geometries from GeoJSON within SQL Server.

  • node-mssql— node client to connect to our database
  • wellknown— module to convert GeoJSON geometry to Well-Know Text (WKT)

From a GeoJSON geometry, wellknown’s stringify method will return us the geometry in WKT Format, a text format representing the geometry which SQL Server can parse internally to it’s native WKB format.

With our geometry now encapsulated into a string, executing a stored procedure to insert a data is simple.

Assuming our table has a column like so, behind the scenes, SQL Server will be able to interpret our WKT string in WKB format!

CREATE TABLE GeometryTable (
[Geometry] GEOMETRY NOT NULL
)

Returning GeoJSON from SQL Server can be accomplished in a few steps as well.

SELECT Geometry.toString() as [WKTGeometry]

Which will return the familiar WKT Geometry which now wellknown can parse to give us our GeoJSON geometry object.

As a final step with all spatial data — put it on a map!


Side Note

This is my first technical and Medium post! Feedback regarding anything related is most appreciated, cheers!