Tuesday, 13 November 2007

Experimenting with MySQL spatial extensions

« Omar | Main | mongrel_rails service and $stdout problem on Windows server »

Two years ago I was experimenting with PostGIS on etch and in these days I'm trying to do the same with MySQL. I'm no longer maintaining a page with my system configuration because It was more annoying than useful (at least for me ;-P ), it should be enough to know that we are on a debian lenny, with MySQL 5.0.45-1.

To test the spatial extensions of MySQL, I'll use again the free GIS data about world borders. The first step is to convert the shape file in a SQL script suitable for MySQL:

  • download shp2mysql from http://kartoweb.itc.nl/RIMapper/ (currently it's at version 0.4);
  • compile it. Assuming that all needed library and header files are installed in the system, it's sufficient to do:
    $ gcc -lshp shp2mysql.c -o shp2mysql
  • the usage is analogous to shp2pgsql, for example:
    $ ./shp2mysql -s 4326 ../../geo_data/world_borders/world_borders.shp world_borders db_name > world_borders.mysql.sql

Now you you can execute the obtained SQL script in a MySQL database and then query the world_borders table:

> select CNTRY_NAME from world_borders where Contains(ogc_geom, GeomFromText('Point(13 40)')) = 1 ;
| Italy      | 
1 row in set (0.41 sec)

and that's fine.

Without the need of loading GIS data we can try some geometric functions:

> select Glength(GeomFromText('LINESTRING(12 34, 13 35)')) ;
| Glength(GeomFromText('LINESTRING(12 34, 13 35)')) |
|                                   1.4142135623731 | 
1 row in set (0.00 sec)

From MySQL 5.0 reference manual:

In MySQL, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.

Does it mean that you can't do geometric calculation on spheroid with MySQL spatial extensions? I don't think so, maybe it's only a bit more complicated, read this enlighten (for me) thread: MySQL GIS

A final note: both PostgreSQL with PostGIS and MySQL spatial extensions work well under Microsoft Windows. I know it because not every coworker uses GNU/Linux, unfortunately ;-) .

Posted by Nicola Piccinini at 12:18 PM CET in geo/
2008-Jun-18 CEST: » .SHP to MySQL