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

[Frank Warmerdam]

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,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent