6

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)));

enter image description here

How do I write a simple code inside this SQL editor to make the loop work?

  • 1
    please describe what the query does verbally – underdark Jul 13 '13 at 10:22
  • Why are you looping. That is normally not what you want to do. – Nathan W Jul 13 '13 at 10:27
  • Because I have to do it 1000 times, you see the Loopid is changing from 1 to 1000. – Yu Guo Jul 13 '13 at 10:36
  • You don't need that loop. TOP 1000 would work the same – Nathan W Jul 13 '13 at 10:47
7

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
  • correct me if I'm wrong, but doesn't postgresql has limit instead of top n? – nickves Jul 13 '13 at 12:40
  • oppps :) My mistake – Nathan W Jul 13 '13 at 12:46
  • Weird phrasing; LIMIT typically appears after ORDER BY. – Craig Ringer Jul 15 '13 at 3:25
  • Updated the code. Been doing to much TSQL. – Nathan W Jul 15 '13 at 3:28
3

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()

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.