1

So I have database with which I want to do the following:

Find out which other rows are contained within the geometry of the row in question. To keep it simple, I am finding the envelope/bbox of the current row and then trying to figure out which other rows are within its bounding box.

So far I have the following working SQL query attempting to take advantage of the SpatialIndex:

SELECT bta.id, btb.id
from <table_name> as bta, <table_name> as btb
WHERE btb.ROWID IN (
SELECT btb.ROWID
FROM SpatialIndex
WHERE f_table_name = <table_name>
AND search_frame = ST_Envelope(bta.geom))

So the expected result would be something of the following form:

bta_id, btb_id
1, 1
1, 3
1, 5
2, 2
2, 6 ...

However, it is not working as expected. Instead of obtaining the result explained/shown above, I just obtain all the the ids again as follows:

bta_id, btb_id
1, 1
1, 2
1, 3
1, 4
1, 5
1, 6 ...

And when I check their respective bounding boxes they are not even close! I am limiting the query results but from what I have seen I would simply get the all the pairs from 1 to n.

How can I diagnose this problem?

2
  • Are you sure there is no any join relation between bta and btb? result of your query will be cross table of (Cartesian multiply) between bta and all btb rows which satify where clause.
    – Reza
    Commented Feb 2, 2022 at 5:02
  • there is another issue in query I can not understand , "SELECT btb.ROWID FROM SpatialIndex" inside where clause,are you sure this query returns right result?you reference btb.ROWID while the table name after from is SpatialIndex?maybe you should put[] around it to cover period function? SELECT [btb.ROWID] from SpatialIndex... refrences a field with btb.ROWID as name inside SpatialIndex table while Select btb.ROWID from SpatialIndex still references a field inside btb table.
    – Reza
    Commented Feb 2, 2022 at 5:20

1 Answer 1

0

If i understand the question correctly - you're just looking for ST_Intersects?

SELECT bta.id, btb.id
from <table_name> as bta, <table_name> as btb
where st_intersects(bta.geom,btb.geom)

This will give you all the rows intersecting every other row - for other spatial relationships see the PostGIS documentation

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.