Performance with oraclespatial using ogr

[Umberto Nicoletti]

On Fri, 2004-11-19 at 10:07 -0500, Frank Warmerdam wrote:
> Merlijn Simonse wrote:
> > Frank,
> >
> > Thanks for your respond. In my database I have about ten different tables
> > with spatial data. The PK fields have a different name in every table. As
> > the OCI_FID CONFIG parameter is the same for all tables I probably better
> > add an OGR_FID field to the necessary tables.
> >
> > To test if a unique indexed OGR_FID column improves performance I added
> > this column to my table and also changed my DATA parameter to:
> > DATA "SELECT OGR_FID, GEOM, LOCATIE_ID from LOCATIE where LOCATIESOORT_ID
> > = 10" (I was wondering if "SELECT LOCATIE_ID as OGR_FID..." could also
> > work)
> >
> > I didn't notice any differencin performance. I also tried:
> > DATA "SELECT OGR_FID, GEOM, LOCATIE_ID from LOCATIE"
> >
> > Without the where clause it's very slow as well.
> > Am I not using the right DATA parameter to let it use my OGR_FID field?
>
> Merlijn,
>
> In fact, on reflection, I don't think OGR's OCI driver (nor any other driver
> for that matter) can do fast feature fetches from a user defined SQL operation.
>
> You could just use the table directly instead of providing your own SQL statement,
> and do the "winnowing" of results with normal MapServer mechanism (an EXPRESSION
> in a CLASS?). The downside of this is that all the records will be read the
> first time through, but the subsequent feature fetch should be fast.
>

Couldn't one use a view instead of a custom query? To OGR it will look
like it is accessing a table directly.

Regards,
Umberto

> Generally speaking, when you use user defined SQL with OGR, you lose all benefits
> of it's own spatial searching and feature id based fetches.  It is much better
> to "direct" table access letting OGR manage some of these things.  But currently
> there is no mechanism via MapServer to pass off an attribute query when doing
> direct table access. What I really ought to do is change the MapServer OGR
> binding so that you can say "<tablename> WHERE <query>" in the DATA statement
> and have this be interpreted as direct table access, applying the requested
> query.  This is really the case that OGR is optimized for.  But currently there
> is no way to accomplish this.
>
> In fact, I could hack this in quite easily with a PROCESSING statement that
> could hold the spatial query.  I wonder if Daniel would blow a gasket if I
> hacked that in today before beta3 is snapped!
>
> I would also echo the other response you got mentioning that the direct
> Oracle Spatial support in MapServer now apparently has query support.  If that
> can do the job for you, it is likely to be faster than even an optimized
> OGR implementation.
>
> 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
--
Umberto Nicoletti <unicoletti at prometeo.it>
Prometeo SRL