5

I have an ArcGIS Map Server Map Service connecting to an Oracle 11G instance via Direct Connect. There is no need for ArcSDE since our application doesn't need versioning, replication, or any other of the doo-dads that ArcSDE provides. All we want to do is plot some dots on a map.

The Oracle table(s) have a SDO Geometry column containing only point geometry types. I have implemented a spatial index, validated geometries, optimized tolerance...etc. However, the rendering performance of a layer pointing at SDO Geometry column is awful - 15-20 seconds to render 10k points with very simple symbology. I have actually found that using an event layer using raw x,y values is faster than using SDO geometry columns.

I will admit that I am more knowledgeable of MS SQL and PostGIS - but I've been pretty thorough in making sure I am doing good by Oracle. I am hoping someone with more experience using Oracle SDO Geometry in their map services can provide more guidance - are there maybe a few gotcha's I'm missing.

UPDATE (Per Vince's Request):

Spatial Index SQL: CREATE INDEX ON "SGSDWADM"."SERVICELOCATION"("GEOMETRY") INDEXTYPE IS MDSYS.SPATIAL_INDEX

USER_SDO_GEOM_METADATA row: enter image description here

Describe Output:

enter image description here

Improve this question
6
  • If you're connecting with Direct Connect, you're using ArcSDE. Are you really using a Query Layer? Are all 10k points plotted on every map? Not having an index would help a full table scan query.
    – Vince
    Oct 23, 2013 at 13:48
  • I am using a query layer - sorry for wrong terminology. 10k points would only be plotted at full extent. But even when I am drilled down to only a few points in view - still taking forever to draw...
    – user890
    Oct 23, 2013 at 14:28
  • Please update the question to include the contents of the USER_SDO_GEOM_METADATA row for the table, and the SQL you used to create the spatial index. DESCRIBE output would be a plus. Have you measured the query performance of various spatial queries from SQL*Plus? Have you tried generating traces to review the query plan?
    – Vince
    Oct 23, 2013 at 14:56
  • The geometry metadata isn't very legible, and it's missing the SRID (which would determine if 0.001 was too large a tolerance, or too small). Point-only tables are supposed to be indexed with "parameters('sdo_indx_dims=2, layer_gtype=point')"
    – Vince
    Oct 23, 2013 at 17:41
  • Vince - you rock. I am not sure why the SRID does not show in the table, but its set to 4326. However, changing to the spatial index you suggested offers a huge speed increase. Now my only issue is that ArcMap crashes when I try to do anything with this query layer.
    – user890
    Oct 24, 2013 at 12:08

1 Answer 1

Reset to default
3

Spatial indexes in general, and Oracle's spatial index in particular, tend to work better if single-point and multi-point objects are treated differently. In this case, that would mean using the PARAMETERS option in the CREATE INDEX command, as follows:

CREATE INDEX servicelocation_spx ON servicelocation(geometry)
    INDEXTYPE IS MDSYS.SPATIAL_INDEX 
    PARAMETERS ('sdo_indx_dims=2, layer_gtype=point')
/

If you have multiple independent disks, there would likely be benefit to specifying a tablespace on a drive other than the tablespace where the primary table lives (also inside the PARAMETERS option).

Here's an Oracle documentation link.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.