Oracle Spatial performance question

659395

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

Siva Ravada-Oracle

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

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

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

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