My title was already long enough, but I should have added the subtitle “…on Windows”. Up until this week you had to be a Windows compiling guru and a GDAL/OGR master to get the driver for Ingres to build. With a wave of his hand, Frank fixed that this week and now I (a mere Windows padowan) was able to get it running. Here are a few of the things that transpired and some examples of the end result. Things are easier on Linux (what’s new?), but I needed to crack the Windows nut for a few reasons since it wasn’t quite as ready.
First off, if you are interested in hearing more about the Ingres geospatial capabilities, let me know or join our low volume, informal, list where I (am just starting to) post more info as it comes available or review our wiki (slowly getting updated). We are working, heads down, on getting the next Ingres db release out which will include geospatial capabilities in the core of the database (it’s not an add-on). This release will be backed by Actian’s subscription support services you can get 24/7 professional support for this open source product, with support spread around the world. Now onto the geek details…
Ingres Libraries & Database
Naturally you’ll need Ingres db libraries to get started. We don’t have a simple zip of files so I first pulled that together. You can likely download any of the Windows installer packs from the Ingres download pages: actian.com/downloads/ingres and it will include the library files you need. On my machine all the files I need were in two folders (lib and files) found in C:\Program Files (x86)\Ingres\IngresII\ingres\ with subfolders: lib – for libraries, and files – for headers/include.
If you also need the Ingres database too, grab a 10.1 (build 120+) installer to ensure it’s capable. If that doesn’t work then grab the windows installer from here that also included the geospatial capabilities but may be a little older. I haven’t built Ingres from source on Windows yet, so won’t attempt to walk you through that, but I’ll get to that eventually.
Build GDAL/OGR
Grab the latest source for GDAL/OGR, the improvements were just committed this week. For those who may be new to GDAL/OGR – we are specifically interested in the OGR side of the equation as it manages the vector (not raster) data access we need.
Edit the nmake.opt file, specifically uncomment lines 356-360 and edit 356 to reflect the location of your Ingres libraries I mentioned above.
356 INGRES_DIR = C:\Users\tyler\src\ingres\ # files and lib folders are in here
357 INGRES_INC_DIR = $(INGRES_DIR)\files
358 INGRES_LIB = $(INGRES_DIR)\lib\iilibapi.lib \
359 $(INGRES_DIR)\lib\iilibutil.lib \
360 $(INGRES_DIR)\lib\libingres.lib
Then run, from the GDAL/OGR source folder:
nmake /f makefile.vc
It was that easy and I’m no pro on building on Visual Studio 2010 – having just downloaded the free express version last week.
(Note: I also built in GEOS support, it was just as easy to build from source in Windows, and all you need to do is edit nmake.opt as above but for GEOS after you have it built. You need this for our final step, if you want to hack up QGIS to use this new format from OGR.)
Check that it built in the support for Ingres by running, from the command line, some of the programs in the GDAL/OGR source\apps folder and adding the –formats option. For example:
apps\ogr2ogr --formats
...
-> "MSSQLSpatial" (read/write)
-> "Ingres" (read/write)
-> "PCIDSK" (read/write)
...
Convert data to Ingres Geospatial
One of the reasons we really need this OGR support was for data loading and extracting. We are working on FME support but for those without FME, OGR and Geotools will remain critical!
Grab your favourite shapefiles and give ogr2ogr a try. The general syntax, to connect to a local Ingres database is like this:
ogr2ogr -f ingres @driver=ingres,dbname=[database name] [input.shp] -nln [new table name] #nln is optional
e.g...
ogr2ogr -f ingres @driver=ingres,dbname=canada roadlines.shp -nln roads
Depending on the projection/SRS of the data you may get notices complaining about not being able to INSERT into the spatial_ref_sys table. More on this at another time, but if you want to ignore it, feel free to add -skipfailures and/or -overlap – if you want to run it again easily.
The Ingres database access application is called ‘sql’ – when you run this from a command line you get an interactive session. Type ‘help \g’ (\g being like the ; on other databases) and it will list the tables that are in your database. You can also run:
ogrinfo @driver=ingres,dbname=[database name] – to see if the table was created.
Viewing QGIS
There is no QGIS specific database access provider setup for Ingres.. yet. (Want to help with it? Let me know!) So the approach I thought I try, was to use the OGR Virtual Format. For this you simply create an XML file that includes the connection information you used above. This virtual format is way more powerful than that but this will get you started:
<OGRVRTDataSource>
<OGRVRTLayer name="road">
<SrcDataSource>@driver=ingres,dbname=canada</SrcDataSource>
<SrcLayer>road</SrcLayer>
<GeometryType>wkbLinestring</GeometryType>
<LayerSRS>EPSG:4326</LayerSRS>
<GeometryField>shape</GeometryField>
</OGRVRTLayer>
</OGRVRTDataSource>
Before you can use this in QGIS, consider that the GDAL/OGR that is distributed with it does not include Ingres support that we built earlier. Let the hacking begin, unless you want to build QGIS from scratch on Windows too 🙂 I was using OSGeo4W – a great distribution for Windowswith an easy to use installer for getting QGIS, GRASS and more up and running quickly. I copied these files from GEOS and GDAL into the OSGeo4W bin folder, after backing them up first of course :). Yes, this is a total hack but it saved me from rebuilding QGIS – ideally this won’t be necessary in the future if we can get Ingres support built into the base distributions of GDAL/OGR.
cd C:\OSGeo4W\bin rename gdal18.dll gdal18.dll.orig rename geos_c.dll geos_c.dll.orig copy C:\Users\tyler\src\gdal-svn\gdal19.dll gdal18.dll copy C:\Users\tyler\src\geos-3.3.2\src\geos_c.dll
Now, start up QGIS, select Layer -> Add Vector Layer. Choose Browse for a dataset. Change the drop down type filter box (bottom right) to VRT - Virtual Datasource [OGR], find your VRT file and select Open. Give it some time, I found it slow, at least on this low end laptop I was using. I didn’t get it to display attribute tables, likely due to my ignorance of the VRT format options and querying is slow.
Summary
So OGR is working well with Ingres geospatial types and I’m really glad we’ve got that ready to go on both Linux and Windows (anyone want to try Mac?). We have a queue of fixes, improvements and new features to add to the Ingres database over the upcoming months. We are also focusing on performance (any R-Tree gurus around interested in digging into the Ingres indexing code with us? 🙂 ). This initial development and testing has us off to a good start and I hope you’ll be interested in following along as we continue toward our next release. If you are interested in learning more or share thoughts I invite you to drop me a note at tyler.mitchell at actian !dot! com. Or follow me on Twitter. You can also read about my open source geo book publishing endeavours at http://locatepress.com.
