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/t...@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/t...@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@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev
