Contents


Overview

Skill Level: Any Skill Level

GDAL/OGR is an open source toolkit that provides many utilities to work with spatial data. This recipe takes you through the steps to convert spatial data in raster and vector formats to and from a DB2 for z/OS, DB2 LUW or DashDB database.

Ingredients


With this recipe you will populate DB2 tables with spatial data, get information about the spatial data and convert spatial data in both vector and raster formats into and from DB2.

Step-by-step

  1. Setup DB2 and sample data

    1. Note: this recipe uses GDAL/OGR on Windows connecting to a local DB2 database. Change the database connection values in the examples to your database name and userid.

    2. Download and unzip the sample data to a convenient directory.

    3. Open a DB2 command window where you can execute SQL statements. If you are using a remote database, make sure it is cataloged locally. You can use the scripts catalog-luw.sql or catalog-zos.sql as appropriate and make changes for the remote server location. (Use catalog-luw.sql for DashDB)

      You can execute the script with a command like:
      db2 -tvf catalog-luw.sql

    4. Import sample spatial for banks, customers and San Jose streets.
      – Modify the scripts import-luw.sql or import-zos.sql as appropriate to change the userid in the stored procedure. (The scripts contain descriptions of each of the steps and any modifications needed.
      – Execute the script with a command like:
      db2 -tvf import-luw.sql
      – Check that there were no processing errors

    5. Verify the imported data with the SQL statement (change “dash5510” to your connection userid):
      db2 select name, street, varchar(db2gse.st_astext(geom),32) from dash5510.banks
      which should return the name, street address and location of the Meridian and San Carlos branches
  2. Install GDAL/OGR

    1. From the GDAl/OGR (download) location select the release that corresponds to your Windows OS, either 32-bit or 64-bit:
      release-1800-gdal-2-1-0-mapserver-7-0-1
        or
      release-1800-x64-gdal-2-1-0-mapserver-7-0-1
    2. Select and download the “Generic installer for the GDAL core components
    3. Execute the downloaded file to have Windows install GDAL
    4. If you want to use the Python support, also download and install the “Installer for the GDAL python bindings” that correspond to the version of Python you have installed. It is important that the “bitness” of GDAL, the Python binding and the Python environment all be the same.
  3. Get information about vector spatial tables in DB2

    The ogrinfo utility is the main tool to get information about the vector spatial tables in DB2.

    From the Windows start menu, navigate to the GDAL application and select the GDAL command prompt.

    The various options can be displayed with the command
    ogrinfo –help
    but you are probably better off looking at the detailed description at the web site http://www.gdal.org/ogrinfo.html

    You can get a list of all the tables that have spatial columns with a command like:
    ogrinfo DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;DATABASE=OSTEST;HOSTNAME=localhost;PROTOCOL=TCPIP;PORT=50000;UID=osuser;PWD=osuserpw

    The parameters should be modified to match your database connection information. For a remote host, HOSTNAME is the IP address of the database server. This example is using DB2 LUW; for DB2 for z/OS the PORT would be 446. Note that the DRIVER specification needs to be surrounded by double-quotes as the driver name includes blanks.

    This should result in a simple list of the sample spatial tables:
    gdal-ogrinfo1


    You can list information about a specific table and each of the features by specifying the -al option and appending the tables specification (this can result in a lot of output):
    ogrinfo -al DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;database=OSTEST;Hostname=localhost;PROTOCOL=TCPIP;port=50000;UID=osuser;PWD=osuserpw;tables=banks
    gdal-ogrinfo2

    You can list just summary information about a specific table the -al and -so options and appending the tables specification. If you define an Windows ODBC datasource, you can use a simpler syntax by just specifying the datasource name (DSN):
    ogrinfo -al -so DB2ODBC:DATABASE=ostest;DSN=ostest64;tables=banks;
    gdal-ogrinfo3

     

  4. Import and export vector spatial data to/from DB2

    The ogr2ogr utility is the main tool to move or convert vector data from one source or format to another.

    From the Windows start menu, navigate to the GDAL application and select the GDAL command prompt.

    The various options can be displayed with the command
    ogr2ogr –help
    but you are probably better off looking at the detailed description at the web site http://www.gdal.org/ogr2ogr.html.

    To export a DB2 spatial table to the Esri shapefile format, you can use a command like the following to specify first the output name, banks2.shp, and then the source table in DB2.
    ogr2ogr banks2.shp DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;DATABASE=OSTEST;HOSTNAME=localhost;PROTOCOL=TCPIP;PORT=50000;UID=osuser;PWD=osuserpw;tables=banks
    The Windows directory listing shows the 4 files that constitute a shapefile:
    gdal-ogr2ogr1

    Conversely, to import a shapefile into DB2, you can use a command like the following:
    ogr2ogr -f DB2ODBC DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;DATABASE=OSTEST;HOSTNAME=localhost;PROTOCOL=TCPIP;PORT=50000;UID=osuser;PWD=osuserpw; banks2.shp
    It is necessary to specify the option -f DB2ODBC to let ogr2ogr know that the destination is DB2.

    The result can be checked with:
    ogrinfo -al -so DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;DATABASE=OSTEST;HOSTNAME=localhost;PROTOCOL=TCPIP;PORT=50000;UID=osuser;PWD=osuserpw;tables=banks2
    which should return:
    gdal_ogrinfo4

  5. Import/Export raster data to/from DB2

    The DB2 support for raster data through GDAL uses the specification defined by the Open Geospatial Consortium (OGC).

    The gdal_translate utility is the main tool to move or convert raster data from one source or format to another.

    The various options can be displayed with the command
    gdal_translate –help
    but you are probably better off looking at the detailed description at the web site http://www.gdal.org/gdal_translate.html.

    To export a DB2 spatial table to the Esri shapefile format, you can use a command like the following to specify first the output name, banks2.shp, and then the source table in DB2.
    gdal_translate -of DB2ODBC -co RASTER_TABLE=small_world small_world.tif DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;database=OSTEST;Hostname=localhost;PROTOCOL=TCPIP;port=50000;UID=osuser;PWD=osuserpw;
    The option -of DB2ODBC specifies that the output is to DB2. The option -co RASTER_TABLE=small_world specifies the name of the raster data in DB2. This is followed by the name of the input file and the DB2 connection information.
    Running the command shows the size of the image in pixels and a progress message:
    gdal_translate1


    The raster data is managed in 5 tables with the DB2 schema GPKG which can be shown with the command:
    gdal_translate2

    You can use the gdalinfo utility to get information about raster data.

    The various options can be displayed with the command
    gdalinfo –help
    but you are probably better off looking at the detailed description at the web site http://www.gdal.org/gdalinfo.html.

    The information about the raster data just loaded can be shown with a command like:
    gdalinfo -oo TABLE=small_world DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;database=OSTEST;Hostname=localhost;PROTOCOL=TCPIP;port=50000;UID=osuser;PWD=osuserpw;
    Which should show the following:
    gdalinfo1

    You can also use the gdal_translate utility to export raster data from DB2 to a file (or other format) with a command like:
    gdal_translate -of GTIFF -oo TABLE=small_world DB2ODBC:”DRIVER={IBM DB2 ODBC DRIVER}”;database=OSTEST;Hostname=localhost;PROTOCOL=TCPIP;port=50000;UID=osuser;PWD=osuserpw; small_world2.tif
    The option -of GTIFF specifies the output format. The option -oo TABLE=small_world specifies the name of the raster data in DB2. This is followed by the DB2 connection information and the output file name.

     

  6. Develop applications using the GDAL Python API

    GDAL provides bindings to write applications in C, C++, Python and Java. The easiest way to find information on using these APIs is to Google “GDAL API” and the language.

    Python is a very convenient language for developing applications as it doesn't require compile steps and is easy to use. Information to get started with can be found in the OSGEO GDAL wiki and the GDAL tutorial.

    It isn't practical to go into an indepth Python tutorial here of all the GDAL capabilities but a simple example with comments is provided. (Make sure you add Python to your path before running). The file db2test.py is also included in the sample data. You can run it with a command like:
    python db2test.py

    This is the source code for the sample:

    # include OGR functionality
    import ogr
    
    # attempt to open a DB2 ODBC connection and check if it was successful
    fileName = "DB2ODBC:DRIVER={IBM DB2 ODBC DRIVER};database=OSTEST;Hostname=localhost;PROTOCOL=TCPIP;port=50000;UID=osuser;PWD=osuserpw"
    dataSource = ogr.Open( fileName )
    if dataSource is None:
        print 'failed to open data source'
        quit()
    
    # get the CUSTOMERS table as a layer and check if it was successful    
    layer = dataSource.GetLayer("CUSTOMERS")
    if layer is None:
         print 'failed to get layer'
         quit()
    
    # get the count of the features in the layer     
    count = layer.GetFeatureCount()
    print 'Layer count: ', count
    
    # get the geographic extent of the layer
    extent = layer.GetExtent()
    print 'Layer extent: ', extent
    
    # get the first feature found and dump its attributes
    feature = layer.GetNextFeature()
    geometry = feature.GetGeometryRef()
    feature.DumpReadable()
    
    # set a query envelope so we get a subset of features
    layer.SetSpatialFilterRect( -121.9, 37.3, -121.8, 37.4 )
    
    # get the count of features within the query envelope        
    count = layer.GetFeatureCount()
    print 'Count: ', count    
    

     This should produce output like the following (the specific customer may be different):
    gdal_python1

     

  7. Conclusion

    GDAL/OGR is one of the oldest and most widely used GIS toolkit.

    With GDAL/OGR you can import raster and vector data to/from DB2 as well as converting between different data formats.

    The Python API is a powerful environment for building GIS applications exploiting spatial data managed in a DB2 database.

Join The Discussion