In recent years, GeoJSON has become a popular data format for exchanging GIS data due to several factors. The primary factors being that it’s easy to read, and it’s simple and lightweight. In 5.7.5, we added support for parsing and generating GeoJSON documents via two new functions: ST_GeomFromGeoJson() and ST_AsGeoJson(). These functions makes it easy to connect MySQL with other GeoJSON enabled software and services, such as the Google Maps Javascript API.
Since GeoJSON is a JSON format, we needed a library to parse and write JSON documents. After evaluating several candidates, we ended up with rapidjson due to its features, speed, and compatible license.
The new functions support all of the geometry types specified in the GeoJSON specification, as well as collections. The parsing function also extracts the geometry information from feature objects, as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> SELECT ST_AsText(ST_GeomFromGeoJson( -> '{ '> "type": "Feature", '> "properties": {"Location": "Oracle HQ"}, '> "geometry": '> { '> "type": "Point", '> "coordinates": [-122.262289, 37.530518] '> } '> }' '> )); +--------------------------------+ | SELECT ST_AsText(ST_GeomFr ... | +--------------------------------+ | POINT(-122.262289 37.530518) | +--------------------------------+ |
When creating GeoJSON documents from spatial data, you have the possibility to specify a maximum number of decimal digits in the output. If GeoJSON output length is a concern for you, then this is a simple and efficient way to reduce the output length if your geometry contains a lot of coordinates with many decimals.
You can even add a URN in the OGC namespace to the GeoJSON output. The function uses the SRID from the geometry input, and outputs a short or long format OGC URN depending the input parameters (note that the output below is formatted for readability):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> SELECT ST_AsGeoJson(ST_GeomFromText("POINT(-0.127676 51.507344)", 4326), 5, 4); +-------------------------------------------------------------------------+ | ST_AsGeoJson(ST_GeomFromText("POINT(-0.127676 51.507344)", 4326), 5, 4) | +-------------------------------------------------------------------------+ | { | | "type":"Point", | | "coordinates": [-0.12768,51.50734], | | "crs": | | { | | "type":"name", | | "properties": | | { | | "name":"urn:ogc:def:crs:EPSG::4326" | | } | | } | | } | +-------------------------------------------------------------------------+ |
URNs in the OGC namespace are also recognized by the parsing function, and are stored in the geometry output from the function.
What about Importing GeoJSON with 3D Geometries?
You might think that your lovely collection of GeoJSON documents with 3D geometries are useless with these functions, and that they can’t be imported, but this is exactly what the options argument in ST_GeomFromGeoJson is for. By default, the parsing function will refuse to parse documents with 3D geometries. We do, however, realize that this would reject a lot of existing documents out there, so setting the options argument to 2, 3, or 4 will allow you to import these documents:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> SELECT ST_AsText(ST_GeomFromGeoJson( -> '{ '> "type": "LineString", '> "coordinates": '> [ '> [30.1904297,69.7181067,1.11], '> [27.8173828,70.2446036,1.12], '> [21.9726563,69.6876184,1.12], '> [18.1054688,68.7204406,1.11], '> [15.9082031,67.5924750,1.12], '> [14.2382813,66.2314575,1.11], '> [13.1835938,65.1091482,1.10], '> [11.9091797,63.6267446,1.10], '> [8.65722660,61.2702328,1.11], '> [7.07519530,58.0080978,1.11] '> ] '> }', 3 '> )); +------------------------------------------------------------------------------+ | SELECT ST_AsText(ST_GeomFr ... | +------------------------------------------------------------------------------+ | LINESTRING(30.1904297 69.7181067,27.8173828 70.2446036,21.9726563 69.6876184,| | 18.1054688 68.7204406,15.9082031 67.592475,14.2382813 66.2314575,13.1835938 6| | 5.1091482,11.9091797 63.6267446,8.6572266 61.2702328,7.0751953 58.0080978 | +------------------------------------------------------------------------------+ |
Option values of 2, 3, and 4 all have the same effect for now, but in the future when 3D geometries are supported, they will produce different results. We recommend using option 3, since this will mean that documents with 3D geometries will be rejected when 3D is introduced in MySQL. This will ensure that the change of behavior won’t happen silently and that you can take the appropriate action in your applications.
We look forward to your feedback on the new Geohash and GeoJSON features! Please let us know if you have any comments or if you run into any bugs. Thank you for using MySQL!