In the PostGIS SQL Editor, I just want to make a loop to make this function work
This query is to give the UTM Zone number to one column of Global DTM raster table
For Loopid = 0 to 1000, then execute the following statments:
UPDATE public.globaldtm
SET "UTM" = loopid
WHERE rid IN (SELECT rid FROM globaldtm WHERE ST_Intersects(rast,(select geom from utm where gid =loopid)));
How do I write a simple code inside this SQL editor to make the loop work?
This is doing the same thing without the loop:
UPDATE public.globaldtm
SET "UTM" = sub.gid
FROM (
SELECT rid, sub.gid
FROM globaldtm
JOIN utm ON ST_Intersects(rast, utm.geom) = 1
ORDER BY utm.gid, rid
LIMIT 1000) as sub
WHERE rid = sub.rid
limit instead of top n?
LIMIT typically appears after ORDER BY.
There are two more answers which is more suitable to my question. I got it from Stack Overflow.
1st :
UPDATE public.globaldtm
SET UTM = loopid
FROM generate_series(0, 1000) as x(loopid) -- Added this line
WHERE rid IN (SELECT rid FROM globaldtm WHERE ST_Intersects(rast,(select geom from utm where gid =loopid)));
2nd :
CREATE OR REPLACE FUNCTION updateValuesWithLoop()
RETURNS void
AS $$
BEGIN
FOR Loopid IN 0..1000 LOOP
UPDATE public.globaldtm
SET "UTM" = loopid
WHERE rid IN (SELECT rid FROM globaldtm WHERE ST_Intersects(rast,(select geom from utm where gid =loopid)));
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
Execute the function:
select updateValuesWithLoop()
TOP 1000would work the same