{"id":432,"date":"2015-11-02T15:16:58","date_gmt":"2015-11-02T21:16:58","guid":{"rendered":"http:\/\/thenoyes.com\/littlenoise\/?p=432"},"modified":"2016-06-13T15:03:43","modified_gmt":"2016-06-13T20:03:43","slug":"the-world-is-not-in-your-books-and-maps","status":"publish","type":"post","link":"https:\/\/thenoyes.com\/littlenoise\/?p=432","title":{"rendered":"The world is not in your books and maps."},"content":{"rendered":"

MySQL 5.7<\/a> came out with support for JSON, improved geometry, and virtual columns. Here’s an example showing them all playing together.<\/p>\n

\"click<\/a><\/p>\n

Download citylots.json<\/a>.<\/p>\n

It comes as one big object, so we’ll break it up into separate lines:
\ngrep \"^{ .type\" citylots.json > properties.json<\/code><\/p>\n

Connect to a 5.7 instance of MySQL.<\/p>\n

CREATE TABLE citylots (id serial, j json, p geometry as (ST_GeomFromGeoJSON(j, 2)));
\nLOAD DATA LOCAL INFILE 'properties.json' INTO TABLE citylots (j);<\/code><\/p>\n

A few of the rows don’t contain useful data:
\nDELETE FROM citylots WHERE j->'$.geometry.type' IS NULL;<\/code><\/p>\n

In MySQL Workbench<\/a>, do:
\nSELECT id, p FROM citylots;<\/code><\/p>\n

Then click on Spatial View. It takes a couple of minutes for 200k rows, but there’s a map of San Francisco. <\/p>\n

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.<\/p>\n

If you selected some other fields, you can click on the map and see the relevant data for that particular geometry.<\/p>\n","protected":false},"excerpt":{"rendered":"

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, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4],"tags":[],"class_list":["post-432","post","type-post","status-publish","format-standard","hentry","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p2IBF1-6Y","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=432"}],"version-history":[{"count":13,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/432\/revisions"}],"predecessor-version":[{"id":471,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=\/wp\/v2\/posts\/432\/revisions\/471"}],"wp:attachment":[{"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/thenoyes.com\/littlenoise\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}