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?

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, privacy policy and cookie policy

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