By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Using LOOP with set returning functions

P: 1
I am struggling to write a pl/sql function that encompasses the two below queries. Right now, the function executes, but when called it isn't returning anything....

It looks like I *might* be running into some trouble with passing $1 in a set returning function (i.e ST_Dump or generate_series...). It fails like this on a Multipolygon feature:

Expand|Select|Wrap|Line Numbers
  1. ERROR:  value "01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040000000000000F03F0000000000000040000000000000F03F000000000000F03F" is out of range for type integer
  2. CONTEXT:  PL/pgSQL function linesfrompolygon2(geometry) line 8 at FOR over SELECT rows
  3. ********** Error **********
  4.  
and returns nothing on a polygon feature.



Query 1: Returns A list of Polygons from a Multi or Single part geom

Expand|Select|Wrap|Line Numbers
  1.  
  2.     SELECT id, name, 
  3.     ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))
  4. AS geom
  5. INTO table_of_polys_only
  6. from poly_and_multipoly;
  7.  
Result:

Expand|Select|Wrap|Line Numbers
  1. id|name|geom
  2. 1|A|POLYGON ((7.7 3.8,7.7 5.8,9 5.8,7.7 3.8))
  3. 2|B|POLYGON ((0 0,4 0,4 4,0 4,0 0), (1 1,2 1,2 2,1 2,1 1))
  4. 3|B|POLYGON ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))
  5.  
Query 2: Breaks the Polygons into Linestrings

Expand|Select|Wrap|Line Numbers
  1. SELECT id, name, st_asewkt((st_dump(st_boundary(geom))).geom)
  2. FROM table_of_polys_only;
  3.  
Result:
Expand|Select|Wrap|Line Numbers
  1. id|name|st_asewkt text
  2. 1|A|LINESTRING (7.7 3.8,7.7 5.8,9 5.8,7.7 3.8)
  3. 2|B|LINESTRING (0 0,4 0,4 4,0 4,0 0)
  4. 2|B|LINESTRING (1 1,2 1,2 2,1 2,1 1)
  5. 2|B|LINESTRING (-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)
  6.  
So far my code looks like this:

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION LinesFromPolygon2(polygon geometry) 
  2. RETURNS SETOF geometry_dump AS 
  3. $BODY$DECLARE 
  4.  m integer; -- integer the stores # of polys in a mpoly
  5.  g geometry;
  6.  p geometry_dump%ROWTYPE; 
  7. BEGIN
  8. IF GeometryType($1) LIKE 'MULTI%' THEN 
  9. -- break multipolygons into polygons
  10.     FOR m IN SELECT ST_GeometryN($1, generate_series(1, ST_NumGeometries($1))) LOOP 
  11.       p.path[1] := m; -- use to store Multipolygon number 
  12.       g := (ST_Dump(ST_Boundary(ST_GeometryN($1, m)))).geom;
  13.      RETURN NEXT p;
  14.      END LOOP;
  15. ELSE -- It is not a MULTI- geometry 
  16.     g := (ST_Dump(ST_Boundary($1))).geom; 
  17.   END IF; 
  18.   RETURN; 
  19. END;$BODY$
  20. LANGUAGE plpgsql ;
  21.  
Call:

Expand|Select|Wrap|Line Numbers
  1. SELECT id, name, LinesFromPolygon2(the_geom)
  2.   FROM public.poly_and_multipoly;
  3.  
Trial data (using PostGIS ext)

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE poly_and_multipoly (
  2.   "id" SERIAL NOT NULL PRIMARY KEY,
  3.   "name" char(1) NOT NULL,
  4.   "the_geom" geometry NOT NULL
  5. );
  6. -- add data, A is a polygon, B is a multipolygon
  7. INSERT INTO poly_and_multipoly (name, the_geom) VALUES (
  8.     'A', 'POLYGON((7.7 3.8,7.7 5.8,9.0 5.8,7.7 3.8))'::geometry
  9.     ), (
  10.     'B',
  11.     '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
  12. );
  13.  
Jun 15 '15 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.