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




_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[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@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users