[postgis-users] network generation question.

[Jeff Hamann]

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