Insert Shapefile in MySql as Spatial Data

MySQL supports Spatial data type that corresponds to OpenGIS Classes. While MySQL is not one of the recommended database to store spatial data, but it definitely adds value if you have only one option to store GIS data. For instance I need to dump the Shapefile in MySQL, when I need to render the spatial data with leaflet in order of sorted latitude longitude one by one. This could be easily possible to get the spatial data from MySQL where we don’t need to use any GIS API to get and perform spatial operation.

To dump or insert a shapefile in a MySQL server we need to install ogr2ogr tool in our machine. If you have a windows machine and you have installed QGIS using OSGeo4W then this tool comes with the org2ogr package.

update: You may also see the ogr2ogr in program files folder of QGIS as shown below.

Insert Shapefile in MySql as Spatial Data

1.) Now open the MySQL server and create one database or use one of the existing database. I have made a database named as “spatial”.

2.) Open the command prompt as administrator. I have ogr2ogr file in C drive->OSGeo4W->bin folder. So navigate to this path in command prompt with change directory command (cd).

3.) Now write the command as follows:

ogr2ogr –f MySQL MySQL:database_name,host=localhost,user=database_username, password=database_password path/to/shapefile  -nln datatable_name -update -overwrite -lco engine=MYISAM.

Where you need to edit the bold word written above according to system configuration and need.

Note: If table and its column is not created, then the above command will automatically will create the table name as “datatable_name” as indicated above. You may also define SRS by giving argument as  ‘-a_srs “EPSG:4326″‘.

Reference : http://www.bostongis.com/PrinterFriendly.aspx?content_name=ogr_cheatsheet

I hope this post would helped you to insert spatial data in MySQL easily and you are now eligible to perform Spatial Queries on MySQL data. If you still find problem in performing any step above, do comment below with details, we may look and discuss over the same.