2

I'm just getting started on the WITH RECURSIVE function in PostGIS. I'm trying to recurse through a list of ids, passing them to through to the quadgrid function (credit goes to Mark Wynter at dimensionaledge). The quadgrid function only accepts a single geometry row as input.

The ids are returned via the query:

SELECT parent.gid FROM proc.gfn_rdsid78 parent

The ids are then called in the WHERE clause:

SELECT the_geom::geometry(Polygon, 4326) as geom
FROM DE_RegularQuadGrid((SELECT child.geom FROM proc.gfn_rdsid78 child WHERE child.gid = 1276643), 
                    'rds_autogenerated.rdsid78_pointseed_urbanflood_singlepoint',
                    'geom', 
                    3, 
                    10)

What would be the optimal of getting back my unioned geometries?

  • I am not sure I fully understand the question. If you look at the De_RegularQuadGrid function definition it returns RETURNS TABLE (depth integer, the_geom GEOMETRY, cell_value double precision) , ie, it returns a table, one of the rows of which, is the geometries of the quad grid. – John Powell Apr 26 '18 at 9:02
  • My problem is not so much with the output of De_RegularQuadGrid but the input. The function requires "parent_geom", a single geometry, as a first argument - rather than multiple geometry rows as returned by "SELECT parent.gid FROM proc.gfn_rdsid78 parent". My question is on finding the most efficient method to run the DE_RegularQuadGrid function recursively on all rows? – user2455068 Apr 27 '18 at 9:23
1

My current solution looks like this:

DO $$   
DECLARE
  rec record;
BEGIN
  FOR rec IN 
        SELECT distinct(gid) FROM proc.gfn_rdsid78
        order by gid
  LOOP
     RAISE NOTICE '% value is %', rec.gid, quote_nullable(rec.gid);
     EXECUTE 'INSERT INTO proc.loop_temptable(geom) 
     SELECT the_geom::geometry(Polygon, 4326) as geom 
     FROM DE_RegularQuadGrid((SELECT (ST_Dump(geom)).geom AS geom FROM proc.gfn_rdsid78 child where child.gid = ' || rec.gid || '), 
                    ''rds_autogenerated.rdsid78_pointseed_urbanflood_singlepoint'',
                    ''geom'', 
                    3, 
                    10)';
  END LOOP;
  Return;
END; $$

Not sure this is very efficient though.

0

Most loops you can avoid by using a lateral join. Which is actually some kind of a loop through the rows of your first selection and a union after. The geometry from the subquery is merged into the geometry of your final table. I do use this approach for calculating service areas from a given set of points, therefore the idea is the same. But you need to check if it works for your data. I just copied parts of your query to the right places in the lateral query without knowing how your function is working. Smaller adjustments may be necessary. (Postgresql 9.3 is minimum for lateral queries)

select 
    parent.gid,
    quadgrid.geom
from 
    proc.gfn_rdsid78 as parent
lateral
    (SELECT the_geom::geometry(Polygon, 4326) as geom
    FROM DE_RegularQuadGrid((SELECT geom FROM proc.gfn_rdsid78) as temp, 
        'rds_autogenerated.rdsid78_pointseed_urbanflood_singlepoint',
        'geom', 
        3, 
        10)) as quadgrid;

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.