[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

  From MAILER-DAEMON  Fri Mar  2 12:01:44 2007
Return-Path: <>
X-Original-To: postgis-users at postgis.refractions.net
Delivered-To: postgis-users at netnation.refractions.net
Received: from mail.inet.dk (mail99.123hotel.dk [80.160.71.178])
	by netnation.refractions.net (Postfix) with ESMTP id CFAFD2C0936
	for <postgis-users at postgis.refractions.net>;
	Fri,  2 Mar 2007 12:01:23 -0800 (PST)
Received: from ([64.40.100.236])
	by mail.inet.dk (80.160.71.178=mail.inet.dk) with SMTP id HXD23831
	for <postgis-users at postgis.refractions.net>;
	Fri, 02 Mar 2007 21:01:31 +0100
Date: Fri, 02 Mar 2007 21:01:31 +0100
From: Martin Hoegh <mh at svaj.dk>
To: <postgis-users at postgis.refractions.net>
Message-Id: <912435247 at mail.inet.dk>
Content-Type: text/plain; charset="utf-8"
Subject: [postgis-users] Re: postgis-users Digest, Vol 53, Issue 2
X-BeenThere: postgis-users at postgis.refractions.net
X-Mailman-Version: 2.1.5
Precedence: list
Reply-To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
List-Id: PostGIS Users Discussion <postgis-users.postgis.refractions.net>
List-Unsubscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
	<mailto:postgis-users-request at postgis.refractions.net?subject=unsubscribe>
List-Archive: <http://lists.refractions.net/pipermail/postgis-users>
List-Post: <mailto:postgis-users at postgis.refractions.net>
List-Help: <mailto:postgis-users-request at postgis.refractions.net?subject=help>
List-Subscribe: <http://postgis.refractions.net/mailman/listinfo/postgis-users>,
	<mailto:postgis-users-request at postgis.refractions.net?subject=subscribe>
X-List-Received-Date: Fri, 02 Mar 2007 20:01:44 -0000

Jeg er på forældreorlov indtil d. 14. maj. Mine kollegaer kan traeffes paa tlf. 98129300.

I am on parental leave until May 14. If you have any matter of urgency please do not hesitate to call my collegues on +45 98129300