Using a spatial query within a view - very slow

[soniadickerson1]

Using the following:
Oracle 11g, SDE 10.0

I've been able to create both a view and an sde view that contains a spatial query (sde.st_intersects).  Querying this view is very slow and it only contains 8000 records.  The points table contains 8000 records and the regions table contains 20 records.  It takes about 4 minutes to generate the view (either one) and about the same amount of time to get results from any query. 

Any suggestions?  Is there a better way to do this, perhaps?

Here's the code from the view

CREATE OR REPLACE FORCE VIEW schema.VW_REGIONS_TEST
(
   REGION,
   AREA,
   POINT_ID
)
AS
   SELECT      regions.REGION AS region,
          regions.AREANAME AS area,
          points.globalid AS point_id
     FROM schema.ADMIN_BNDY regions, schema.POINTS points
    WHERE sde.st_intersects (regions.shape, points.shape) = 1;




Here's the code from the sdetable create:

sdetable -o create_view
-T VW_SDE_POINTS_REGIONS
-t "schema.POINTS, schema.ADMIN_BNDY???
-c "POINTS.OBJECTID, POINTS.GLOBALID, ADMIN_BNDY.REGION,ADMIN_BNDY.AREANAME"
-w "sde.st_intersects(ADMIN_BNDY.shape,POINTS.shape)=1"
-i sde:oracle11g:LRSSDE01:NPC_FOR
-u ************
-p ******


Thank you very much,
Sonia

[VinceAngelo]

What is your goal here?  From all appearances, you've trying to return potentially
duplicate polygons for each row with a point id. 

You might have some polynomial expansion as well.

Have you tried using the ST_WITHIN operator.

What is the state of the spatial index on the ADMIN_BNDY table?

- V

[VinceAngelo]

So, just for grins, I populated two random tables, with 20 rectangles partitioning
a georegion, and 8000 random points, and three views:

% asc2sde -o create -l rectangles,poly -g 5 -C rect.ctl

ASCII to ArcSDE 10.1 Loader Utility      Mon Feb 25 14:33:45 2013
------------------------------------------------------------------------
Results:
        Records read: 20
        Rows created: 20
         Insert time: 39.64 ms (504.49 TPS)
        Elapsed time: 993.10 ms

% asc2sde -o create -l locations,point -g 3 -C pnts.ctl

ASCII to ArcSDE 10.1 Loader Utility      Mon Feb 25 14:34:11 2013
------------------------------------------------------------------------
Results:
        Records read: 8000
        Rows created: 8000
         Insert time: 1.27 secs (6290.73 TPS)
        Elapsed time: 3.04 secs

  1  create view forum_view as
  2  select l.objectid loc_id,r.objectid rec_id,r.poly
  3  from locations l,rectangles r
  4* where SDE.st_intersects(r.poly,l.point) = 1
SQL> /

View created.

% sdelayer -o register -l forum_view,poly -e na+ -R 3 -C loc_id,USER -t ST_GEOMETRY

ArcSDE 10.1  for Oracle11g Build 768 Thu Aug 23 15:57:13  2012
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.

% sdequery -N -t locations                                     

ArcSDE 10.1 Generic Query Tool           Mon Feb 25 14:35:29 2013
------------------------------------------------------------------------
8000 rows found in 220.49 ms

% sdequery -N -t rectangles                                    

ArcSDE 10.1 Generic Query Tool           Mon Feb 25 14:35:36 2013
------------------------------------------------------------------------
20 rows found in 10.89 ms

% sdequery -N -t forum_view

ArcSDE 10.1 Generic Query Tool           Mon Feb 25 14:35:48 2013
------------------------------------------------------------------------
8000 rows found in 5.47 secs
 
  1  create view forum_view2 as
  2  select l.objectid loc_id,r.objectid rec_id,r.poly
  3  from locations l,rectangles r
  4* where SDE.st_contains(r.poly,l.point) = 1
SQL> /

View created.

% sdelayer -o register -l forum_view2,poly -e na+ -R 3 -C loc_id,USER -t ST_GEOMETRY

ArcSDE 10.1  for Oracle11g Build 768 Thu Aug 23 15:57:13  2012
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.

% sdequery -N -t forum_view2

ArcSDE 10.1 Generic Query Tool           Mon Feb 25 15:05:36 2013
------------------------------------------------------------------------
8000 rows found in 5.56 secs

  1  create view forum_view3 as
  2  select l.objectid loc_id,r.objectid rec_id,r.poly
  3  from locations l,rectangles r
  4* where SDE.st_within(l.point,r.poly) = 1
SQL> /

View created.

% sdelayer -o register -l forum_view3,poly -e na+ -R 3 -C loc_id,USER -t ST_GEOMETRY

ArcSDE 10.1  for Oracle11g Build 768 Thu Aug 23 15:57:13  2012
Layer    Administration Utility
-----------------------------------------------------
Successfully Created Layer.

