[postgis-users] help building query

[Reid Priedhorsky]

Hi folks,

My data looks like this (geometries omitted):

   id   |     name     | start_node_id | end_node_id | geometry
-------+--------------+---------------+-------------+----------
  79401 | 10th Ave S   |        324060 |      324053 |
  79402 | Elliot Ave S |        326463 |      324052 |
  79527 | Elliot Ave S |        326551 |      324072 |
  79529 | 10th Ave S   |        326553 |      324073 |
  79578 | Elliot Ave S |        323515 |      326551 |
  79579 | 10th Ave S   |        323516 |      326553 |
  79614 | Elliot Ave S |        326600 |      323515 |
  79778 | Elliot Ave S |        326700 |      326701 |
  79779 | 10th Ave S   |        326702 |      326703 |
  79881 | Elliot Ave S |        326766 |      326767 |
  79883 | 10th Ave S   |        326768 |      326769 |

i.e., each block of a road is one row.

I would like a query which would combine all parts of each street into 
the same row, building appropriate new geometries. For example:

      name     | geometry
--------------+----------
  10th Ave S   | (linestring combining touching blocks of 10th Ave S)
  Elliot Ave S | (linestring combining one set of touching blocks)
  Elliot Ave S | (linestring combining another set)

Complications that I'm aware of:

1. Roads which have the same name but don't touch shouldn't be combined.

2. The linestrings which form each block might not be aligned in the 
same direction, e.g. a road which runs east-west might have some blocks 
defined by linestrings starting in the east and going west while other 
blocks are vice versa.

I'm a little stumped. Any ideas?

Thanks!

Reid