Oracle spatial query

[Ian]

In a layer for GPS sites I am using this query:

    DATA "SHAPE FROM (select 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] [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] [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 as
id,s.site_idas 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] [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] [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 as
id,s.site_idas 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.2and Oracle 9i.

Thank you,

Ian
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20061121/f15a7cdc/attachment.htm>