[postgis-users] network generation question.

[Kevin Neufeld]

Hi Jeff,

I just posted a page to the PostGIS wiki that might help you.  Let me 
know if I can clarify anything for you.

http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesNetworkTopology

Cheers,
Kevin

Jeff Hamann wrote:
> PostGIS Community:
>
> I've been chewing on this for the last day or so, and could use some 
> guidance on the following situation because I think I'm doing some 
> poorly.
>
> 1) I have a set of linestrings that represent road segments.
> 2) I need to generate a table that contains a 
> from_node,to_node,linestring where the from_node/to_nodes are the 
> points of intersection.
> 3) I'm a newbie with st_intersection and my query so far is:
>
> -- break the existing multilinestringm's into something managable.
>
> drop table if exists temproads;
> create table temproads as select *, st_force_2d( 
> ST_GeometryN(centerline,1) ) as cline FROM road_lines where 
> ST_NumGeometries(centerline) = 1;
> insert into temproads select *, st_force_2d( 
> ST_GeometryN(centerline,2) ) as cline FROM road_lines where 
> ST_NumGeometries(centerline) = 2;
>
>
> create table
>      roads2 as
> select
>   r1.trid as from_id,
>   r2.trid as to_id,
>   Relate( r1.cline, r2.cline ) as relate,
>   ST_Intersection( r1.cline, r2.cline ) as st_intersection
> from
>   temproads r1,
>   temproads r2
> where
>  intersects( r1.cline, r2.cline )
> order by
>     r1.trid,
>        r2.trid;
>
> The results look nice on the screen, but then I need to break up the 
> linestrings into segments between where the two linestrings touch. 
> When I dig a little deeper, it looks like all of the original parts 
> are there, and I can write the additional code to extract out the new 
> line segments, end nodes (from/to), and build a new table from 
> scratch, but I'm not sure I'm doing this smartly.
>
> So my question is this:
>
> 1) Given a bunch of linestrings (multilinestrings), is it possible to, 
> in one or two postgis queries, to generate a network graph with edges 
> (as linestrings) and verticies (as points) where each linestring has 
> the from vertex and the to vertex from the newly created points, in 
> addition to the corresponding linestring between the two?
>
> Thanks for PostGIS!
>
> Jeff Hamann, PhD
> PO Box 1421
> Corvallis, Oregon 97339-1421
> 541-754-2457
> jeff.hamann[at]forestinformatics[dot]com
> http://www.forestinformatics.com
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users