[postgis-users] Calculate driving distance in postgis

[Stephen Woodbridge]

Hi,

If you already know what segments in the database the route will follow 
then the length of the route can be calculated like

select sum(length_spheroid(the_geom, 
'SPHEROID["GRS_1980",6378137,298.257222101]') from your_table where 
<select the segments in the route>

You can get fancier if you want to compute drive time along the route 
based on CFCC road class by multiplying an average speed associated to 
the CFCC of the segment and the length of the segment and summing up that.

-Steve W.

K Kuo wrote:
> Hi List,
> First of all, thanks Arnaud for the reply.
> I think pgdijkstra will find the shortest path between two points. 
> However, I intend to calculate the driving distance which is defined by 
> myself. Any suggestion? Thanks in advance.
> 
> Kurt
> 
> Hi List,
> Does anyone have a better way to calculate driving distance in postgis?
> I have a database which convert from tiger/line data. I have found that I
> can use distance_spheroid function to calculate distance between two 
> points,
> which supposes to be the shortest distance instead of driving distance, 
> am I
> correct? What I am thinking about is that I have to query out all nodes
> alone the route, calculate distances for each segment then add them up. I
> don't think this is a good approach. Any suggestion?
> By the way, the tiger/line data contain fnode, tnode number for each line
> segment. How can I get the long/lati value from postgis.
> 
> Thank you very much.
> 
> Regards,
> 
> Kurt
> 
> 
> 
> 
> ------------------------------
> 
> Message: 16
> Date: Thu, 02 Feb 2006 09:58:39 +0100
> From: Arnaud Lesauvage <thewild at freesurf.fr>
> Subject: Re: [postgis-users] Calculate driving distance in postgis
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <43E1C9BF.8050207 at freesurf.fr>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> 
> K Kuo a écrit :
> 
>> I have a database which convert from tiger/line data. I have found 
>> that I can use distance_spheroid function to calculate distance 
>> between two points, which supposes to be the shortest distance instead 
>> of driving distance, am I correct? What I am thinking about is that I 
>> have to query out all nodes alone the route, calculate distances for 
>> each segment then add them up. I don't think this is a good approach. 
>> Any suggestion?
> 
> 
> Hi !
> 
> I have successfully installed and used pgdijkstra, which is a
> PostgreSQL implementation of the Dijkstra algorithm (widely used
> for shortest path calculations).
> http://www.cartoweb.org/contribs.html
> It is very easy to install and fairly easy to use.
> This module includes special PostGIS functions that can return the
> shortest path between two nodes as a PostGIS geometry (a
> LINESTRING, I believe).
> You would just need to get the Length() of this geometry to have
> the exact driving distance between the nodes.
> 
> Regards
> -- 
> Arnaud
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>