In the informix world you would cast the polygon to a geometry and then into the line<br><br>ie:<br><br>select shape::st_geometry::st_multilinestring from bleargh<br><br>But have been able to cast the geometry type in postgis :(<br>
<br><br><br><div class="gmail_quote">On Mon, Apr 7, 2008 at 1:47 AM, Randall, Eric <<a href="mailto:ERandall@eriecountygov.org">ERandall@eriecountygov.org</a>> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div>
<br>
<p><font face="Courier New" size="2">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<br>
</i></font>
</p>
<br>
<p><font face="Arial" size="2">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.</font></p>
<p><font face="Arial" size="2">I neglected to account for the cases where a non-shared edge needs to end at the beginning. Adding a case statement</font>
<br><font face="Arial" size="2">seems to have handled that. I'd like to find a way to reference the derived table (p4) multiple times to avoid</font>
<br><font face="Arial" size="2">repeating the query (p5). The Common Table Expression WITH statement should do this I think but it doesn't seem to be </font></p>
<p><font face="Arial" size="2">supported in my version of PostgreSQL (8.2). Can anyone give me some ideas? Thanks.</font>
</p>
<p><font face="Arial" size="2">-Eric</font>
</p>
<br>
<br>
<p><font face="Arial" size="2">/* </font>
</p>
<p><font face="Arial" size="2"> example_turn_poly_into_lines.sql</font>
<br><font face="Arial" size="2"> </font>
<br><font face="Arial" size="2"> replace "polytable" with your poly table.</font>
<br><font face="Arial" size="2"> replace gid value (309) with your poly's gid.</font>
<br><font face="Arial" size="2"> </font>
<br><font face="Arial" size="2">*/</font>
</p>
<br>
<br>
<p><font face="Arial" size="2">select p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom </font>
<br><font face="Arial" size="2">from</font>
<br><font face="Arial" size="2">(</font>
</p>
<p><font face="Arial" size="2">/* Get edges shared with other polys */</font>
</p>
<p><font face="Arial" size="2">select t1.gid as poly_id,t2.gid as adjacent_poly_id, </font>
<br><font face="Arial" size="2">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 face="Arial" size="2">ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
</p>
<p><font face="Arial" size="2">union</font>
</p>
<p><font face="Arial" size="2">/* </font>
<br><font face="Arial" size="2"> Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. </font>
<br><font face="Arial" size="2"> Produces extra line(s) where there is more than one non-shared edge,</font>
<br><font face="Arial" size="2"> necessitating the self-join and ST_covers filter at the end.</font>
<br><font face="Arial" size="2"> Need help, more work on this, ...suggestions?</font>
<br><font face="Arial" size="2">*/</font>
</p>
<p><font face="Arial" size="2">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 face="Arial" size="2">from</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select t1.gid as poly_id,-1::integer as adjacent_poly_id,</font>
<br><font face="Arial" size="2">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 face="Arial" size="2">as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p1,</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select t1.gid as poly_id,-1::integer as adjacent_poly_id, </font>
<br><font face="Arial" size="2">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))))) </font></p>
<p><font face="Arial" size="2">when 0 then 1</font>
<br><font face="Arial" size="2">else ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))</font></p>
<p><font face="Arial" size="2">end</font>
<br><font face="Arial" size="2">as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p2,</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_linemerge(ST_boundary(geom)) as geom</font>
<br><font face="Arial" size="2">from polytable </font>
<br><font face="Arial" size="2">where gid = 309</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p3</font>
<br><font face="Arial" size="2">where p1.position < p2.position</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p4,</font>
</p>
<p><font face="Arial" size="2">/* repeat for needed self join */</font>
</p>
<p><font face="Arial" size="2">(</font>
</p>
<p><font face="Arial" size="2">/* Get edges shared with other polys */</font>
</p>
<p><font face="Arial" size="2">select t1.gid as poly_id,t2.gid as adjacent_poly_id, </font>
<br><font face="Arial" size="2">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 face="Arial" size="2">ST_linemerge(ST_intersection(ST_linemerge(ST_boundary(t1.geom)),ST_linemerge(ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
</p>
<p><font face="Arial" size="2">union</font>
</p>
<p><font face="Arial" size="2">/* </font>
<br><font face="Arial" size="2"> Create edges not shared with other polys. Uses -1 for pseudo external universe polygon. </font>
<br><font face="Arial" size="2"> Produces extra line(s) where there is more than one non-shared edge,</font>
<br><font face="Arial" size="2"> necessitating the self-join and ST_covers filter at the end.</font>
<br><font face="Arial" size="2"> Need help, more work on this, ...suggestions?</font>
<br><font face="Arial" size="2">*/</font>
</p>
<p><font face="Arial" size="2">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 face="Arial" size="2">from</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select t1.gid as poly_id,-1::integer as adjacent_poly_id,</font>
<br><font face="Arial" size="2">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 face="Arial" size="2">as position, ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">and ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p1,</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select t1.gid as poly_id,-1::integer as adjacent_poly_id, </font>
<br><font face="Arial" size="2">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))))) </font></p>
<p><font face="Arial" size="2">when 0 then 1</font>
<br><font face="Arial" size="2">else ST_line_locate_point(ST_linemerge(boundary(t1.geom)),ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))))</font></p>
<p><font face="Arial" size="2">end</font>
<br><font face="Arial" size="2">as position, ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">and ST_startpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) not in</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_endpoint(ST_linemerge(ST_intersection(ST_boundary(t1.geom),ST_boundary(t2.geom)))) as geom</font>
<br><font face="Arial" size="2">from polytable t1, polytable t2</font>
<br><font face="Arial" size="2">where t1.gid = 309</font>
<br><font face="Arial" size="2">and ST_touches(t1.geom, t2.geom)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p2,</font>
<br><font face="Arial" size="2">(</font>
<br><font face="Arial" size="2">select ST_linemerge(ST_boundary(geom)) as geom</font>
<br><font face="Arial" size="2">from polytable </font>
<br><font face="Arial" size="2">where gid = 309</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p3</font>
<br><font face="Arial" size="2">where p1.position < p2.position</font>
<br><font face="Arial" size="2">)</font>
<br><font face="Arial" size="2">as p5</font>
</p>
<p><font face="Arial" size="2">where ST_covers(p4.geom,p5.geom)</font>
<br><font face="Arial" size="2">group by p4.poly_id,p4.adjacent_poly_id,p4.pos_order,p4.geom</font>
<br><font face="Arial" size="2">having count(ST_covers(p4.geom,p5.geom)) = 1</font>
<br><font face="Arial" size="2">order by p4.pos_order</font>
</p>
<br>
<p><font face="Arial" size="2">---------</font>
</p>
<br><font color="#888888">
<p><font face="Arial" size="2">Eric Randall</font>
<br><font face="Arial" size="2">County of Erie</font>
<br><font face="Arial" size="2">140 W 6th St</font>
<br><font face="Arial" size="2">Room 119</font>
<br><font face="Arial" size="2">Erie, PA 16501</font>
</p>
<p><font face="Arial" size="2">ph. 814-451-6063</font>
<br><font face="Arial" size="2">fx. 814-451-7000</font>
</p>
</font></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br>