[postgis-users] Re: help building query

[Paul Ramsey]

Your intuition is correct. If you do LineMerge you should end up with 
complete lines, and multilines hopefully in places where the lines don't 
join. Then break the multilines back out into individual lines and you 
should be done.

P

Reid Priedhorsky wrote:

> 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.
> 

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


-- 

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey at refractions.net
   Phone: 250-383-3022
   Cell: 250-885-0632