I’m really quite good with maps

Workbench announced support for a spatial view in 6.2, but examples are somewhat lacking. Just how do you get a SHP into MySQL?

Download and unpack a SHP file such as these country boundaries.

In the Workbench installation directory, you’ll find a program “ogr2ogr” that can convert .shp or GeoJSON and load it straight into MySQL.

“C:\Program Files\MySQL\MySQL Workbench 8.0\ogr2ogr.exe” -f MySQL MySQL:databaseNameHere,user=userNameHere,password=passwordHere World_Countries_Generalized.shp -nln worldmap -lco GEOMETRY_NAME=p

(It might give the error ERROR 1: PROJ: proj_identify: Cannot find proj.db which it seems can be ignored.)

Now just select rows of interest in Workbench, click the Spatial View format button, and there’s your world map.

You can run multiple selects (such as the citylot data from yesterday’s post) to overlay on top of the world map.