Hello everyone, welcome to my GIS Tutorial blog. Today I was working on how to export shapefile to MySQL Database. Actually, I was expecting the GUI mode inside MySQL Workbench to work. But in fact, I still cannot solve the problem. You may read my previous post about the ogr2ogr tool issue in MySQL Workbench. And finally, I came up with the hard way. Uploading the spatial datasets to MySQL Database using ogr2ogr command line utility. It is not difficult but for some people, working with the command line could be a pain. But let’s try it. For a more complete list and example of ogr2ogr commands, please check out this page.
We will learn the following
- Install OSGeo4W in Windows 10
- Export Shapefiles, Mapinfo Table to MySQL Database
- Open MySQL Spatial database in QGIS
Export Shapefile to MySQL Database Using ogr2ogr Tool
Before we can actually export the file to the MySQL spatial database, we need to have the ogr2ogr tool installed on our system. The easiest way to do this is by installing OSGeo4W. OSGeo4W is a special binary distribution of many open source geospatial software for Windows. For more information, please visit their website.
Lesson 1. Install OSGeo4W in Windows 10
First, download the binary installation file from this link. Double-click the file and follow on-screen installation wizard until finish. Next, open OSGeo4W Shell. You can find this shortcut from Windows 10 start menu.
Check the ogr2ogr version with this command
GDAL 2.2.4, released 2018/03/19
Lesson 2. Export Spatial Dataset to MySQL Database using ogr2ogr
On the previous step, we have successfully installed OSGeo4W on Windows 10. Now let’s try to export some files to the MySQL database. Make sure you have the MySQL server host, username, and password in hand. The following command will export a Mapinfo Table (Cntr_UTM.Tab) to MySQL database.
ogr2ogr -f MySQL MySQL:gis_database,host=192.168.100.14,user=dhani,password=12345 d:\Contour.Tab -nln contour -update -overwrite -lco engine=MYISAM
- MySQL database: gis_database
- Host: 192.168.100.14
- MysQL username: dhani, password: 12345
- Souce file: D:\Contour.Tab
- Destination table: contour
Change those details with your own.
Lesson 3. Open MySQL Spatial Data on QGIS
To open the MySQL spatial table on QGIS, go to Layer | Add Layer | Add Vector Layer. Click Database on the Source type and then choose MySQL from the database type drop-down list. You will need to create a new connection if you don’t have it.
Enter your MySQL database credentials and make sure you test the connection as shown above. Your new connection will be listed in the connections list.
Click Add and you will see the table list populated from the MySQL database. See the example below.
Select the layer and then hit OK. In a moment, your MySQL layer will be visible in QGIS Canvas