Planet MySQL :: Planet MySQL - Archives - 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:

./configure --with-mysql=/usr/local/mysql-5.7.4-labs-april-linux-el6-x86_64/bin/mysql_config
make 
make install

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

yum install python-devel
cd /home/matt/gdal/gdal-1.11.0/swig/python
make
python setup.py install --prefix=/usr/

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:

/home/matt/gdal/gdal-1.11.0/swig/python/scripts/gdal_polygonize.py o41078a1.tif -f "ESRI Shapefile" testdata/
Traceback (most recent call last):
  File "/home/matt/gdal/gdal-1.11.0/swig/python/scripts/gdal_polygonize.py", line 36, in 
    import gdal, ogr, osr
  File "/usr/lib64/python2.6/site-packages/GDAL-1.11.0-py2.6-linux-x86_64.egg/gdal.py", line 2, in 
    from osgeo.gdal import deprecation_warn
  File "/usr/lib64/python2.6/site-packages/GDAL-1.11.0-py2.6-linux-x86_64.egg/osgeo/__init__.py", line 21, in 
    _gdal = swig_import_helper()
  File "/usr/lib64/python2.6/site-packages/GDAL-1.11.0-py2.6-linux-x86_64.egg/osgeo/__init__.py", line 17, in swig_import_helper
    _mod = imp.load_module('_gdal', fp, pathname, description)
ImportError: /usr/lib64/python2.6/site-packages/GDAL-1.11.0-py2.6-linux-x86_64.egg/osgeo/_gdal.so: undefined symbol: GDALRasterBandGetVirtualMem

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

export LD_PRELOAD=/usr/local/lib/libgdal.so.1

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:

mkdir testdata
/home/matt/gdal/gdal-1.11.0/swig/python/scripts/gdal_polygonize.py o41078a1.tif -f "ESRI Shapefile" testdata/

4) I finally loaded the generated shapefile into MySQL:

    ogr2ogr -f MySQL MySQL:tifftest,user=root testdata/out.shp -update -overwrite -progress -nln testdata

The final results

mysql> show tables;
+--------------------+
| Tables_in_tifftest |
+--------------------+
| geometry_columns   |
| spatial_ref_sys    |
| testdata           |
+--------------------+
3 rows in set (0.00 sec)

mysql> show create table testdata\G
*************************** 1. row ***************************
       Table: testdata
Create Table: CREATE TABLE `testdata` (
  `OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
  `SHAPE` geometry NOT NULL,
  `dn` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `OGR_FID` (`OGR_FID`),
  SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=InnoDB AUTO_INCREMENT=3488 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select ogr_fid, st_astext(shape), dn from testdata limit 3\G
*************************** 1. row ***************************
         ogr_fid: 1
st_astext(shape): POLYGON((752396.619854 4557341.646744,752401.496654 4557341.646744,752401.496654 4557339.208344,752403.935054 4557339.208344,752403.935054 4557334.331544,752401.496654 4557334.331544,752401.496654 4557336.769944,752399.058254 4557336.769944,752399.058254 4557339.208344,752396.619854 4557339.208344,752396.619854 4557341.646744))
              dn: 0
*************************** 2. row ***************************
         ogr_fid: 2
st_astext(shape): POLYGON((752311.275854 4557327.016344,752313.714254 4557327.016344,752313.714254 4557322.139544,752311.275854 4557322.139544,752311.275854 4557327.016344))
              dn: 0
*************************** 3. row ***************************
         ogr_fid: 3
st_astext(shape): POLYGON((752386.866254 4557329.454744,752389.304654 4557329.454744,752389.304654 4557324.577944,752386.866254 4557324.577944,752384.427854 4557324.577944,752384.427854 4557327.016344,752386.866254 4557327.016344,752386.866254 4557329.454744))
              dn: 0
3 rows in set (0.00 sec)

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.

Search

MySQL Links