Hi, I added this function to find the nearest hospital using the distance covered on the route itself. My reasoning was this : - Find the 3 most near hospitals using distance() function - Iterate the 3 hospitals and find the one which is the shortest distance taking into considerations one-ways et al. using shootingstar_sp() pgRouting Function. Using Pastebin I have pasted my current function and also inline commented more on the function The paste bin can be found here : http://yancho.pastebin.com/f2f986b8c At the end of the paste you can find the EXPLAIN ANALYZE of the query. As you can see I have highlighted in yellow some RAISE NOTICEs to time the query and these are the results : Below please find the result : - filling up the hospital_location with that query took : 32646ms - the IF section took 994 ms - returning back to the FOR loop took : 104 ms - Shooting Star execution took : 25079ms - If statement took : 614ms - Shooting star took : 37927 ms - If took : 300 ms Total query runtime: 114250 ms. 2 rows retrieved. Execution plan : NOTICE: Entering the FOR IN Thu Dec 06 21:02:46.267488 2007 CET NOTICE: entering shooting star Thu Dec 06 21:02:46.300164 2007 CET NOTICE: before IF Thu Dec 06 21:03:24.307906 2007 CET NOTICE: value of nearest.dist is 585.966565014107 NOTICE: value of hospital.gid is 12712 NOTICE: value of nearest.gid is <NULL> NOTICE: value of 2ND nearest.gid is POINT(640607.6248615 224673.124400562) NOTICE: after if Thu Dec 06 21:03:24.308900 2007 CET NOTICE: before end of loop Thu Dec 06 21:03:24.309029 2007 CET NOTICE: entering shooting star Thu Dec 06 21:03:24.309133 2007 CET NOTICE: before IF Thu Dec 06 21:04:02.024339 2007 CET NOTICE: value of nearest.dist is 91.0322939509899 NOTICE: value of hospital.gid is 38600 NOTICE: value of nearest.gid is POINT(640607.6248615 224673.124400562) NOTICE: value of 2ND nearest.gid is POINT(640952.4998615 224309.563400563) NOTICE: after if Thu Dec 06 21:04:02.024953 2007 CET NOTICE: before end of loop Thu Dec 06 21:04:02.025093 2007 CET NOTICE: entering shooting star Thu Dec 06 21:04:02.025178 2007 CET NOTICE: before IF Thu Dec 06 21:04:39.952201 2007 CET NOTICE: after if Thu Dec 06 21:04:39.952510 2007 CET NOTICE: before end of loop Thu Dec 06 21:04:39.952598 2007 CET NOTICE: after end of loop before returning Thu Dec 06 21:04:39.952846 2007 CET Total query runtime: 114250 ms. 2 rows retrieved. Hope this information can help you give me a better idea on how I can improve this query. Thanks and regards Matthew ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
