Texas
I wanted some test data to use for some GIS exercises and was very happy to find many useful sets curated by the OpenStreetMaps folks. Shapefiles are used to hold the various data points of an item of interest. I had assumed that the data would have some sort of longitude/latitude pairs but was wondering what I would need to do to work with that data and what ever came bundled with it. I download the Texas data and then loaded it into the database.
You will need a copy of the ogr2org utility This wonderful program reads the raw shapefile and converts it into SQL. You may want to get the source but hopefully a version may be available for you Linux distribution. There is a copy of the ogr2ogr program that comes with MySQL Workbench but I have not been able to get it to convert the data and load that data into a MySQL instance, with either Windows, Fedora, or Ubuntu.
$ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=****** gis_osm_natural_a_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
$ ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=***** gis_osm_natural_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
The Data
The ogr2org utility reads the shape files and puts all the attributes into a a table.
mysql> DESC gis_osm_natural_free_1;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| OGR_FID | int(11) | NO | PRI | NULL | auto_increment |
| SHAPE | geometry | NO | MUL | NULL | |
| osm_id | varchar(10) | YES | | NULL | |
| code | decimal(4,0) | YES | | NULL | |
| fclass | varchar(28) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
And a sample line from that table.
mysql> select OGR_FID,ST_ASTEXT(SHAPE) as Shape, osm_id, code, fclass, name FROM gis_osm_natural_free_1 limit 1;
+---------+-------------------------------+----------+------+--------+--------------------+
| OGR_FID | Shape | osm_id | code | fclass | name |
+---------+-------------------------------+----------+------+--------+--------------------+
| 1 | POINT(29.3060929 -94.7679897) | 80029566 | 4141 | beach | Stewart Beach Park |
+---------+-------------------------------+----------+------+--------+--------------------+
1 row in set (0.00 sec)
The SHAPE column has the desired longitude and latitude. So now we have this data but I had zero clue to where Stewart Beach Park was located.
MySQL Workbench to the Rescue
Workbench makes it easy to 'see' where the data was. You can see in the below example that Stewart Beach Park is the first row returned.
 |
| MySQL Workbench has an amazing number of features including the ability to help display GIS data. |
If you right click on the 'BLOB' graphic under the shape column you will see an option to Show Point In Browser.
 |
| The location of Stewart Beach Park on the Galveston Sea Wall |
So Stewart Beach Park is right on the Galveston Sea Wall and around the corner from the cruise ship terminals.
Mid-Atlantic Developer's Conference
I will be speaking on MySQL & GIS at the Mid-Atlantic Developer's Conference and you can still attend but tickets are going quickly.
