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

[Simon Mercier]

Hi Rotenhan

You should try to add DEBUG 5 in your layer to log the SQL build by 
Mapserver and try to see what going wrong with spatial index (explain 
plan in psql).

Simon



Le 13-05-06 11:11 AM, Rotenhan von, Wernher a écrit :
>
> 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.
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users


-- 
simon mercier
co-fondateur solutions mapgears
2383 che ste-Foy bur 202 québec, qc
canada G1V1T1

t_418_476_7139#101
m_418_559_7139
simonmercier.net / mapgears.com

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