Thursday, 13 March 2008

Loading spatial data into MySQL

To be able to create choropleth maps in geobrowsers you'll need polygon data for the geographical units you want to map. These polygons can then be coloured according to a statistical value.

If you want to store geographic features in an open source database, you have two options: PostGIS/PostgreSQL or MySQL Spatial. MySQL has limited GIS support compared to PostGIS, but I want to test if it's sufficient for thematic mapping.

I will recommend two software packages: XAMPP and FWTools. XAMPP is an easy to install Apache distribution containing MySQL, PHP and PERL. FWTools is an open source GIS binary containing MapServer, GDAL/OGR, Proj.4, OpenEV, OGDI and Phyton. Maybe more than you need, but it is an easy install. The Swiss Army Knife is the GDAL/OGR library.

I have my boundary polygons stored in a shapefile. There is no MySQL command which enables you to upload spatial data to a database table (this can be done with PostGIS using the shp2pqsql command) . If you have installed the GDAL/OGR library, you can use the ogr2ogr command to upload point, lines or polygons from a shapefile. The command I used was:

ogr2ogr -f MySQL MySQL:my_database,user=root,password=mypassword TM_WORLD_BORDERS_SIMPL-0.1.shp -nln my_table -nlt MULTIPOLYGON -update -overwrite -lco GEOMETRY_NAME=my_polygon_column

5 comments:

  1. Unknown13 April 2008 at 05:29

    Hi:

    Thanks for leaving the example of how to import shapefile data to a mysql table. I'm working on doing the same sort of thing with some moderately large shapefiles and trying to integrate with google maps api - not sure if this approach is going to work terribly well or if I will have to go back to mapserver but my partner that does the GIS and archive work wants to give it a shot.

    Thanks again,
    Mike

    ReplyDelete
  2. Anonymous28 May 2008 at 09:31

    Found your blog by chance!
    Really interesting themes here - its stored now in my feedreader

    thanks

    Kurt

    ReplyDelete
  3. Autodesk19 July 2008 at 18:26

    you can also populate a MySQL database using AutoCAD Map 3D. This desktop GIS uses FDO (Feature Data Objects) an open source technology that allows you to 'bulk copy' data from one datastore (i.e. Shapefile) to another (i.e. MySQL)

    ReplyDelete
  4. Zaharia Constantin26 November 2013 at 19:07

    Hello, i have download TM_WORLD_BORDERS-0.3 and imported inside mysql, but some countries are missing, like Moldova, Bulgaria. Is possibile to update also with this counties?

    ReplyDelete
  5. Anonymous26 March 2014 at 01:04

    Hi,

    Thanks for the post. I've succeded exporting shp file to mysql using ogr2ogr. The polygon is stored in blob in geometry column. Using asText function i realized that the point inside the polygon is not WGS84 point (Country:Malaysia). How to correct it?
    Example : POLYGON((-1537.2089012992 24645.590501093,-1673.2760986513 24610.21210087 .......)

    Thank for yr help.

    ReplyDelete