Postgres OGR driver not using spatial filters correctly.

[digimap2k]

File: ogrpgdatasource.cpp Method: ExecuteSQL Lines: Around 2334 onwards

When calling ExecuteSQL on a postgres OGR driver I am providing an SQL select statement and a spatial filter. Where there are results in the database this works fine.

Where there are no results matching the select statement (irrespective of the filter) then the following happens.

1. OGR creates the ExecuteSQLCursor and fetches zero records.
2. No errors seen so the results layer is created.
3. The results layer fails to implement the spatial args as the cursor fetch has no records so the SRID remains unknown.
4. OGR attempts to pull a cursor page of records without a spatial filter. This requires a full scan of the database using fields which may not be indexed and is slow.

FIX - would be to check that the cursor used to try and establish the geometry column actually hit something and return an empty layer of results rather than retrawling the database.

[digimap2k]

OK, I've got to the bottom of what is happening. The main problem lies in the process used to get the spatial reference SRSID for the geometry column. This is being done using the following mechanism:

SELECT getsrid("<pszGeomColumn>") FROM (<original query>) AS foo LIMIT 1

This works on the assumption that performing the original query with no spatial filter is OK as we'll probably hit a result soon enough. There are some problems with this:

1. The user is assuming OGR is doing spatial filtering and has probably arranged their indexes accordingly. It is likely that the original query is using non-indexed columns.

2. It's possible that the original query isn't going to hit any results at all and even where there are results there's no guarantee of finding them quickly.

Worst case scenario (which is how I found this), the query is for a very obscure open street map feature in a tiny spatial area (e.g. a single tile render). This results in a full search of the database (many millions rows) using a complex query of non-indexed columns which takes minutes (hence classification as bug not enhancement).

The Solution: A better mechanism would be to modify the way in which the SRID is determined. This can be done using the postgres API to get the OID of the table from which the above query was first executed. I've done this in my local install of 1.9.2 and attached the files. Changes are marked with /* Andy Shelley */

[digimap2k]

Local mods showing a fix.

[digimap2k]

One extra class member in the results layer to hold the table name for the geometry column.

[Even Rouault]

Fix inspired by your changes, but a bit different. Please report if it fixes your issue.

r24545 /trunk/ (5 files in 2 dirs): PG: optimize SRID fetching on SQL result layer; compatibility with PostGIS 2.0 by using ST_SRID (#4699, #4700)

r24546 /branches/1.9/ (5 files in 2 dirs): PG: optimize SRID fetching on SQL result layer; compatibility with PostGIS 2.0 by using ST_SRID (#4699, #4700)

[digimap2k]

Checked out and built the trunk this morning and yes that is working perfectly. I've checked the PG logs for all the edge cases and all are getting the SRSID succesfully from the table schema.

Environment is Qt C++ web service linking to GDAL dll on windows apache test server. I will also test on linux development servers later next week but assume all OK unless I get back to you.

Thanks for the prompt resolution, very impressed.