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

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).