1

I am using ESRI ST_Geometry in my application, and I found that the spatial query is rather slow:

The following sql is used for query pois inside a certain bounding_box:

select * from ( 

    select name, st_astext(shape) as shape from POIView h where 1 = 1 and ST_Intersects(h.shape, st_geometry('POLYGON ((20.227742
        30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1 
) where rownum <= 10

Count the total nums:

select count(shape) from POIView h                    where 1 = 1 and ST_Intersects(h.shape, st_geometry('POLYGON ((20.227742
    30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1

And the first sql will cost almost 3 seconds, and the second will cost 4+ minutes which is unacceptable.

What' more, the bounding_box is a rather simple polygon with a small area, I can not image how long it will cost once I put a complex polygon as the argument.

Is it possible to fix this?

BTW, all the tables are exported to Oracle 11g by ArcMap 10.0 with the sde connection, and we have only 120k records in the table.

Update: the view:

CREATE OR REPLACE VIEW POIView (objectid, shape, id, name, address, table_name) AS
    SELECT objectid,shape,hotel_id as id,hotel_name as name,address,'HotelPoint' AS table_name from HotelPoint
union ALL
    SELECT objectid,shape,school_id as id,school_name as name,address,'SchoolPoint' AS table_name from SchoolPoint
union ALL
    SELECT objectid,shape,id,name,address,'ImportFeaturePoint' AS table_name from ImportFeaturePoint
union ALL
    SELECT objectid,shape,id,name,address,'ImportFeatureLine' AS table_name from ImportFeatureLine
union ALL
    SELECT objectid,shape,id,name,address,'ImportFeaturePolygon' AS table_name from ImportFeaturePolygon
union ALL
    .....

And once I followed the comment from @Vince, tried to change the sql to:

select * from ( 

    select name, st_astext(shape) as shape from POIView h where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
        30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1 
) where rownum <= 10

It take 6 seconds now. And the count sql:

select count(shape) from POIView h   where ST_ENVINTERSECTS(h.shape, st_geometry('POLYGON ((20.227742
    30.1681829, 20.296578 30.168182, 20.296578 30.21127,20.22774 30.211270, 20.22774 30.16818))',3)) = 1

takes 240 seconds.

I am downloading the sp5, I will post the new result after I install the patch.

Update after I installed the SP5, and I found that both the query will take longer than before with 24 seconds for the basic query, and 2 minutes for the count query.

Improve this question
12
  • 1
    Why are you specifying "1 = 1"? Envelope queries should use SDE.ST_ENVINTERSECTS. ArcGIS 10 is elderly; have you applied SP5?
    – Vince
    Commented Mar 20, 2015 at 11:31
  • Is the POIView source really a view? If it is, how is it constructed, and how many rows are in the base table?
    – Vince
    Commented Mar 20, 2015 at 14:20
  • Yes, the POIView is a view created manually in the database. The view select rows from 6+ tables, and the total rows are less than 25k. And why I use 1 =1 is that I have to build the where cause for the sql by different query condition, and condition may be null. I have not applied the patches atm, I will have a try.
    – giser
    Commented Mar 21, 2015 at 14:11
  • Joining 6 tables is likely the cause of your poor performance, especially when combined with a "WHERE 1 = 1" (which may coax the optimizer to give up on the spatial index entirely). You will need to provide the join SQL (by editing the question) before we could be of any assistance. SP5 is 32 months old, nearly two computer generations; please do apply it (the patches that followed) soon.
    – Vince
    Commented Mar 21, 2015 at 16:51
  • I update my post, and have applied the Service pack5
    – giser
    Commented Mar 23, 2015 at 1:07

0

Reset to default

Your Answer

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