[postgis-users] Turn a Polygon into Lines

[Drew Sheedy]

In the informix world you would cast the polygon to a geometry and then into
the line

ie:

select shape::st_geometry::st_multilinestring from bleargh

But have been able to cast the geometry type in postgis :(



On Mon, Apr 7, 2008 at 1:47 AM, Randall, Eric <ERandall at eriecountygov.org>
wrote:

>
> Lee Hachadoorian wrote:
> >* I'm looking through the PostGIS reference, and I can't seem to find a
> *>* way to take a geometry of polygons and turn it into lines.  What I'm
> *>* looking for is something like the ArcGIS Feature to Line geoprocessor,
> *>* which will create a line shapefile where each feature is an arc
> *>* representing the boundary between neighboring polygons with a field
> *>* indicating the ids of the polygon on either side.
> *>*
> *>* Functions like ST_MakeLine require point geometries, and I don't see
> *>* anything else that seems to be what I'm looking for.  Any ideas would
> *>* be welcome.
> *>*
> *>* Thanks,
> *>* Lee Hachadoorian
> *>* PhD Student in Geography
> *>* Program in Earth & Environmental Sciences
> *>* CUNY Graduate Center
> *
>
> This is a correction to my March 31 post.   I should have have tested a
> little more and not been in such a hurry I guess.
>
> I neglected to account for the cases where a non-shared edge needs to end
> at the beginning.  Adding a case statement
> seems to have handled that.   I'd like to find a way to reference the
> derived table (p4) multiple times to avoid
> repeating the query  (p5).  The Common Table Expression WITH statement
> should do this I think but it doesn't seem to be
>
> supported in my version of PostgreSQL (8.2).  Can anyone give me some
> ideas?  Thanks.
>
> -Eric
>
>
> /*
>
>     example_turn_poly_into_lines.sql
>
>    replace "polytable" with your poly table.
>    replace gid value (309) with your poly's gid.
>
> */
>
>
> select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom
> from
> (
>
> /* Get edges shared with other polys */
>
> select t1.gid as poly_id,t2.gid as adjacent_poly_id,
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
> as pos_order,
>
> ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
>
> union
>
> /*
>    Create edges not shared with other polys.  Uses -1 for pseudo external
> universe polygon.
>    Produces extra line(s) where there is more than one non-shared edge,
>    necessitating the self-join and ST_covers filter at the end.
>    Need help, more work on this, ...suggestions?
> */
>
> select p1.poly_id, p1.adjacent_poly_id, p1.position as
> pos_order,ST_line_substring(p3.geom,p1.position,p2.position)  as geom
>
> from
> (
> select t1.gid as poly_id,-1::integer as adjacent_poly_id,
>
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
> as position,
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> and
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> not in
> (
> select
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> )
> )
> as p1,
> (
> select t1.gid as poly_id,-1::integer as adjacent_poly_id,
> case
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
>
> when 0 then 1
> else
> ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
> end
> as position,
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> and
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> not in
> (
> select
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> )
> )
> as p2,
> (
> select ST_linemerge(ST_boundary(geom)) as geom
> from polytable
> where gid = 309
> )
> as p3
> where p1.position < p2.position
> )
> as p4,
>
> /* repeat for needed self join */
>
> (
>
> /* Get edges shared with other polys */
>
> select t1.gid as poly_id,t2.gid as adjacent_poly_id,
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
> as pos_order,
>
> ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
>
> union
>
> /*
>    Create edges not shared with other polys.  Uses -1 for pseudo external
> universe polygon.
>    Produces extra line(s) where there is more than one non-shared edge,
>    necessitating the self-join and ST_covers filter at the end.
>    Need help, more work on this, ...suggestions?
> */
>
> select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order,
> ST_line_substring(p3.geom,p1.position,p2.position)  as geom
>
> from
> (
> select t1.gid as poly_id,-1::integer as adjacent_poly_id,
>
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
> as position,
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> and
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> not in
> (
> select
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> )
> )
> as p1,
> (
> select t1.gid as poly_id,-1::integer as adjacent_poly_id,
> case
> ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
>
> when 0 then 1
> else
> ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))
>
> end
> as position,
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> and
> ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> not in
> (
> select
> ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))
> as geom
> from polytable t1, polytable t2
> where t1.gid = 309
> and ST_touches(t1.geom, t2.geom)
> )
> )
> as p2,
> (
> select ST_linemerge(ST_boundary(geom)) as geom
> from polytable
> where gid = 309
> )
> as p3
> where p1.position < p2.position
> )
> as p5
>
> where ST_covers(p4.geom,p5.geom)
> group by p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom
> having count(ST_covers(p4.geom,p5.geom)) = 1
> order by p4.pos_order
>
> ---------
>
> Eric Randall
> County of Erie
> 140 W 6th St
> Room 119
> Erie, PA 16501
>
> ph. 814-451-6063
> fx. 814-451-7000
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080407/b4d0ec66/attachment.html>