473,326 Members | 2,136 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Using LOOP with set returning functions

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
0 2107

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

Similar topics

5
by: Jim Cser | last post by:
Hello- I have a function to generate a multi-dimensional array, which then gets summed over one axis. The problem is that the dimensions are large, and I run out of memory when I create the...
6
by: gamehack | last post by:
Hello all, I've thought about having to implement 2 functions, say enter_main_loop(func_name); and a function called quit_application(); which terminates the program. The enter_main_loop() can...
4
by: 3rdshiftcoder | last post by:
hi- i am having trouble using parameter values in my function and to be honest a little trouble with member variables. i am trying to pass in the argument 'd' representing delete. what the...
12
by: Synapse | last post by:
Hello everyone!:) i need your help again..i got there a code of a while loop program and our prof let us convert it into functions. can anyone help me please. It must be converted into 3 types. ...
2
by: Beorne | last post by:
I have to call a c++ library funtion returning a string with the following signature: char *get_identifier(); Usually when I have to marshal a function with a char* output parameter I do: ...
3
by: nico3334 | last post by:
I'm filling in a Report with SQL data using VB code. I'm using LOOP and MoveNext. Before using MoveNext, I would like to be able to check whether the new data is equal to the previous data that was...
5
by: boss1 | last post by:
hi all, i have a problem with loop in select statement.i m using code : <select name = "s" size = "1" > <option selected>P-Code</option> <option...
9
by: nickstock | last post by:
Hi all im struggling with this and some help would be great. overview: ive written a workshop time and attendance program and all works great however my boss has found that when doing a wages print...
2
by: kako0000000 | last post by:
Hello I used textbox in my project i write a number and some thing writing i textbox using loop for example i used this code Private Sub Command1_Click() Dim i As Integer For i = 0 To 10...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.