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
How can I diagnose this problem?