Forum Stats

  • 3,827,873 Users
  • 2,260,836 Discussions
  • 7,897,401 Comments

Discussions

Oracle Spatial performance question

659395
659395 Member Posts: 7
edited Sep 19, 2008 2:36PM in Spatial Discussions
All,

I am doing a performance test on Oracle 11g Spatial. I am simulating doing searches in 10 degree by 10 degree windows over 6M+ images, six arc minutes per side. Here is my spatial query construction:

String intersectSQL = "SELECT A.name, A.GEOMETRY.Get_WKT() " +
"FROM six_amin_polygons A " +
"WHERE SDO_RELATE(A.GEOMETRY,?, " +
"'mask=inside+coveredby+overlapbdyintersect')='TRUE'";

where the question mark is replaced by the geometry structure of the search window. The results for the first few searches are fast, then the query times balloon very quickly. PostGIS/PostgreSQL performs these searches in an average time of 30 s per window.

Here are the initial (first four rows) of Oracle Spatial results:
area_idx area_name sql_query_time number_results
0 S80.0W90.0 3890 10100
1 S80.0W80.0 3124 10100
2 S80.0W70.0 186484 10100
3 S80.0W60.0 183077 10100

Any ideas? Am I using the best mask for image/area intersection? Please advise.

Thanks,

Jeff

Answers

  • Siva Ravada-Oracle
    Siva Ravada-Oracle Member Posts: 1,055 Employee
    Jeff,
    Most of this time is probably going into the conversion of to WKT.
    Take that conversion out of the SELECT and see how it works.

    And do you really need the mask to be inside+coveredby+overlapbdyintersect.
    Can you just do ANYINTERACT mask ?

    siva
  • 659395
    659395 Member Posts: 7
    Hi Siva,

    Thanks for the response. I am rebuilding the indices, now, as a precaution. I will try removing that part of the select from the query.

    As for the mask, would the mask I specified produce the same results as "anyinteract" for intersecting polygons?

    Jeff
  • Siva Ravada-Oracle
    Siva Ravada-Oracle Member Posts: 1,055 Employee
    With anyinteract you get

    inside+coveredby+overlapbdyintersect+touch

    since you are comparing polygons to polygons.


    Do you want polygons that touch the window geometry in the result ? Do you want all the geometries
    that have some kind of intersection with the window query ? Then you should use ANYINTERACT mask.

    siva
  • 659395
    659395 Member Posts: 7
    Siva,

    Returning the geometry object instead of a text object created within the DB and using JGeometry to resolve the point arrays in the object reduced the query times down to the 3 to 4 second range over 6M+ objects, which is acceptable. Thank you for your help.

    Jeff
This discussion has been closed.