2

I would like to execute ST_Intersection(clipper_geom, clipped_geom) on number of rows coming from a table.

https://postgis.net/docs/ST_Intersection.html

https://postgis.net/docs/ST_Intersects.html

POSTGIS intersection does not support the processing of multiple geometries natively, differently from ST_Intersects() and I had to design a function (returning a table) that selects rows intersecting with my clipper_geom using ST_Intersects(), loop through the result set and compute each intersection with ST_Intersection(). The geom field and clipped_geom_wkt are the ones recording the clipped geometry.

The function works, but I need a different one for every table we want to produce the clipping. I would like to read the input table dynamically (column name and type) and define both in the RETURN statement.

All field names and types are the same, only geom is updated and clipped_geom_wkt is added.

I tried to search Stack Overflow and I found examples on how to create dynamic table structure but none performs a subsequent LOOP over the first results, where the column names have to be matched to insert / update the new data.

This is what I have come up so far, but I am unsure about how to perform the LOOP part, to add the clipped_geom_wkt field and update the geom field. Some responses advise to use RETURNS TABLE (...) if more fields are added to SETOF ...

https://stackoverflow.com/questions/17821482/easy-way-to-have-return-type-be-setof-table-plus-additional-fields

But then it seems that dynamically generated columns are only supported with RETURNS SETOF ...

https://stackoverflow.com/questions/11740256/refactor-a-pl-pgsql-function-to-return-the-output-of-various-select-queries/11751557#11751557

https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret/8611675#8611675

CREATE OR REPLACE FUNCTION clip_palin_polygon_complete(clipped_table text,clipper_geom text, age_sequence VARCHAR)
RETURNS TABLE (rec clipped_table, clipped_geom_wkt text)) AS $$ --not sure if this is the right way to do it...
DECLARE var_r record;
BEGIN
    FOR var_r IN (
        SELECT * FROM clipped_table 
        WHERE clipped_table.seq IN (age_sequence)
        AND ST_Intersects(ST_GeomFromText(clipper_geom,4326), clipped_table.geom)
    )
    LOOP
        /* 
        these are the original table fields that I would like to keep and match
        dynamically with any table I have as input (clipped_table)
        objectid := var_r.objectid;
        seq := var_r.seq;
        -- etc.
        */

        --below there are the only two fields that need modifying
        geom := (
            SELECT ST_Intersection(ST_GeomFromText(clipper_geom, 4326), var_r.geom) AS geom);
        clipped_geom_wkt := (
            SELECT
            ST_AsText(ST_Intersection(ST_GeomFromText(clipper_geom,4326), var_r.geom)) AS clipped_geom_wkt);
        RETURN NEXT;
    END LOOP;
END; $$
LANGUAGE 'plpgsql'
  • It would help to clarify your setup: Postgres version, PostGIS version, table definition (minimum example), desired result (for the example). You write The function works, but that can't be the displayed function, which certainly doesn't. Show your working function, describe how you want to change / expand it. You write All fields names and types are the same. The same between function input and output, or the same over all possible input tables? Seems you are trying to derive a return type dynamically from variable tables, plus one additional column? – Erwin Brandstetter Oct 5 '18 at 22:45
1

What you might need

You should be able to avoid all the complication with a simple SELECT instead:

SELECT t.*, ST_Intersection(x.geom, t.geom) AS geom2
FROM   tbl t  -- replace tbl with actual table
JOIN   ST_GeomFromText($clipper_geom, 4326) AS x(geom) ON ST_Intersects(x.geom, t.geom)
WHERE  t.seq = $age_sequence;

Minor difference: you get the original geom and the intersection geom2 in the result. Add the redundant ST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt to the SELECT list if you really need it.

What you ask

I understand your question like this:

I have various input tables, each has a column geom geometry. I want a function to take a table name and clipper_geom geometry as input and return all rows intersecting with my clipper_geom. One column clipped_geom_wkt text shall be appended and the value of geom changed, each showing the intersection with clipper_geom.

That's not trivial. SQL is a static language, a function demands to know the return type at call time at the latest. We can achieve dynamic results with polymorphic types, but we need a well-known row type to work with. The row type of an existing table is good for it, but you want to append another column. That's not easily possible. The workaround is to return the polymorphic row type, plus an extra column (like you seem to have tried already). Decompose the row type in the function call to arrive at your desired result:

CREATE OR REPLACE FUNCTION my_func(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS TABLE (tbl ANYELEMENT, clipped_geom_wkt text) AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);  -- compute once
BEGIN
   FOR tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence  -- data type must match!
       , _geom
   LOOP          
      tbl := tbl #= hstore('geom', ST_Intersection(_geom, tbl.geom));
      clipped_geom_wkt := ST_AsText(tbl.geom);
      RETURN NEXT;
   END LOOP;
END
$func$  LANGUAGE plpgsql;  -- don't quote the language name

Call (important!):

SELECT (tbl).*, clipped_geom_wkt
FROM my_func(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);

Works for me in Postgres 10.

Notable features:

Simpler

I stuck to your design above, but it's unnecessary complication for the given example. The extra column clipped_geom_wkt is completely redundant, just derive it from geom in the function call. Then we can use the input type directly:

CREATE OR REPLACE FUNCTION my_func2(_tbl ANYELEMENT
                                 , clipper_geom text
                                 , age_sequence text)
  RETURNS SETOF ANYELEMENT AS
$func$
DECLARE
   _geom geometry := ST_GeomFromText(clipper_geom, 4326);
BEGIN
   FOR _tbl IN EXECUTE format(
     'SELECT * FROM %s
      WHERE  seq = $1
      AND    ST_Intersects($2, geom)'
    , pg_typeof(_tbl)
      )
   USING age_sequence -- data type must match!
       , _geom
   LOOP
      _tbl := _tbl #= hstore('geom', ST_Intersection(_geom, _tbl.geom));
      RETURN NEXT _tbl;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT *, ST_AsText(geom) AS clipped_geom_wkt -- if you need the redundant column
FROM   my_func2(NULL::public.clipped_table, $my_clipper_geom, $my_age_sequence);
  • 1
    Yes, I acknowledge the approach of creating a function it was over complicating the solution to the problem, but as non-expert in SQL it looked simpler to me to understand. The answer solved my problem and went beyond explaining how to achieve a solution using the same methodology I wanted to use. Thanks a lot. On a side note, I need indeed the WKT representation of the clipped geometries as they are later outputted to another software component which is unable to understand Postgis geom type. – user3523583 Oct 14 '18 at 15:16

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.