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

[Webb Sprague]

Rethinking:

On Sun, May 18, 2008 at 12:10 PM, Webb Sprague <webb.sprague at gmail.com> wrote:
>> That is exactly what I want to do... but first I want to find a way to boil
>> down all trips to 12 or so distinct paths; without having to overlay every
>> trip.  But since I only have points, not streets; i have no way to say path
>> #1 is different from path #2.

1.  Find or make a table that describes the street system as polygons
for each block and intersection, with unique ids  (create through
buffering lines/ points or by converting a shapefile or creating
geometric data from float input). Be careful to get the topology right
(full coverage, non overlapping etc)

2.  Match each of your points to the street/ intersection polygon it
is "within()", store the ID.  This is how you go from floating
geometries to a schematic representation.

3. Using group by and sort, collect the street/ intersection ID's into
an ordered array array_accum for each path

4.  For each array, collapse the repeated street/ intersection IDs
into single IDs (make  S1, S1, S1, I2, I2, S3, S3, I4, I4, I4 into S1,
I2, S3, I4, with a fancy function or some regex trickery after
converting into a string).

5.  Give the new schematic / collapsed array to each path row (UPDATE).

6.  Find the average times, return the paths with the lowest.  You may
have to average times based on grouping with the street sequence to
get meaningful results.  Also different times/ events may yield
different average trip times.

If you start a wiki page with data, code, results, and questions
somewhere, I would happily collaborate (I think this is a fun problem)