0

I have a query that return result as expected - list of points. I would like to create a function with two parameters:

  1. input polygon geometry
  2. N as number of points to create

    WITH
    polygon AS 
        (select geometry from property_gis where id=1247675),
    intervals AS 
        (SELECT generate_series (0, 9) as steps)
    SELECT ST_LineInterpolatePoint(ST_ExteriorRing(((ST_Dump(polygon.geometry)).geom)), steps/(select count(steps)::float-1 from intervals)) as polygon_points
    FROM
        polygon,intervals;
    

I've created next function:

CREATE OR REPLACE FUNCTION get_points(polygon geometry)
RETURNS geometry[] AS
$$
DECLARE
    geom_out geometry[];
BEGIN
    WITH
        intervals AS (SELECT generate_series (0, 9) as steps)
        SELECT
            ST_LineInterpolatePoint(ST_ExteriorRing(((ST_Dump(polygon)).geom)),steps/(select 
count(steps)::float-1 from intervals))
        INTO
            geom_out
        FROM
            intervals;

   RETURN geom_out;
END;
$$
LANGUAGE plpgsql ;

but when I try to call it, like this:

with
    poly AS (select geometry from property_gis where id=1247675)
select  get_points(poly.geometry)
from poly;

I get the following error:

[22P02] ERROR: malformed array literal: "01010000000A17E1637B1054C0815E5CA915553A40" Detail: Array value must start with "{" or dimension information. Where: PL/pgSQL function get_points(geometry) line 5 at SQL statement

  • 1
    I guess that you should return a SETOF geometry not a geometry, check the doc about SETOF – obchardon May 5 at 13:42
1

I've made changes to have setof geometry

CREATE OR REPLACE FUNCTION get_points(polygon geometry)
RETURNS setof geometry AS
$$
DECLARE
geom_out geometry;
BEGIN

    WITH
    intervals AS (
        SELECT generate_series (0, 9) as steps
    ),
     points as (
            SELECT
                   ST_LineInterpolatePoint(
                       ST_ExteriorRing(((ST_Dump(polygon)).geom)),
                       steps/(select count(steps)::float-1 from intervals)
                       ) as point
            FROM intervals
     )
SELECT into geom_out
    point
    FROM
        intervals, points;

RETURN query select geom_out;
END;
$$
LANGUAGE plpgsql ;

And function call. Now it not crashes, returns only one result , but I need all geometries.

with polygon as (
    select geometry from property_gis where id=1247675
)
select get_points(polygon.geometry)
from polygon;

Any suggestions?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.