[postgis-users] Re: help building query

[Reid Priedhorsky]

Paul Ramsey wrote:
> 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.

OK -- Thanks, Paul. This seems to be moving in the right direction now.

My current working query is:

   select name, astext(LineMerge(Collect(geometry)))
   from byway_segment
   where name = 'Elliot Ave S' or name = '10th Ave S'
   group by name;

(The WHERE clause is to keep the result size small while I'm working 
things out.)

This seems to collect the separate linestrings together into 
multilinestrings, just as I need. How can I break them back out into 
linestrings? "select name, Dump(LineMerge(Collect(geometry))) ..." gives 
the error "set-valued function called in context that cannot accept a set".

Thanks again,

Reid

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