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: -
ERROR: value "01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F0000000000000040000000000000F03F00000000000000400000000000000040000000000000F03F0000000000000040000000000000F03F000000000000F03F" is out of range for type integer
-
CONTEXT: PL/pgSQL function linesfrompolygon2(geometry) line 8 at FOR over SELECT rows
-
********** Error **********
-
and returns nothing on a polygon feature.
Query 1: Returns A list of Polygons from a Multi or Single part geom -
-
SELECT id, name,
-
ST_GeometryN(the_geom, generate_series(1, ST_NumGeometries(the_geom)))
-
AS geom
-
INTO table_of_polys_only
-
from poly_and_multipoly;
-
Result: -
id|name|geom
-
1|A|POLYGON ((7.7 3.8,7.7 5.8,9 5.8,7.7 3.8))
-
2|B|POLYGON ((0 0,4 0,4 4,0 4,0 0), (1 1,2 1,2 2,1 2,1 1))
-
3|B|POLYGON ((-1 -1,-1 -2,-2 -2,-2 -1,-1 -1))
-
Query 2: Breaks the Polygons into Linestrings -
SELECT id, name, st_asewkt((st_dump(st_boundary(geom))).geom)
-
FROM table_of_polys_only;
-
Result: -
id|name|st_asewkt text
-
1|A|LINESTRING (7.7 3.8,7.7 5.8,9 5.8,7.7 3.8)
-
2|B|LINESTRING (0 0,4 0,4 4,0 4,0 0)
-
2|B|LINESTRING (1 1,2 1,2 2,1 2,1 1)
-
2|B|LINESTRING (-1 -1,-1 -2,-2 -2,-2 -1,-1 -1)
-
So far my code looks like this: -
CREATE OR REPLACE FUNCTION LinesFromPolygon2(polygon geometry)
-
RETURNS SETOF geometry_dump AS
-
$BODY$DECLARE
-
m integer; -- integer the stores # of polys in a mpoly
-
g geometry;
-
p geometry_dump%ROWTYPE;
-
BEGIN
-
IF GeometryType($1) LIKE 'MULTI%' THEN
-
-- break multipolygons into polygons
-
FOR m IN SELECT ST_GeometryN($1, generate_series(1, ST_NumGeometries($1))) LOOP
-
p.path[1] := m; -- use to store Multipolygon number
-
g := (ST_Dump(ST_Boundary(ST_GeometryN($1, m)))).geom;
-
RETURN NEXT p;
-
END LOOP;
-
ELSE -- It is not a MULTI- geometry
-
g := (ST_Dump(ST_Boundary($1))).geom;
-
END IF;
-
RETURN;
-
END;$BODY$
-
LANGUAGE plpgsql ;
-
Call: -
SELECT id, name, LinesFromPolygon2(the_geom)
-
FROM public.poly_and_multipoly;
-
Trial data (using PostGIS ext) -
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 multipolygon
-
INSERT 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
-
);
-
0 2107 Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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.
...
|
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:
...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |