[postgis-users] network generation question.
Jeff Hamann
jeff.hamann at forestinformatics.com
Fri Jun 12 13:59:51 PDT 2009
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
More information about the postgis-users
mailing list