% sdequery -N -t forum_view3

ArcSDE 10.1 Generic Query Tool           Mon Feb 25 15:15:32 2013
------------------------------------------------------------------------
8000 rows found in 1.10 secs

As you can see, they were all pretty quick, but the ST_WITHIN was the fastest.

Your performance will depend on:
1) the simplicity of the polygons, and
2) the efficiency of the spatial index

- V

[soniadickerson1]

Thank you so much for taking a look at this.

You asked about the purpose- its for a crystal report.  For the point report, we need to know which region polygon its in.  I've tried several different methods but thought it would be best to keep this on the database side and build a view of the point data and its respective region using a spatial query.

Again, perhaps there is a better way to do that.

I appreciate you giving this a try.  I'll apply the st_within function and report back.

[VinceAngelo]

When I increased the vertex density of my rectangles (from 5 to 361), my query time
increased by a factor of four.  I also tried using a LEFT OUTER JOIN in the SQL, but
that was twice as long.

The problem here is the 8000 spatial queries -- Even if one query is relatively efficient
(one millisecond), 8000 of them still take 8 seconds.  Since 30 milliseconds is not
uncommon for a complex spatial query, 4 minutes is not unreasonable for this join.

You might try driving the query the other way -- 20 ST_CONTAINS will be faster than
8000 ST_WITHINs, but boundary conditions could generate more (or less) than the
full point complement on output.

In many ways, you're better off materializing this view (CREATE TABLE join_result AS ...)
and recreating it if the geometries change.

- V

[VinceAngelo · Accepted solution]

One more on this topic...

This gave the best performance in SQL with the vertex-dense rectangles:

  1  CREATE OR REPLACE VIEW forum_view10 AS   2  SELECT l.objectid loc_id,r.objectid rec_id,l.point   3  FROM rectangles2 r   4  LEFT OUTER JOIN locations l   5*    ON SDE.st_within(l.point,r.poly) = 1 SQL> /  View created.  % sdelayer -o register -l forum_view10,point -e np -R 3 -C loc_id,USER -t ST_GEOMETRY  ArcSDE 10.1  for Oracle11g Build 768 Thu Aug 23 15:57:13  2012 Layer    Administration Utility ----------------------------------------------------- Successfully Created Layer.  % sdequery -N -t forum_view10  ArcSDE 10.1 Generic Query Tool           Tue Feb 26 10:20:40 2013 ------------------------------------------------------------------------ 8000 rows found in 988.24 ms 

Outside the SQL realm, I have a high-performance query tool that uses a RAM cache for
identity operations, and it took just 107 milliseconds (beyond cache construction) to query
the large polygons with 8000 points:

% cat ident.ctl COORDREF_XY     -210,-120,1000000 COORDSYS        GCS_WGS_1984 ENVELOPE        0,0,20,20 SKIP            1 REGISTER        loc_id(USER) META            id1:VERBOSE="TRUE" META            id1:SUMMARIZE="true" META            id1:TABLE="rectangles2" META            id1:COLUMN="poly" META            id1:ALIAS="point" META            id1:USE_CACHE="true" COLUMNS loc_id          Int32                   -  10   N rec_id          Identity(id1,objectid)  -  10   Y point           WKTShape                -   1   Y END  % sdequery -t locations -C objectid,point -d - -q | \   ascinfo -o scan -f - -C ident.ctl  ArcSDE 10.1 Dynamic ASCII Table Utility  Tue Feb 26 10:39:07 2013 ------------------------------------------------------------------------ ! id1: Cache size limited to 128.00Mb ! id1: Connecting to instance 'localhost:esri_sde' as 'staff'... ! id1: Connection created at Tue Feb 26 10:39:07 2013 ! id1: 20 features cached using 124.8Kb in 85.52 ms 8000 rows found in 271.95 ms  ! id1: 8000 features found in 194.05 ms (24 us/query) ! id1: Disconnected at Tue Feb 26 10:39:07 2013 8000 rows read in 237.88 ms 

For 80k random points, the time per query would be less (14us mean), since
the polygon read time is distributed across more rows.

- V

[ThomasColson]

Can your force a hint on the spatial index within the query? I forget the exact commands, but there are some tools out there to investigate SQL Spatial queries, which, in SQL 2008, have HORRIBLE performance because of lack of honoring spatial indexes in some scenarios. Are you able to see the query execution plan in management studio?

[soniadickerson1]

Using the left outer join with the within function, I built a view and found that in our system, it had the same performance as an indexed table so we are just going to use a view.  I had planned to us a table that had update triggers but when I found that the performance was the same, there was no reason to continue down that path.

If we need to speed things up,  I'll look into hints to see if that might make a diffference. 

Your replies have been very beneficial. 
Thank you for all of your help.
Sonia