> > 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.... _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
