MySQL 5.7 came out with support for JSON, improved geometry, and virtual columns. Here’s an example showing them all playing together.
Download citylots.json.
It comes as one big object, so we’ll break it up into separate lines:
grep "^{ .type" citylots.json > properties.json
Connect to a 5.7 instance of MySQL.
CREATE TABLE citylots (id serial, j json, p geometry as (ST_GeomFromGeoJSON(j, 2)));
LOAD DATA LOCAL INFILE 'properties.json' INTO TABLE citylots (j);
A few of the rows don’t contain useful data:
DELETE FROM citylots WHERE j->'$.geometry.type' IS NULL;
In MySQL Workbench, do:
SELECT id, p FROM citylots;
Then click on Spatial View. It takes a couple of minutes for 200k rows, but there’s a map of San Francisco.
The default projection, ‘Robinson’, is designed for showing the whole world at once and so is pretty distorted for this particular data set. Mercator or Equirectangular are better choices. Fortunately, Workbench repaints the data in just a few seconds.
If you selected some other fields, you can click on the map and see the relevant data for that particular geometry.
