Frank,
That did it! I was able to get my query back down to < 2 seconds using:
<SrcSQL>SELECT * FROM alta83_v1 WHERE BOT_LONG > -114 AND BOT_LONG < -113 AND BOT_LAT
> 51 AND BOT_LAT < 52</SrcSQL>
Now I've got to enhance GeoMoose to calculate the view extents in Lat/Long and
hope mapserver will like a VRT layer def of something like:
<SrcSQL>SELECT * FROM alta83_v1 WHERE BOT_LONG > %VIEW_MIN_LONG% AND BOT_LONG <
%VIEW_MAX_LONG% AND BOT_LAT > %VIEW_MIN_LAT% AND BOT_LAT < %VIEW_MAX_LAT%</SrcSQL>
Many thanks!
Brent
Frank Warmerdam wrote:
Brent Fraser wrote:
Frank,
> Brent,
Any time you provide your own SQL it will be executed unaltered. The
spatial and attribute filters are only automatically built into the
select statement when the select statement is constructed internally
by OGR.
OGR makes *no attempt at all* to interprete provided SQL (via
ExecuteSQL()
or <SrcSQL>. So it has no idea what tables it relates to or how any
restrictions could be added.
I guess that's technically true, but ogr (likely the VRT parser) gives
an error when I try to construct my own spatial filter:
<SrcSQL>SELECT * FROM alta83_v1 WHERE BOT_LONG > -114 AND BOT_LONG <
-113 AND BOT_LAT > 51 AND BOT_LAT < 52</SrcSQL>
ERROR 1: Line 4: Didn't find expected '=' for value of attribute 'AND'.
I expect it doesn't like the ">". Quoting the SQL and bracketing the
where clause portion doesn't help. Perhaps there's an escape
sequence I could use?
Brent,
You need to use normal XML escaping. I imagine that is > and < for
the comparison operators.
And is there point in constructing a view with columns of WKT
geometry, XMIN, YMIN, XMAX and YMAX to see if the ODBC driver would
take advantage of those?
Not if you are going to use SrcSQL - no. If you provide your own SQL, then
no automatic spatial filtering will take place in the database - it will be
done after fetching all rows.
Best regards,
_______________________________________________
gdal-dev mailing list
[email protected]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
