[mapserver-users] Mapserver-- Opracle --SDO_relate function very slow via Mapserver

[Rotenhan von, Wernher]

Hello List

Im Running Mapsrever 6.0.2 on Oracle 11g Database

I want to get all geometries that interact with a geometry I clicked within
So I have todo two encapsulated sdo_relate calls :
--The inner one to get the geometry ic clicked in
--The outer one to get the geometries which touch the clicked geometries

This runs very fast within my oracle developer ( 0.09 sec):

SELECT identnolong,
  gresult.geodata GEODATA
FROM rms.zrgeo gresult
WHERE gresult.GEOTYPECODE = '49'
AND SDO_RELATE ( gresult.geodata ,
                                                                                      (SELECT gtouching.geodata
                                                                                                  FROM rms.zrgeo gtouching
                                                                                                  WHERE gtouching.geotypecode = '49'
                                                                                                  AND gtouching.IDENTNOLONG   =
                                                                                        (SELECT MAX(gwithin.IDENTNOLONG)
                                                                                                    FROM rms.zrgeo gwithin
                                                                                                    WHERE SDO_RELATE (
                                                                                                    gwithin.geodata ,
                                                                                                    MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(10.31423,49.692,NULL),NULL,NULL)
                                                                                                    , 'mask=ANYINTERACT') = 'TRUE'
                                                                                                    AND gtouching.geotypecode ='49'
                                                                               )
                  )
               , 'mask=ANYINTERACT') = 'TRUE';

But if I include it into my Map file
the Query becomes very slow 17 sec
If I force Oracle not to make use of ths spacial index I get nearly the same response time

So is there any possibility to force the use of the spatial indexes ?
"USING SRID 8307"   etc does not Work !!!  because the resultset  is a query and I have to add
"Using NONE"

Would be very nice if you could hepl me !

Regard Wernher




The information contained in this email is confidential. It is intended solely for the addressee. Access to this email by anyone else is unauthorized. If you are not the intended recipient, any form of disclosure, reproduction, distribution or any action taken or refrained from in reliance on it, is prohibited and may be unlawful. Please notify the sender immediately.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20130506/f8a842c2/attachment.html>