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:

Unknown said...

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

Anonymous said...

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

thanks

Kurt

Autodesk said...

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)

Zaharia Constantin said...

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?

Anonymous said...

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.