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

[Brent Fraser]

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 &gt; -114 AND BOT_LONG &lt; -113 AND BOT_LAT &gt; 51 AND BOT_LAT &lt; 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 &gt; %VIEW_MIN_LONG% AND BOT_LONG &lt; %VIEW_MAX_LONG% AND BOT_LAT &gt; %VIEW_MIN_LAT% AND BOT_LAT &lt; %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 &gt; and &lt; 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,