[gdal-dev] OGR and database filter on spatial extent

[Brent Fraser]

More performance news... 

Using ogrinfo -> VRT -> ODBC -> SqlServer2000, the query using:
    <SrcLayer>alta83_v1</SrcLayer>
	- takes less than 2 seconds

    <SrcSQL>Select * from alta83_v1</SrcSQL>
	- a very simple select, logically equivalent to the above
	- takes 30 seconds (!)

>From the debug messages below, it seems that the spatial filter is not passed to the database server if a <SrcSQL> is included in the VRT.  Not too surprising given the difficultly of parsing a SELECT statement and rebuilding it with additional clauses, but we should mention the order of magnitude performance hit in the doc.

And I'm not sure why ogr seems to execute the SQL three times...

Thanks!
Brent

=================================
<SrcLayer>alta83_v1</SrcLayer>:
---------------------------------
G:\GeoData\Wells>ogrinfo -spat -114 51 -113 52 -ro --debug ON all_wells.ovf wells | more
OGR_ODBC: EstablishSession(DSN:"Wells_SQL", userid:"test", password:"test")
ODBC: SQLConnect(Wells_SQL)
OGR_ODBC: Table alta83_v1 has no identified FID column.
OGR: OGROpen(ODBC:test/test at Wells_SQL,alta83_v1/00C846D0) succeeded as ODBC.
OGR: OGROpen(all_wells.ovf/00C83B60) succeeded as VRT.
OGR: GetLayerCount() = 1

OGR_ODBC: ExecuteSQL(SELECT * FROM alta83_v1 WHERE BOT_LONG > -114 AND BOT_LONG < -113 AND BOT_LAT > 51 AND BOT_LAT < 52)
OGR_ODBC: ExecuteSQL(SELECT * FROM alta83_v1 WHERE BOT_LONG > -114 AND BOT_LONG < -113 AND BOT_LAT > 51 AND BOT_LAT < 52)
OGR_ODBC: ExecuteSQL(SELECT * FROM alta83_v1 WHERE BOT_LONG > -114 AND BOT_LONG < -113 AND BOT_LAT > 51 AND BOT_LAT < 52)
INFO: Open of `all_wells.ovf'
      using driver `VRT' successful.

========================================
<SrcSQL>Select * from alta83_v1</SrcSQL>
----------------------------------------
G:\GeoData\Wells>ogrinfo -spat -114 51 -113 52 -ro --debug ON all_wells.ovf wells | more
OGR_ODBC: EstablishSession(DSN:"Wells_SQL", userid:"test", password:"test")
ODBC: SQLConnect(Wells_SQL)
OGR_ODBC: Table alta83_v1 has no identified FID column.
OGR: OGROpen(ODBC:test/test at Wells_SQL,alta83_v1/00C846D0) succeeded as ODBC.
ODBC: ExecuteSQL(Select * from alta83_v1) called.
OGR_ODBC: Table SELECT has no identified FID column.
OGR: OGROpen(all_wells.ovf/00C83B60) succeeded as VRT.
OGR: GetLayerCount() = 1

OGR_ODBC: Recreating statement.
OGR_ODBC: Recreating statement.
INFO: Open of `all_wells.ovf'
      using driver `VRT' successful.



Brent Fraser wrote:
> Frank,
> 
>  Interesting.  I'm attempting to use Mapserver -> OGR -> VRT -> ODBC -> 
> SqlServer2000 -> a table of 250k rows of point features.
> 
>  Testing with ogrinfo to return all the rows takes about 25 seconds, but 
> with a "-spat"  to get about 3000 rows takes < 2 seconds (very 
> acceptable).  And I didn't need to create a view with 
> XMIN,YMIN,XMAX,YMAX as implied on GDAL's ODBC format page; OGR
> applied the filter to the point coordinate columns (dunno what it would 
> do with lines or polygons and no min/max columns though).
> 
>  Looking at mapserver's mapogr.cpp, it appears that 
> OGR_L_SetSpatialFilter is being called, so I'll have to do some more 
> tracing/debugging to find out why my mapserver performance is so bad 
> with this layer.
> 
> Thanks!
> Brent Fraser
> 
> Frank Warmerdam wrote:
>> Brent Fraser wrote:
>>> Hi All,
>>>
>>>  In the case of accessing data in a relational database, does OGR 
>>> have the ability to pass a spatial extent to the database to use as a 
>>> filter on the geometry before sending the rows?
>>
>> Brent,
>>
>> Yes.  The OGRLayer has a SetSpatialFilter() method for this.  Some 
>> drivers
>> evaluate the spatial filter in OGR after reading all records, but smart
>> drivers are able to use the spatial filter for efficient querying.  
>> So, the
>> spatially enabled databases work it into the query.
>>
>> The -spat switch for ogrinfo is translated into a SetSpatialFilter() call
>> for instance.
>>
>> Best regards,
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev
>