<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7651.59">
<TITLE>[postgis-users] Turn a Polygon into Lines</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Courier New">Lee Hachadoorian wrote:<BR>
><I> I'm looking through the PostGIS reference, and I can't seem to find a<BR>
</I>><I> way to take a geometry of polygons and turn it into lines. What I'm<BR>
</I>><I> looking for is something like the ArcGIS Feature to Line geoprocessor,<BR>
</I>><I> which will create a line shapefile where each feature is an arc<BR>
</I>><I> representing the boundary between neighboring polygons with a field<BR>
</I>><I> indicating the ids of the polygon on either side.<BR>
</I>><I><BR>
</I>><I> Functions like ST_MakeLine require point geometries, and I don't see<BR>
</I>><I> anything else that seems to be what I'm looking for. Any ideas would<BR>
</I>><I> be welcome.<BR>
</I>><I><BR>
</I>><I> Thanks,<BR>
</I>><I> Lee Hachadoorian<BR>
</I>><I> PhD Student in Geography<BR>
</I>><I> Program in Earth & Environmental Sciences<BR>
</I>><I> CUNY Graduate Center</I></FONT>
</P>
<BR>
<P><FONT SIZE=2 FACE="Arial">A custom function is the fastest and most efficient way to do this, as others have indicated, </FONT>
<BR><FONT SIZE=2 FACE="Arial">but it's often fun and instructive to try to implement a task in a single statement.</FONT>
<BR><FONT SIZE=2 FACE="Arial">Here's my attempt at the "</FONT><I></I><I><FONT SIZE=2 FACE="Courier New">ArcGIS Feature to Line geoprocessor</FONT><FONT SIZE=2 FACE="Courier New">"</FONT></I> <FONT SIZE=2 FACE="Courier New">mentioned</FONT><I><FONT SIZE=2 FACE="Courier New">.</FONT></I>
<BR><FONT SIZE=2 FACE="Arial">It needs to be faster and smarter, but seems to work. If anyone has suggestions for this or even a different</FONT>
<BR><FONT SIZE=2 FACE="Arial">approach, please chime in. I'm not always good at seeing other paths once I get a ways into the woods.</FONT>
<BR><FONT SIZE=2 FACE="Arial">Thanks.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">-Eric</FONT>
</P>
<BR>
<BR>
<BR>
<BR>
<P><FONT SIZE=2 FACE="Arial">/* </FONT>
<BR><FONT SIZE=2 FACE="Arial"> turn_poly_into_lines.sql</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial"> I used parcel polygons in my experiment.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial"> Replace "polytable" with your poly table. Replace gid value with your poly's gid.</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial"> */ </FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom </FONT>
<BR><FONT SIZE=2 FACE="Arial">from</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">/* Get edges shared with other polys */</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,t2.gid as adjacent_poly_id, </FONT>
<BR><FONT SIZE=2 FACE="Arial">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,</FONT></P>
<P><FONT SIZE=2 FACE="Arial">ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">union</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">/* </FONT>
<BR><FONT SIZE=2 FACE="Arial"> Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. </FONT>
<BR><FONT SIZE=2 FACE="Arial"> Produces extra line necessitating the self-join and the ST_covers filter at the end.</FONT>
<BR><FONT SIZE=2 FACE="Arial"> Need help, more work on this.</FONT>
<BR><FONT SIZE=2 FACE="Arial">*/</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order,ST_line_substring(p3.geom,p1.position,p2.position) as geom</FONT></P>
<P><FONT SIZE=2 FACE="Arial">from</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,-1::integer as adjacent_poly_id,</FONT>
<BR><FONT SIZE=2 FACE="Arial">ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))</FONT></P>
<P><FONT SIZE=2 FACE="Arial">as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p1,</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,-1::integer as adjacent_poly_id, </FONT>
<BR><FONT SIZE=2 FACE="Arial">ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))) </FONT></P>
<P><FONT SIZE=2 FACE="Arial">as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p2,</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_linemerge(ST_boundary(geom)) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable </FONT>
<BR><FONT SIZE=2 FACE="Arial">where gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p3</FONT>
<BR><FONT SIZE=2 FACE="Arial">where p1.position < p2.position</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p4,</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">/* Repeat for needed self-join */</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">(</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">/* Get edges shared with other polys */</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,t2.gid as adjacent_poly_id, </FONT>
<BR><FONT SIZE=2 FACE="Arial">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,</FONT></P>
<P><FONT SIZE=2 FACE="Arial">ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">union</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">/* </FONT>
<BR><FONT SIZE=2 FACE="Arial"> Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. </FONT>
<BR><FONT SIZE=2 FACE="Arial"> Produces extra line necessitating the self-join and the ST_covers filter at the end.</FONT>
<BR><FONT SIZE=2 FACE="Arial"> Need help, more work on this.</FONT>
<BR><FONT SIZE=2 FACE="Arial">*/</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">select p1.poly_id, p1.adjacent_poly_id, p1.position as pos_order, ST_line_substring(p3.geom,p1.position,p2.position) as geom</FONT></P>
<P><FONT SIZE=2 FACE="Arial">from</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,-1::integer as adjacent_poly_id,</FONT>
<BR><FONT SIZE=2 FACE="Arial">ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))</FONT></P>
<P><FONT SIZE=2 FACE="Arial">as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p1,</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select t1.gid as poly_id,-1::integer as adjacent_poly_id, </FONT>
<BR><FONT SIZE=2 FACE="Arial">ST_line_locate_point(ST_linemerge(ST_boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom))))) </FONT></P>
<P><FONT SIZE=2 FACE="Arial">as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable t1, polytable t2</FONT>
<BR><FONT SIZE=2 FACE="Arial">where t1.gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">and ST_touches(t1.geom, t2.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p2,</FONT>
<BR><FONT SIZE=2 FACE="Arial">(</FONT>
<BR><FONT SIZE=2 FACE="Arial">select ST_linemerge(ST_boundary(geom)) as geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">from polytable </FONT>
<BR><FONT SIZE=2 FACE="Arial">where gid = 309</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p3</FONT>
<BR><FONT SIZE=2 FACE="Arial">where p1.position < p2.position</FONT>
<BR><FONT SIZE=2 FACE="Arial">)</FONT>
<BR><FONT SIZE=2 FACE="Arial">as p5</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">where ST_covers(p4.geom,p5.geom)</FONT>
<BR><FONT SIZE=2 FACE="Arial">group by p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom</FONT>
<BR><FONT SIZE=2 FACE="Arial">having count(ST_covers(p4.geom,p5.geom)) = 1</FONT>
<BR><FONT SIZE=2 FACE="Arial">order by p4.pos_order</FONT>
</P>
<BR>
<BR>
<BR>
<P><FONT SIZE=2 FACE="Arial">---------</FONT>
<BR><FONT SIZE=2 FACE="Arial"> </FONT>
<BR><FONT SIZE=2 FACE="Arial">Eric Randall</FONT>
<BR><FONT SIZE=2 FACE="Arial">County of Erie</FONT>
<BR><FONT SIZE=2 FACE="Arial">140 W 6th St</FONT>
<BR><FONT SIZE=2 FACE="Arial">Room 119</FONT>
<BR><FONT SIZE=2 FACE="Arial">Erie, PA 16501</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">ph. 814-451-6063</FONT>
<BR><FONT SIZE=2 FACE="Arial">fx. 814-451-7000</FONT>
</P>
</BODY>
</HTML>