select by location point in polygon with sql query

Question

I have 2 tables

table a this one a polygon layer with 10000 records

table b this one a point layer with 600000 records

in postgresql geodatabase i select a polygon feature with the query by input x and y

then i selected the one feature polygon i need to select point layer records that

intersect by the polygon feature but this query time 40 seconds and very slow please help me .

select shape from sde.table_b where st_contains((select shape from st_point(235116.084 ,
2225686.917,10100) as tt , table_a where st_contains (table_a.shape,tt)='t'),shape)='t'

point data is sample not really very

• Michael Stimson: So, the problem is that it's slow, this can be caused by many things but before we consider that can you do this selection manually in ArcGis or QGIS? Hint: make a point feature class with the point then select the polygon by location then select the points with the selected polygons... if it still takes 40 seconds the problem is likely to be a combination of hardware/network/database tuning. Is the data versioned? does it need to be compressed? will rebuilding the spatial index help? What else is the server doing?

Answer 1

This question is more than a little confusing ("point data is sample not really very" what?). Your SQL query syntax is highly irregular, and fails to use the correct function to take advantage of a spatial index on the point layer. What you probably want is:

SELECT b.shape
FROM table_b b, table_a a
WHERE sde.st_contains(a.shape,st_point(235116.084,2225686.917,10100))='t' AND
 sde.st_within(b.shape,a.shape)='t'

This could also be written with a subquery:

SELECT b.shape
FROM table_b b
WHERE sde.st_within(b.shape,
 (SELECT a.shape
 FROM table_a a
 WHERE sde.st_contains(a.shape,st_point(235116.084,2225686.917,10100))='t'
 )
 )='t' 

You have enough features that you could begin to see the impact of spatial fragmentation, so even if the point data has an appropriate index, you may get poor performance simply because the point features are randomly distributed across the point table, which will produce "full table scan"-like performance.

You should also be aware that ArcGIS best practice is to avoid use of the SDE user for creation of any user data. SDE should be reserved for geodatabase administration -- failure to do so puts you at risk of geodatabase corruption. I strongly urge you to create at least one new user to own spatial data, and at least one new user for applications to use to connect to the data (with the minimum necessary permissions, managed through roles, as is database best practice).