8

GeoJSON

MySQL 5.7 offered several new features in addition to the JSON data type, including a vast improvement in geographic information system (GIS) support. MySQL follows the Open Geospatial Consortium (OGC) OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL option, which proposes extending the SQL RDBMS to support spatial data. MySQL also features functions for converting between spatial values and JSON and follows the GeoJSON specification (RFC 7946) located at http://geojson.org. GeoJSON supports the same geometric and geographic data types as MySQL.

ST_GeomFromGeoJSON

The ST_GeomFromGeoJSON function processes as a GeoJSON-formatted string and returns a geometry. A second optional argument regarding how to handle GeoJSON documents contains geometries with coordinate dimensions higher than a 2; this option can have the value of 1 (default), reject the JSON formatted document and produce an error message; and 2, 3, or 4, accept the document and strip off the coordinates for higher coordinate dimensions. And there is a third and final argument: the Spatial Reference System Identifier (SRID) argument, if given, must be a 32-bit unsigned integer. If not given, the geometry return value has an SRID of 4326. Table 8-1 lists GeoJSON options that are bitmasks, and also lists the permitted values. They can be combined so that a value of 7 is made up of 1, 2, and 4 from the table. The bitmasks for the GeoJSON function are combined to provide more detail or change the style of output.

Table 8-1 Options for GeoJSON

Example 8-1 Using ST_GeomFromGeoJSON

The ST_AsGeomFromGeoJSON function takes a JSON-formatted string and turns it into a geometry. Note that you can wrap this function with ST_AsText to format the output to something more readable. The following, without the ST_AsText, displays the function’s usefulness.

Example 8-2 Not exactly what we wanted from ST_GeomFromGeoJSON

ST_AsGeoJSON

The ST_AsGeoJSON function is the opposite of ST_GeomFromGeoJSON in that takes a geometry and produces a GeoJSON object. Its first option is the number of decimal digits for coordinates, and options can be added, as shown in Example 8-3, to modify the output.

Example 8-3 Using ST_GeomFromText

The ST_GeomFromText options are bitmasks, which means they can be combined. Table 8-2 shows the values of the bitmask options. With no option specified, the output is shown in Example 8-3.

Table 8-2 Options for ST_GeomFromText

Example 8-4 ST_GeomFromText without options

Option 1 adds a bounding box (bbox), as you can see in Example 8-4. Option 2 adds a short-format CRS URN to the output with the default format being a short format (EPSG:srid). Option 4 adds a long-format CRS URN (urn:ogc:def:crs:EPSG::srid) and overrides option 2. Since the option field is a bitmask, the various options can be combined. Option 3, for example, is option 1 plus option 2.

Example 8-5 Other options for the ST_GeoJSON function