[gdal-dev] Problems doing spatial query with OGR

[Frank Warmerdam]

Clay, Bruce wrote:
> I can not use the "BETWEEN" statement with the date field and I can not
> find a date format that works with the WHERE clause.  For some reason
> the data type in OGR space is OFTString but it is DATE in Oracle space.
> If I use the "generic" dialect I can do date comparisons.  My guess
> along with some of the odd Oracle error messages that I am receiving is
> that is causing at least part of the problem.

Bruce,

I believe there is an open ticket on OGR's OCI driver not recognising
the date type, so it falls back to the generic string handling.

> I think the spatial filter might actually have worked because the
> returned feature count was lower but I could not tell for sure because I
> could not get the where clause to be accepted.
> 
> Is there a way to make date/time comparisons in the OGR SQL dialect?

No.  Even if we had the fields as a proper date in OGR, the OGR SQL
dialect does not support any operations on dates as far as I know.

You might want to just explicitly encode the spatial query into your
Oracle WHERE clause if you are only going to be working against
the oracle backend.

If you aren't familiar with oracle spatial filters, the following
code snippet might be helpful.

         oWHERE.Append( " WHERE sdo_filter(" );
         oWHERE.Append( pszGeomName );
         oWHERE.Append( ", MDSYS.SDO_GEOMETRY(2003," );
         if( nSRID == -1 )
             oWHERE.Append( "NULL" );
         else
             oWHERE.Appendf( 15, "%d", nSRID );
         oWHERE.Append( ",NULL," );
         oWHERE.Append( "MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1)," );
         oWHERE.Append( "MDSYS.SDO_ORDINATE_ARRAY(" );
         oWHERE.Appendf( 600,
                 "%.16g,%.16g,%.16g,%.16g,%.16g,%.16g,%.16g,%.16g,%.16g,%.16g",
                         sEnvelope.MinX, sEnvelope.MinY,
                         sEnvelope.MaxX, sEnvelope.MinY,
                         sEnvelope.MaxX, sEnvelope.MaxY,
                         sEnvelope.MinX, sEnvelope.MaxY,
                         sEnvelope.MinX, sEnvelope.MinY);
         oWHERE.Append( ")), 'querytype=window') = 'TRUE' " );

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    | President OSGeo, http://osgeo.org