Community

Poor performance of spatial query in view with multiple geometry type unioned

3755
0
03-22-2015 11:09 PM
kkkk1
by
New Contributor II
‎03-22-2015 11:09 PM

Hi:

I am using Arcgis SDE 10 to export our geographic data to the Oracle 11g database, and the spatial column are saved using the `St_Geometry` type.

And we have almost 10 + tables with points, lines, and polygons.

However we have to query the data despite of their geometry type, so I create a view which union all the required table and columns to the view, like this:

create or replace view MyView as
  select objectid,shape,name,address, 'FeaturePY' as table_name from FeaturePY
union all
  select objectid,shape,name,address, 'FeatureLN' as table_name from FeatureLN
union all
  select objectid,shape,name,address, 'FeaturePY' as table_name from FeaturePY
........

Everything works well unless I try to query the data by some spatial , for example, I want to query data inside the bounding box of

(20.22774247419824,30.168182996158205,20.29657868635644 30.21127000177344)

and make a pagination, I use the following two sqls:

1) for data

select * from ( select name,st_astext(shape) as shape from MyView h where ST_Intersects(h.shape, st_geometry('POLYGON ((20.22774247419824 30.168182996158205, 20.29657868635644 30.168182996158205, 20.29657868635644 30.21127000177344, 20.22774247419824 30.21127000177344, 20.22774247419824 30.168182996158205))',3)) = 1 ) where rownum <= 10

2) for total count:

select count(shape) from MyView h where ST_Intersects(h.shape, st_geometry('POLYGON ((20.22774247419824 30.168182996158205, 20.29657868635644 30.168182996158205, 20.29657868635644 30.21127000177344, 20.22774247419824 30.21127000177344, 20.22774247419824 30.168182996158205))',3)) = 1

However I found that the first sql will cost almost 30 seconds to get the data, and the second sql will take almost 6 minutes to get the count which is unacceptable.

And I have tried to use a different spatial function `ST_ENVINTERSECTS` to replace the `ST_Intersects`, but I can not get any improvement.

This is my current environment:

Dbserver: Windows server 2008 x64
Arcgis SDE 10 x64 with service pack5 installed.
Oracle 11gR2 x64
Records in the view: almost 150,000

I am not sure what's the problem, any suggestion to fix this?

0 Replies