8 Replies Latest reply on May 9, 2014 7:01 PM by Stefan Jager

    SDO_Relate

    31a8ae03-ac4d-4909-8ee6-6d81138fb1ea
    31a8ae03-ac4d-4909-8ee6-6d81138fb1ea May 7, 2014 12:38 PM

      Is there wrongs in Oracle Spatial documentation !??


      My senario is:

      1. the Geom 1 is a one line geometry and comes from a table

      2. Geom2 is a ploygon and contains many geometries in a table also.

       

      I used the folowing syntax:

      select /*+ ordered */ ..from table2,table1 where SDO_Relate(Geom2,Geom1,'mask=anyintraction')= 'TRUE 

      which gives me the best performance (the fastest ==2 Minutes).

       

      Using the recommendation written in Spatial reference, i should use:

      from table1,table2 where SDO_Relate(Geom2,Geom1,'mask=anyintraction')= 'TRUE

      but i get worse performance! (4 Min.)

       

      Furthermore, ccording to oracle reference, it is possible to pass two or more Geometries to Geom2 (using ordered hint and putting Geom2's table at first in the from clause):

      ..SDO_Relate(Geom1,Geom2,'mask=anyintraction')= 'TRUE'

      which even much worse than the two above solutions.

        • 1. Re: SDO_Relate
          John O'Toole
          John O'Toole May 7, 2014 4:26 PM (in response to 31a8ae03-ac4d-4909-8ee6-6d81138fb1ea)

          Assuming the single "window" geometry is in table1 and the target geometry is in table2, then I'd suggest you write the query as follows:

           

          select /*+ ordered */ t2.id
from table1 t1, table2 t2
where sdo_anyinteract(t2.geom, t1.geom) = 'TRUE';

           

          If you're comparing a single geometry with lots of other geometries, then I'd be surprised if this query takes more than a few seconds - obviously that depends on your data, your environment, etc.

          What version of table are you on?  How many rows does the query return?  Are these very large and complex features?

           

          John

          • 2. Re: SDO_Relate
            Stefan Jager
            Stefan Jager May 9, 2014 8:02 AM (in response to John O'Toole)

            John O'Toole wrote:

            What version of table database are you on?

            FTFY

             

            John O'Toole wrote:

            Are these very large and complex features?

            If they are, a simple SDO_FILTER might speed things up. Depending on the accuracy of the comparison needed, of course, but since OP is using anyinteract I'd say the primary filter would be enough, if performance is such an issue.

            Cheers,

            Stefan

            • 3. Re: SDO_Relate
              31a8ae03-ac4d-4909-8ee6-6d81138fb1ea
              31a8ae03-ac4d-4909-8ee6-6d81138fb1ea May 9, 2014 1:15 PM (in response to John O'Toole)

              Hi John,

              the table1 has too many objects (rivers), but I do select only one object (geometry).

              the table2 has also too many regions/lands.

               

              Using your solution (the order of tables in the from-clause as recommended in the reference), it takes 4 min !

              but as i said, if I put the table2 (lands) at first, i get about 2 min!  so as SDO_Relate  exactly.

               

              The database is 10g.

              Thanks

              • 4. Re: SDO_Relate
                31a8ae03-ac4d-4909-8ee6-6d81138fb1ea
                31a8ae03-ac4d-4909-8ee6-6d81138fb1ea May 9, 2014 1:39 PM (in response to 31a8ae03-ac4d-4909-8ee6-6d81138fb1ea)

                I deceided to use the following>

                select B.someAttr from lands B, rivers A where sdo_relate(A.line,B.geom, 'mask=anyintract') ='True'

                and A.ID='value';

                 

                it gives me 58 seconds.

                Here i followed the recommendation of putting B at first in From-clause. But without the ordered hint which involved 6 mints!!!

                 

                Also to be mentioned, the qwery window is now multi-geometries.

                • 5. Re: SDO_Relate
                  Stefan Jager
                  Stefan Jager May 9, 2014 1:55 PM (in response to 31a8ae03-ac4d-4909-8ee6-6d81138fb1ea)

                  58 seconds is still ridiculously long for such a simple query. Can you give us your table descriptions, indexes, and your full exact query, and maybe some sample data so that we can reproduce your situation? Just a few records will do.

                   

                  Because the way you are doing it here is, in my opinion, not the correct way. It looks like you want to get some attribute from all lands that are crossed by a certain river, correct? I would query that as follows:

                   

                  select /*+ ORDERED */ L.countryname from
  rivers R,
  lands L
where
  R.rivername='Rhine' and
  sdo_relate(L.geom, R.line, 'mask=anyinteract')='TRUE';

                   

                  Also: if your select clause on the second geometry (A.ID='value', in your example) only returns one row, it will speed up the spatial selection. Does that ID-column have an index?

                  • 6. Re: SDO_Relate
                    31a8ae03-ac4d-4909-8ee6-6d81138fb1ea
                    31a8ae03-ac4d-4909-8ee6-6d81138fb1ea May 9, 2014 3:45 PM (in response to Stefan Jager)

                    Hello Stefan,

                    first, yes correct. Iknow that my Solution seems to be not the best but it is till now '!, However, it was the last Experiment i did.

                    This is why i put this post, cus every thing i read in the Spatial reference of Oracle relating Performance optimization seems to be incorrect. !

                     

                    Your solution was the first i've tried (it is listed in the reference) -> it takes 4 min. What is more, no matter i use ordered hint or not, it always 4 min!

                     

                    lastly, sorry i can't describe my data cus it is not mine. but the result is abot 1000 rows from thousands of rows.

                    • 7. Re: SDO_Relate
                      31a8ae03-ac4d-4909-8ee6-6d81138fb1ea
                      31a8ae03-ac4d-4909-8ee6-6d81138fb1ea May 9, 2014 5:45 PM (in response to 31a8ae03-ac4d-4909-8ee6-6d81138fb1ea)

                      Well, I apply your query to the follwoing tables:

                       

                      Table2 has about 15ooo ploygons.

                      The so-called qwery window is a line geometry from a table contains 1000 line.

                      The probed line  interacts topologically with 300 Polygons.

                      select '/*+ ORDERED */' count(P.name)

                      from lines L, polygons P

                      where L.name='' and

                      sdo_relate(P.geom,L.geom,'mask=anyintract')= 'True'2 min !!!

                       

                      Now using Count(), this is really the best solution. all other orders(Solutions) take more and some of took 10 min.

                       

                      Thank you.

                      1 person found this helpful
                      • 8. Re: SDO_Relate
                        Stefan Jager
                        Stefan Jager May 9, 2014 7:01 PM (in response to 31a8ae03-ac4d-4909-8ee6-6d81138fb1ea)

                        Well, if you can't or won't give us some more information about your tables, indices etc there's not much we can do I'm afraid.

                         

                        I do know that those response times are ridiculous, so there must be something not right. I've worked with tables that had 9-15 million records in them and have had response times of seconds. 15.000 records is nothing for Oracle. Can you post an explain plan of your query? That might give us some indication at least.