[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: [postgis-users] Function to convert Polygons and MultiPolygons to LineStrings - struggles with path
From: Ruth Simm <goldfish_and_muskie () yahoo ! com>
Date: 2015-06-14 19:35:14
Message-ID: 1204445998.3182979.1434310514734.JavaMail.yahoo () mail ! yahoo ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
I am trying to script a function to return just the Linestring/MLinestring geom.
My function will be able to iterate through the geom of polygons and mpolys and \
return the geom output (only) of st_dump(st_boundary(the_geom)). Problem is I am \
struggling with iterating through the 'path' array....and I don't seem to have a good \
sandbox way of capturing my errors (is there anything -- similar to SQLFIDDLE -- that \
works with the postgis ext?). EX Data:
CREATE TABLE poly_and_multipoly ( "id" SERIAL NOT NULL PRIMARY KEY, "name" \
char(1) NOT NULL, "the_geom" geometry NOT NULL);-- add data, A is a polygon, B is a \
multipolygonINSERT INTO poly_and_multipoly (name, the_geom) VALUES ( 'A', \
'POLYGON((7.7 3.8,7.7 5.8,9.0 5.8,7.7 3.8))'::geometry ), ( 'B', \
'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), ((-1 -1,-1 -2,-2 -2,-2 \
-1,-1 -1)))'::geometry);
Function:
CREATE OR REPLACE FUNCTION LinesFromPolygon2(polygon geometry) RETURNS SETOF \
geometry_dump AS $BODY$DECLARE m integer; g geometry; p geometry_dump%ROWTYPE; \
BEGINIF GeometryType($1) LIKE 'MULTI%' THEN FOR m IN SELECT generate_series(1, \
ST_NumGeometries($1)) LOOP p.path[1] := m; -- use to store Multipolygon \
number g := ST_Dump(ST_Boundary($1, m)); RETURN NEXT p; END \
LOOP;ELSE -- It is not a MULTI- geometry g := ST_Dump(ST_Boundary($1)); \
END IF; RETURN; END;$BODY$LANGUAGE plpgsql ; ERROR:
SELECT id, name, LinesFromPolygon2(the_geom) FROM poly_and_multipoly;
ERROR: parse error - invalid geometryHINT: "({" <-- parse error at position 2 \
within geometryCONTEXT: PL/pgSQL function linesfrompolygon2(geometry) line 13 at \
assignment
********** Error **********
ERROR: parse error - invalid geometrySQL state: XX000Hint: "({" <-- parse error at \
position 2 within geometryContext: PL/pgSQL function linesfrompolygon2(geometry) line \
13 at assignment
[Attachment #5 (text/html)]
<html><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, \
Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:16px"><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr">I am trying to script a function to \
return just the Linestring/MLinestring geom. </div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr">My function will be able to iterate \
through the geom of polygons and mpolys and return the geom output (only) of \
st_dump(st_boundary(the_geom)).</div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr"><br></div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr">Problem is I \
am struggling with iterating through the 'path' array....and I don't seem to have a \
good sandbox way of capturing my errors (is there anything -- similar to SQLFIDDLE -- \
that works with the postgis ext?).</div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr"><br></div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr">EX \
Data:</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">CREATE TABLE \
poly_and_multipoly (</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> "id" SERIAL NOT NULL PRIMARY KEY,</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> "name" \
char(1) NOT NULL,</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style=""> "the_geom" geometry NOT NULL</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">);</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">-- add data, A is a \
polygon, B is a multipolygon</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style="">INSERT INTO poly_and_multipoly (name, the_geom) VALUES (</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> 'A', \
'POLYGON((7.7 3.8,7.7 5.8,9.0 5.8,7.7 3.8))'::geometry</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> ), \
(</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> \
'B',</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style=""> 'MULTIPOLYGON(((0 0,4 0,4 4,0 4,0 0),(1 1,2 1,2 2,1 2,1 1)), \
((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)))'::geometry</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">);</div><div \
dir="ltr" class="" style="" id="yui_3_16_0_1_1434310020505_2627"><br class="" \
style=""></div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr">Function:</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">CREATE OR REPLACE \
FUNCTION LinesFromPolygon2(polygon geometry) </div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">RETURNS SETOF \
geometry_dump AS </div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style="">$BODY$DECLARE </div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr" class="" style=""> m integer;</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> g \
geometry;</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style=""> p geometry_dump%ROWTYPE; </div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">BEGIN</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">IF GeometryType($1) \
LIKE 'MULTI%' THEN </div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> FOR m IN SELECT generate_series(1, \
ST_NumGeometries($1)) LOOP </div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr" class="" style=""> p.path[1] := m; -- use to store \
Multipolygon number </div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> g := ST_Dump(ST_Boundary($1, m));</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> \
RETURN NEXT p;</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> END LOOP;</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">ELSE -- It is not a \
MULTI- geometry </div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> g := ST_Dump(ST_Boundary($1)); </div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""> END \
IF; </div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style=""> RETURN; </div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr" class="" style="">END;$BODY$</div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr" class="" style="">LANGUAGE plpgsql ;</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr">ERROR:</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">SELECT id, name, \
LinesFromPolygon2(the_geom)</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style=""> FROM poly_and_multipoly;</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr"><br></div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">ERROR: parse \
error - invalid geometry</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" \
class="" style="">HINT: "({" <-- parse error at position 2 within \
geometry</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style="">CONTEXT: PL/pgSQL function linesfrompolygon2(geometry) line 13 at \
assignment</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style=""><br class="" style=""></div><div id="yui_3_16_0_1_1434310020505_2339" \
dir="ltr" class="" style="">********** Error **********</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style=""><br class="" \
style=""></div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style="">ERROR: parse error - invalid geometry</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">SQL state: \
XX000</div><div id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" \
style="">Hint: "({" <-- parse error at position 2 within geometry</div><div \
id="yui_3_16_0_1_1434310020505_2339" dir="ltr" class="" style="">Context: PL/pgSQL \
function linesfrompolygon2(geometry) line 13 at assignment</div><div \
id="yui_3_16_0_1_1434310020505_2339" class="" \
style=""> </div></div></body></html>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic