topic Need to test whether ST_GEOMETRY is working properly on database server. in Data Management Questions

Need to test whether ST_GEOMETRY is working properly on database server.

OvidioRivero — Wed, 28 Apr 2010 20:03:39 GMT

Hello all,

Kudos to ESRI for the look of the new pages. Everything looks new and shiny.

I am trying to test if the configuration for ST_GEOMETRY on our server is working correctly.

If I test this example (see link)from the ESRI documentation. it works correctly.

If I try a very similar query of my own the query never completes and if I cancel it leaves an external process on the server that takes more and more memory until the DBA has to kill it.

1) How can we verify st_geometry configuration works correctly?
2) Is my query failing because of invalid syntax?

This is the link to ESRI documentation with query that works:

http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_java_help/index.html#/ST_Overlaps/00920000132m000000/
This is my query:
SELECT hs.FULLNAME place, sa.name state
FROM COM_GIS.tl_2009_pointlm hs, COM_GIS.TL_2009_US_STATE sa
WHERE sa.stusps ='DC' and sde.st_overlaps(sde.st_buffer(hs.shape,.01), sa.shape) = 1;

Re: Need to test whether ST_GEOMETRY is working properly on database server.

AlvaroMoreno — Thu, 05 Aug 2010 17:26:58 GMT

as SDE on sqlplus:

SELECT sde.st_astext(shape) shape_text FROM <table_name>;

Re: Need to test whether ST_GEOMETRY is working properly on database server.

TomBrown — Thu, 05 Aug 2010 20:11:08 GMT

I have a sense everything is working correctly.

It's your query.

This is my query:
SELECT hs.FULLNAME place, sa.name state
FROM COM_GIS.tl_2009_pointlm hs, COM_GIS.TL_2009_US_STATE sa
WHERE sa.stusps ='DC' and sde.st_overlaps(sde.st_buffer(hs.shape,.01), sa.shape) = 1;

First you need to understand what your asking the operators and optimizer to do with your query...

You have two tables in your query tl_2009_pointlm (aliased hs) and tl_2009_us_state (aliased sa). You've set a predicate filter sa.stusps to return all tl_2009_us_state objects that equal DC (let's assume that's just one object).

That individual object is passed to the st_overlaps filter, sa.shape.

The st_overlaps operator can use the spatial index of the table in the first position, but you don't have a table in the first position. You have a collection of buffered hs.shape objects.

sde.st_overlaps(sde.st_buffer(hs.shape,.01), sa.shape)

You've forced the optimizer to first buffer (st_buffer) each object in the tl_2009_pointlm table (that could be thousands or millions of points - only you know the data). As each of those objects are buffered, they are next tested with the input sa.shape for st_overlaps.

Second, do you understand the definition of st_overlaps?

ST_Overlaps
ST_Overlaps compares two geometries of the same dimension and returns 1 or t (TRUE) if their intersection set results in a geometry different from both but of the same dimension.

ST_Overlaps returns 1 or t (TRUE) only for geometries of the same dimension and only when their intersection set results in a geometry of the same dimension. In other words, if the intersection of two ST_Polygons results in an ST_Polygon, overlap returns 1 or t (TRUE).

Therefore, when you buffer hs.shape does the output buffered object when overlapped with the sa.shape (DC) create a geometry that is different then both input geometries?

You may want to use st_intersects if your objective is to return all the hs.shapes that intersect sa.shape. That's only a suggestion not knowing what your true objective is...

Good luck.