[postgis-users] Re: help building query

[Reid Priedhorsky]

Hi folks,

Looks like my question was trickier than I thought. :)

I'd love to hear any suggestions you guys have, even partial solutions 
or "you can't do that" or "you need to relax requirement X".

My intuition is that I should GROUP BY on the street name and then use 
some sort of geometry coalescing -- LineMerge() perhaps -- but I'm 
concerned by the fact that the street segments aren't all adjacent and 
aren't ordered in the same direction.

Thanks again,

Reid


Reid Priedhorsky wrote:
> 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