[postgis-users] Combining street segments

[Randy George]

Hi Shane,

	I was just playing with this type of query this morning:
SELECT assvg(geomunion(the_geom),1,5), name FROM "streets" WHERE the_geom &&
GeometryFromText('LINESTRING(-111.8 33.3,-111.75 33.35)',4269) GROUP BY name

Here is the same dissolve query with a clip added:
SELECT assvg(geomunion(intersection(the_geom, GeomFromText('POLYGON((-111.8
33.3,-111.8 33.35,-111.75 33.35,-111.75 33.3,-111.8 33.3))',4269))),1,5),
name 
FROM "streets" 
WHERE the_geom && GeometryFromText('LINESTRING(-111.80 33.30, -111.75
33.35)',4269)
GROUP BY name

Geomunion() added a bit to the query time in my particular (slow sever) it
was about 8000ms more for the geomunion to dissolve about 2400 streets to
430 streets

Perhaps someone has a more efficient approach.

rkgeorge

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Shane
Sent: Saturday, May 06, 2006 11:54 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Combining street segments

Hello list,

I am rather new to GIS in general so please excuse if the
answer is obvious.  I've downloaded street data for
Canadian roads in Shapefile format and imported with
shp2pgsql.  It has road segments in a multilinestring but
they seem to be seperate for each part.  Here's an example.

Imperial|ST|MULTILINESTRING((-122.967562365924
49.2221848465251,-122.96616153254
2 49.2221934080796))
Imperial|ST|MULTILINESTRING((-122.966161532542
49.2221934080796,-122.96471956746
 49.2222020149332))
Imperial|ST|MULTILINESTRING((-122.96471956746
49.2222020149332,-122.962536008687
 49.2222192739398))

and so on.  There are 94 entries for Imperial ST.  Since
this is supposed to be one street, is there a query that
could assemble this into one geometry provided all the
lines are actually touching as there could be more than one
street of the same name in the country?

Tia,
Shane

-- 
http://www.cm.nu/~shane/
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users