[postgis-users] Finding/Categorizing Paths from A to B

[Webb Sprague]

>
> I have a table (called rawdata) with columns of :
> Path#,Point#(int),Latitiude(double),Longitude(double),Speed(double),Timestamp,Point
> (basically lat/lon in a single point object)
>
> I have a second table called paths:
> Path#(int),Line (line object)
>
> So, I really want to find a way to look at the paths table and determine
> that paths 1,2,3 I took 1st street. Path 4,5 I took 5th street. Path 6
> started going on 1st street then switch over to 5 street.
>
> So if I looked at it graphically on a map, I could easily determine which
> paths where which.

Overlaying on a map should be easy: QGIS, etc.  Do you have a table of
streets, if you need to see which street you went down (you need one)?

>>
>>> What I don't have is any indication of what paths I took, so I
>>> have no idea how to average several trips / path.

You have lines that are your aggregated points that are the paths you
took, you have times on each point, plus you have an attribute
(path#) that allows you to group points into paths.  If speed is what
you need to average (It is hard to understand what means "how to
average several trips/ path" -- average WHAT over WHAT?), then you can
find the speed for each path by taking the (time at begin - time at
start) / distance for each path.

YOu might be able to GROUP BY path# on rawdata, then use
MAX(timestamp) - MIN(timestamp) to get time elapsed.  I would update
the "paths" table with this.  Then LENGTH(path) (check docs for
correct func).  Then update paths set speed = length/ time. Voila --
sort by speed, assuming that the begin and end in paths is always the
same.  This doesn't give you which street at all, but that would be
the next query.

I am afraid you haven't specified your problem very clearly so it is a
little hard to help more.

Also it is easier to deal with code and output without your filtering.
 For the tables, could you run "\d mytable"  and cut and paste the
output?  Maybe also a select blah limit 10 from the tables, using
astext to get the geometries?  Don't worry about email length.  Google
likes to mine it for text anyway....