Introduction

In the process of getting a collection of Oracle tables working efficiently with GeoIQ Connect, we learned a few tricks and added some additional code to improve performance. This post covers some hints you can do on the database side of things to make your database tables work well with GeoIQ Connect.

Don’t Use Queries

For small sets of data, Connect queries can be convenient, but they perform poorly with large result sets. GeoIQ will normally add conditions to reduce the amount of data queried to just what is needed, but these optimizations don’t work with queries. Instead, create a view on the database and register that through geoiq.

Use a Geometry Column with an Index

This will greatly speed up performance of data fetches when getting data for a small area of the map, since only those features in the bounds will be returned. If you have only latitude/longitude columns, you can add a geometry column and use a trigger to set it when new features are added. Also, make sure your geometry column is indexed.

Avoid CLOB/BLOB columns

These large blocks of data typically require a separate request to the server to get the content, which will significantly slow down performance when iterating over a lot of rows. Note that in many cases, geometries are stored as CLOBs/BLOBs – to address that issue…

Add a Pre-calculated WKT Column

To get the geometry data out of the database, GeoIQ will convert it to either WKT (Well-known Text) or Hex-encoded WKB (Well-known Binary). If your table has a column named GEOM_WKT where GEOM is your geometry column, GeoIQ will assume that GEOM_WKT is the WKT representation of the geometry in GEOM. Adding this column to your table will eliminate the extra lookup for BLOB/CLOB, as well as the cost of transforming the geometry to WKT. In addition, if you don’t need all the detail of the original geometry, you can simplify the WKT column.

There are a few caveats, however. First, to get the performance gain, you need to make the WKT column a normal string column, not a CLOB, so there will be an upper limit on how big a WKT it can handle. Fortunately, if a particular value is null, the system will fall back to querying for the original geometry column, so you can be selective on which rows you provide WKT values for.

In the future, GeoIQ should support pre-calculated Hex WKB columns as well.

Ensure Tables Have a Primary Key

There are a few operations whose performance is improved by having a single-column primary key on your table. These include the above mentioned geometry column lookup, as well as batch processing large tables, to, for example, generate pngs and tiles.

Simplify Geometries

Even a table with a small number of rows can create memory and speed problems if the geometries it contains are very large. In some cases, you may be able to simplify the geometries enough to improve performance without impacting the use of the data.