[postgis-users] RE: Improving the timing of a function

Fri Dec 7 08:43:12 PST 2007

Hi again,

I managed to get this Function to compile and actually work. What I changed
were the values in the expand to pass to the functions give_source() and
give_target().

I have made a quick OpenLayers + Mapserver script so as to test the outcome
and unfortunately this function is giving me really awkward results!

The more the accident happens near to a hospital / firestation, the less
chance it will be chosen as the nearest hospital / firestation.

As one can test for himself, here :
http://yancho.no-ip.org/~yancho/rescue.html  the nearer (I even tried on the
same street) the lesser the chance to be chosen as the nearest hospital.

The new function can be found here : http://yancho.pastebin.com/d37f3b903

The PHP script I am using for routing can be found here :
http://yancho.pastebin.com/f6cff7908

Any help how I can fix this problem will be extremely appreciated!

Thanks and regards

Matthew 

From: Matthew Pulis [mailto:mpulis at gmail.com] 
Sent: Thursday, December 06, 2007 9:41 PM
To: 'Matthew Pulis'; 'PostGIS Users Discussion'
Subject: RE: Improving the timing of a function

Hi again,

Thanks to those who already tried to help me I have put some RAISE NOTICE
and here is the result :

This is the pastebin of the new code : http://yancho.pastebin.com/f2f986b8c
<- marked with yellow are the new RAISE NOTICEs

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.

I hope this can help better J

Thanks

From: Matthew Pulis [mailto:mpulis at gmail.com] 
Sent: Thursday, December 06, 2007 8:31 PM
To: 'PostGIS Users Discussion'
Subject: Improving the timing of a function

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

At the end of the paste you can find the EXPLAIN ANALYZE of the query.

Paste can be found here : http://yancho.pastebin.com/f3cca3a41

May I point out that I tried the first SELECT query without LIMIT 3 and it
gave me 52 points in 32ms and with LIMIT 3 it gave me the 3 points in 31 ms.

Any help would be extremely appreciated J

Thanks and regards

Matthew Pulis 

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 18467 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len>  for free now!

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 18467 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len>  for free now!

  _____  

I am using the free version of SPAMfighter for private users.
It has removed 18467 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter <http://www.spamfighter.com/len>  for free now!

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20071207/e2bc23b4/attachment.html>