0

We are using ST_Geometry for Oracle 11g at the moment.

And we need to query features (points,lines,polygons in a view) inside a given extent(either a circle or a polygon or a rectangle), and we use the st_envintersects to do the spatial calculation.

However since some of the features are poygons which make the query rather slowly even we create indexes for the geometry column of each feature.

For example there is a view feature LView with columns:

objectid
lon(
lat
geom(the geometry, point or polygon or polyline)
...

And we use the following sql to do the query:

select * from Landuse l where sde.st_envintersects(l.geom,sde.st_geometry('POLYGON((....))',3)) = 1;

It will take almost 2-3 minutes to do the querying and there is only 2000 records in the view.

Then when we re-create the LView and remove the features of polyon and polylines whose geometry is too complex.

The same query will take only a few seconds.

It seems that the spatial calculation for complex the polygon will slow down the query. So I want to treat the polygons as points(by the lon lat column) during the query.

Something like this:

select * from Landuse l where sde.st_envintersects(sde.st_geometry('Point(l.lon,l.lat)',3),sde.st_geometry('POLYGON((....))',3)) = 1;

Is this possible


Update:

I am using the Arcgis 10.0.

A complex polygon(multiple polygon) may contain 2000+ points.

Index: CREATE INDEX spatialidx_landuse_polygon ON landuse_polygon(geom) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=1,0,0 st_srid=3')

View:

create or replace view LView as
  select objectid,geom,'landuse_polygon' as table_name, lon, lat from landuse_polygon 
union all
    select objectid,geom,'school_point' as table_name, lon, lat from school_point 
    ....................

The view contains almost 20 + tables.

I have not register the view with sde. ( I did not know this at all).

select count(*) from LView will give result of 2670.

Query plan: I have checked the query and the query for the table of polygons take too long.

  • Please edit the question to address: What version of ArcGIS was used to create ST_GEOMETRY? How large is your well-known text string? How did you construct your index? How did you create the view? Did you register the view with ArcSDE? How many total rows are in the table? Have you reviewed the query plan? – Vince May 14 '15 at 13:54
  • You CANNOT do a UNION ALL join that merges different topology classes into one return result. This will not function correctly, even if all the indexes are identical and the rowid columns have unique values (so that the result set does not have duplicate objectids). Depending on the nature of the queries, a one degree grid size may inappropriate. – Vince May 15 '15 at 2:52
  • I checked again, and the index is created through arcmap instead of manually. – giser May 15 '15 at 4:02
  • If there's more than one table, there's more than one index, though this has little bearing on the unsupportable nature of your query. – Vince May 15 '15 at 10:54
  • But we do have the requirement to query different types of features in a single query, how do you handle this? – giser May 15 '15 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.