AnsweredAssumed Answered

Speed up ST Functions

Question asked by nschleifer5555 on Feb 17, 2015
Latest reply on Feb 18, 2015 by nschleifer5555

Like • Show 1 Like1
Comment • 5

We are working on migrating our existing applications from SDO_Geometry into ST_Geometry (Oracle 11g - SDE 10.2.2).

One of our applications uses an iPad to get the X, Y coordinates and locate the closest linear asset.

Our SDO sql statement was simple:

WHERE SDO_WITHIN_DISTANCE ( A.AREAGEOMETRY ,mdsys.sdo_geometry(2001, 8307,

mdsys.sdo_point_type( -78.2990416, 44.29297897, NULL), NULL, NULL), 'distance=75') = 'TRUE'

The response time is 196 msecs

Attempting to duplicate the results using ST functions is a little frustrating

I have not been able to us the x, y points from the iPad at all, so I've been uisng the UTM coordinates.

I've been able to reproduce the results using the UTM coordinates but the response time is way too slow (see below)

14 secs

where sde.st_intersects (sde.st_buffer(sde.st_point(715480.6, 4907962.3, 26917), 15 ),pl.shape) = 1

11 secs

where sde.st_distance (sde.st_point(715480.6, 4907962.3, 26917),pl.shape) < 15

9 secs

where sde.st_distance (sde.st_geometry(715480.6, 4907962.3,null,null, 26917),pl.shape) < 15

Does anyone have any advice for how to speed the response time up?

Are these the correct sql functions? or am I missing one?

Please let me know.

vangelo-esristaff

Feb 17, 2015 2:27 PM

Correct Answer

Not having the data, and not having the time to simulate some, I'm not fond of specifying a query, but something like:

WHERE sde.st_envintersects(pl.shape,715480.6-15,4907962.3-15,715480.6+15,4907962.3+15) = 1

and sde.st_distance (sde.st_geometry(715480.6, 4907962.3,null,null, 26917),pl.shape) < 15

ought to work reasonably quickly.

You need to review the query plan to determine whether the index is being used, but I can't give you further information than that. You may need to work with Tech Support to determine the optimal query (it doesn't help that the old query was in WGS_1984). It's possible that a 530 meter index grid is inappropriate for that dataset (but couldn't say for sure without the data).

BTW: change the "15"s to "75"s if you want a similar result as Oracle with a "75" query.

- V

See the reply in context

No one else had this question

Outcomes

vangelo-esristaff

Feb 17, 2015 11:59 AM

The st_distance query is doing a full table scan (not using the spatial index), so you need to get the spatial index in play by using sde.ST_ENVINTERSECTS in the first term of the WHERE clause. The ST_INTERESECTS query should have been enough to engage the spatial index, so I also wonder if you have the index available.

- V

1 person found this helpful

Actions

nschleifer5555 @ vangelo-esristaff on Feb 17, 2015 12:18 PM

We have the standard Spatial Index created through the desktop tools:

INDEXTYPE IS SDE.ST_SPATIAL_INDEX

PARAMETERS('ST_GRIDS = 530 ST_SRID = 26917 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4')

NOPARALLEL;

Is there a way to test if the query is calling the spatial index?

Secondly, I'm a little unsure of how/where to use the st_envintersects ? Am I replacing the st_intersect with it? or do I need to use them both?

Actions

vangelo-esristaff

@ nschleifer5555 on Feb 17, 2015 2:27 PM

Not having the data, and not having the time to simulate some, I'm not fond of specifying a query, but something like:

WHERE sde.st_envintersects(pl.shape,715480.6-15,4907962.3-15,715480.6+15,4907962.3+15) = 1

and sde.st_distance (sde.st_geometry(715480.6, 4907962.3,null,null, 26917),pl.shape) < 15

ought to work reasonably quickly.

BTW: change the "15"s to "75"s if you want a similar result as Oracle with a "75" query.

- V

2 people found this helpful

Actions

nschleifer5555 Feb 18, 2015 5:42 AM

Totally understandable. You're response is appreciated.

I will continue on this path.

Thanks

Actions

nschleifer5555 Feb 18, 2015 6:37 AM

THANK-YOU SO MUCH! It works perfectly!!!

Response time is now 264 msecs......... SO EXCITING!!!

Actions

5 Replies

vangelo-esristaff Feb 17, 2015 11:59 AM
Mark Correct
Correct Answer
The st_distance query is doing a full table scan (not using the spatial index), so you need to get the spatial index in play by using sde.ST_ENVINTERSECTS in the first term of the WHERE clause. The ST_INTERESECTS query should have been enough to engage the spatial index, so I also wonder if you have the index available.

- V
1 person found this helpful
Like • Show 0 Likes0
Actions
- nschleifer5555 @ vangelo-esristaff on Feb 17, 2015 12:18 PM
  Mark Correct
  Correct Answer
  We have the standard Spatial Index created through the desktop tools:
  INDEXTYPE IS SDE.ST_SPATIAL_INDEX
  PARAMETERS('ST_GRIDS = 530 ST_SRID = 26917 ST_COMMIT_ROWS = 10000 PCTFREE 0 INITRANS 4')
  NOPARALLEL;
  Is there a way to test if the query is calling the spatial index?
  
  Secondly, I'm a little unsure of how/where to use the st_envintersects ? Am I replacing the st_intersect with it? or do I need to use them both?
  Like • Show 0 Likes0
  Actions
  - vangelo-esristaff @ nschleifer5555 on Feb 17, 2015 2:27 PM
    Unmark Correct
    Correct Answer
    Not having the data, and not having the time to simulate some, I'm not fond of specifying a query, but something like:
    
    WHERE sde.st_envintersects(pl.shape,715480.6-15,4907962.3-15,715480.6+15,4907962.3+15) = 1
    and sde.st_distance (sde.st_geometry(715480.6, 4907962.3,null,null, 26917),pl.shape) < 15
    
    ought to work reasonably quickly.
    
    You need to review the query plan to determine whether the index is being used, but I can't give you further information than that. You may need to work with Tech Support to determine the optimal query (it doesn't help that the old query was in WGS_1984). It's possible that a 530 meter index grid is inappropriate for that dataset (but couldn't say for sure without the data).
    
    BTW: change the "15"s to "75"s if you want a similar result as Oracle with a "75" query.
    
    - V
    2 people found this helpful
    Like • Show 1 Like1
    Actions
nschleifer5555 Feb 18, 2015 5:42 AM
Mark Correct
Correct Answer
Totally understandable. You're response is appreciated.
I will continue on this path.

Thanks
Like • Show 0 Likes0
Actions
nschleifer5555 Feb 18, 2015 6:37 AM
Mark Correct
Correct Answer
THANK-YOU SO MUCH! It works perfectly!!!

Response time is now 264 msecs......... SO EXCITING!!!
Like • Show 0 Likes0
Actions

GeoNet

The Esri Community

Speed up ST Functions

Outcomes

Related Content

Recommended Content