Oracle spatial query

[Fernando Simon]

Hi Ian,
    This issue it's relate with the way how Mapserver executes the query's.
    Mapserver core call's the Oracle Spatial driver to request the data,
but juts forward one by one record. This error occur for all the
database drivers (Oracle Spatial, PostGIS). One suggestion to improve
the query's in Oracle server it's have a index in your ID column. Did
you measure (time to finish) the query from Mapserver using SQLPlus
directly?
    The FILTER parameter just will help to draw the map, don't help in
query mode. I suggest to you to always use the FILTER parameter (or
RELATE if you need more precision in the bbox) to improve the performance.
    Best regards.

------------------------------------------------------------------------
Fernando Simon
Mapserver and Oracle Spatial developer
G10 - Laboratorio de Computacao Aplicada - Brazil
http://www.univali.br/g10 - UNIVALI/CTTMAR
------------------------------------------------------------------------
   

Ian wrote:
> In a layer for GPS sites I am using this query:
>
>     DATA "SHAPE FROM (select s.id <http://s.id> as id,s.site_id as
> site_id,upper(s.site_code) as site_code,s.shape,nvl(s.city,'n/a') as
> city,sa.array_code as array_code,nvl(s.site_name,'n/a') as name from
> site s, site_affiliation sa where s.site_id = sa.site_id and
> s.site_type_code = 'CONGPS' and sa.array_code in
> ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) USING
> UNIQUE ID SRID 8307 NONE VERSION 9i"
>
> The query works fine on its own, but when run via MapServer the query
> is run for EACH matching record, so the apache error log looks like:
>
> [Tue Nov 21 11:20:02 2006] [error] [client 132.239.152.99
> <http://132.239.152.99>] [Tue Nov 21 11:20:02 2006].167983
> msOracleSpatialLayerGetShape was called. Using the record = 7234.
> [Tue Nov 21 11:20:02 2006] [error] [client 132.239.152.99
> <http://132.239.152.99>] [Tue Nov 21 11:20:02 2006].168002
> msOracleSpatialLayerGetShape. Sql: SELECT ID, SITE_ID, SITE_CODE,
> CITY, ARRAY_CODE, NAME, SHAPE FROM (select s.id <http://s.id> as
> id,s.site_id as site_id,upper(s.site_code) as
> site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as
> array_code,nvl(s.site_name,'n/a') as name from site s,
> site_affiliation sa where s.site_id = sa.site_id and s.site_type_code
> = 'CONGPS' and sa.array_code in
> ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) WHERE ID
> = 7234
> [Tue Nov 21 11:20:02 2006] [error] [client 132.239.152.99
> <http://132.239.152.99>] [Tue Nov 21 11:20:02 2006].172106
> msOracleSpatialLayerGetShape was called. Using the record = 7390.
> [Tue Nov 21 11:20:02 2006] [error] [client 132.239.152.99
> <http://132.239.152.99>] [Tue Nov 21 11:20:02 2006].172125
> msOracleSpatialLayerGetShape. Sql: SELECT ID, SITE_ID, SITE_CODE,
> CITY, ARRAY_CODE, NAME, SHAPE FROM (select s.id <http://s.id> as
> id,s.site_id as site_id,upper(s.site_code) as
> site_code,s.shape,nvl(s.city,'n/a') as city,sa.array_code as
> array_code,nvl(s.site_name,'n/a') as name from site s,
> site_affiliation sa where s.site_id = sa.site_id and s.site_type_code
> = 'CONGPS' and sa.array_code in
> ('SCIGN','BARD','PANGA','BARGN','PBO','EBRY','WCDA','AKDA')) WHERE ID
> = 7390
> ...etc
>
> On queries that return many results this really bogs down the server
> and performance suffers big time. It seems like using FILTER wouldn't
> make a difference since it would just replace the WHERE clause that
> already exists in the query... Ideas? Something I'm doing wrong? This
> is in MapServer 4.6.2 and Oracle 9i.
>
> Thank you,
>
> Ian