Timeline for Best way to assign points to nearest roads in postgis?
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Oct 8, 2013 at 19:42 | comment | added | djq |
maybe ORDER BY rank and LIMIT 1 might help (not sure - have not tried)
|
|
| Oct 8, 2013 at 19:08 | comment | added | Matt Moehr |
Yes on spatial indexes. Do you mean: select * from <your query> where ranked_distance = 1; ??? Cause that doesn't help performance at all. I think the loop still has to go over all roads for each point.
|
|
| Oct 8, 2013 at 18:44 | comment | added | djq | I assume you have spatial indexes on your geometry columns; did you try adding a subquery to just get the row with rank 1? | |
| Oct 8, 2013 at 18:10 | comment | added | Matt Moehr |
This "works" but the join is still too slow to really implement this as a solution in my processing flow. Relevant lines from explain are in my above comment. I guess I'm going to have to write a function that does incremental table inserts...
|
|
| Oct 8, 2013 at 18:08 | comment | added | Matt Moehr |
-> Nested Loop (cost=0.00..1343261576.55 rows=1 width=706) Join Filter: ((st_closestpoint(l.geom, dade_random_points.wkb_geometry) && st_expand(dade_random_points.wkb_geometry, 200::double precision)) AND (dade_random_points.wkb_geometry && st_expand(st_closestpoint(l.geom, dade_random_points.wkb_geometry), 200::double precision)) AND _st_dwithin(st_closestpoint(l.geom, dade_random_points.wkb_geometry), dade_random_points.wkb_geometry, 200::double precision) AND st_intersects(st_buffer(st_closestpoint(l.geom, dade_random_points.wkb_geometry), 1e-05::double precision), l.geom))
|
|
| Oct 7, 2013 at 2:40 | history | edited | djq | CC BY-SA 3.0 |
added 259 characters in body
|
| Oct 7, 2013 at 2:16 | history | edited | djq | CC BY-SA 3.0 |
added 269 characters in body
|
| Oct 7, 2013 at 1:56 | history | answered | djq | CC BY-SA 3.0 |