Timeline for PostGIS distance query using a dynamic radius
Current License: CC BY-SA 4.0
7 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Dec 19, 2023 at 21:06 | comment | added | Regina Obe | I understand what you are saying now. In this case if you have say a large points_to_check table relative to your pois table, the planner will hold the pois table constant and not use the index anyway regardless if you have one. Usually the case is that your pois with distance row set is small relative to the population you are comparing against so you want the index on the points_to_check to be used, but if your pois is much larger than the table of points you are checking, then @geozelot's solution is the best answer | |
| Dec 19, 2023 at 13:08 | comment | added | JGH |
To alleviate any doubt, would you mind adding the explain plan of the query with dynamic distances and that still use the spatial index?
|
|
| Dec 18, 2023 at 22:48 | comment | added | Regina Obe | Also your assumption that PostGIS can't use an index on your point cause your radius is not constant is mistaken. If you are seeing your index not being used, it's probably for another reason. I'm on the PostGIS Dev team BTW and wrote a book on this manning.com/obe3 so I know how ST_DWIthin works and how it leverages indexes. | |
| Dec 18, 2023 at 22:39 | history | edited | Regina Obe | CC BY-SA 4.0 |
added 2 characters in body
|
| Dec 18, 2023 at 22:33 | comment | added | Regina Obe | Your radiuses are kept in the table, correct? I'm assuming, you are either checking one point at a time or you have a table of points (points_to_check) you want to check against your table of point dynamic radius and determine which ones satisfy the within condition | |
| Dec 18, 2023 at 14:05 | comment | added | Daniel Orner | um... yes, it's geography, but I'm not sure how this answers the question? | |
| Dec 17, 2023 at 12:29 | history | answered | Regina Obe | CC BY-SA 4.0 |