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
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
I am using the Arcgis 10.0.
A complex polygon(multiple polygon) may contain 2000+ points.
CREATE INDEX spatialidx_landuse_polygon ON landuse_polygon(geom) INDEXTYPE IS sde.st_spatial_index PARAMETERS('st_grids=1,0,0 st_srid=3')
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
Query plan: I have checked the query and the query for the table of polygons take too long.