Importing Raster Based Spatial Data into MySQL 5.7

Setting up the Geospatial Data Abstraction Library
GDAL 1.10+ (by OS Geo) supports converting spatial data between various raster and vector (points, lines, polygons, etc.) based formats, along with supporting MySQL. MySQL and ESRI Shapefiles are generally the best supported combination today.

Version 1.10 of GDAL has big improvements over older versions, but most linux distros (EL6 has GDAL 1.7) only have packages for the older versions. I would recommend building the latest 1.11.0 release from source because the older source packages had a bug building with MySQL 5.6+.

Building it from source would look something like this on UNIX/Linux:

You’ll also need to build and install the related GDAL python libraries separately. For example:

One additional note related to these new Python tools. If you already have an older version of GDAL installed (PostGIS bundles an older version) you’ll see this error when you try to run them because the old library will be earlier in your runtime linker’s path, if not in its cache:

In this case, we can tell the linker to preload our newer 1.11.0 library in our shell this way:

Setting up MySQL
I would also recommend that you use the MySQL 5.7.4-april lab release (the one labeled “MySQL GIS…” in the pulldown). You can read more about the new GIS work in 5.7 here.

An Example
Now that we have all of the necessary pieces in place, here’s a complete working example using Oracle Linux 6.5 on x86_64, with the MySQL 5.7.4-april labs release (the pre-built binaries), and GDAL 1.11.0 (built from source):

  • 1) I downloaded a sample GeoTIFF file to use.
  • 2) I created a database to play in:
    mysql> create database tifftest;
  • 3) I converted the tiff data into shapefile data (raster data -> polygon data), which we can then load into MySQL:
  • 4) I finally loaded the generated shapefile into MySQL:

The final results

I hope that this is helpful! If you ever run into a situation where you need to import/load raster based spatial data into MySQL (spatial databases generally only support vector format), as I did recently, then this should at least help you get started.

2 thoughts on “Importing Raster Based Spatial Data into MySQL 5.7

  1. I am only familiar with spatial data represented by vectors, such as in downloaded from open street map. Is saving to database as raster some sort of caching using the database?

    1. You can think of raster formats as dealing with pixels (cells in a grid matrix) rather than points, and you’ll often encounter them when gathering spatial data from satellite or aerial photos. Here are some helpful links:

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 9 = sixteen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">