A Little Noise

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 to .csv. Run it like this:

"C:\Program Files\MySQL\MySQL Workbench 8.0\ogr2ogr.exe" -f CSV countries.csv countries.shp -lco GEOMETRY=AS_WKT

Now create a table and load the CSV.

CREATE TABLE worldmap (
	OBJECTID smallint unsigned,
	NAME varchar(50),
	ISO3 char(3),
	ISO2 char(2),
	FIPS varchar(5),
	COUNTRY varchar(50),
	ENGLISH varchar(50),
	FRENCH varchar(50),
	SPANISH varchar(50),
	LOCAL varchar(50),
	FAO varchar(50),
	WAS_ISO varchar(3),
	SOVEREIGN varchar(50),
	CONTINENT varchar(15),
	UNREG1 varchar(30),
	UNREG2 varchar(15),
	EU boolean,
	SQKM decimal(20,11),
	g geometry
);

LOAD DATA LOCAL INFILE 'countries.csv' INTO TABLE worldmap FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' IGNORE 1 LINES
(@WKT, OBJECTID, NAME, ISO3, ISO2, FIPS, COUNTRY, ENGLISH, FRENCH, SPANISH, LOCAL, FAO, WAS_ISO, SOVEREIGN, CONTINENT, UNREG1, UNREG2, EU, SQKM)
SET g = ST_GeomFromText(@WKT);

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.