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
…
But then it seems that dynamically generated columns are only supported with RETURNS SETOF ...
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'
Best Answer
What you might need
You should be able to avoid all the complication with a simple
SELECT
instead:Minor difference: you get the original
geom
and the intersectiongeom2
in the result. Add the redundantST_AsText(ST_Intersection(x.geom, t.geom)) AS clipped_geom_wkt
to theSELECT
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 andclipper_geom geometry
as input and return all rows intersecting with myclipper_geom
. One columnclipped_geom_wkt text
shall be appended and the value ofgeom
changed, each showing the intersection withclipper_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:
Call (important!):
Works for me in Postgres 10.
Notable features:
Note how the row variable
tbl
is defined implicitly in theRETURNS
clause, where it copies the input type dynamically (ANYELEMENT
). So you are not bound to useSETOF
with polymorphic types like you speculated.Related (you linked to that one yourself):
FOR-IN-EXECUTE
to loop over a dynamic query.#=
. See:(tbl).*
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 fromgeom
in the function call. Then we can use the input type directly:Call: