<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Trebuchet MS";
panose-1:2 11 6 3 2 2 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.EmailStyle20
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
mso-ligatures:none;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple style='word-wrap:break-word'><div class=WordSection1><p class=MsoNormal>Didn’t notice you were talking about this - <a href="https://github.com/gbb/par_psql">https://github.com/gbb/par_psql</a><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>New to me, but yes that as a driver should work fine.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I’d still use a procedure and just use par_sql to drive the runs of that procedure.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Generate the driver query something like<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>SELECT 'CALL clip_rasters(' || i::text || ', ' || (i + j - 1)::text || '); -- &'<o:p></o:p></p><p class=MsoNormal>FROM (SELECT 10) AS f(j), generate_series((SELECT min(id) FROM gfdl_03_temp ), (SELECT max(id) FROM gfdl_03_temp ), j) AS I;<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>And then copy that into your par_sql script.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Replace the number 10 with whatever iteration you prefer to use.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b>From:</b> Regina Obe <lr@pcorp.us> <br><b>Sent:</b> Tuesday, November 14, 2023 11:07 PM<br><b>To:</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br><b>Cc:</b> 'Manaswini Ganjam' <manu.ganjam@gmail.com><br><b>Subject:</b> RE: [postgis-users] par_psql code for queries<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I’m guessing your intention is to run this in separate connections cause otherwise sad to say you can’t do this in parallel without having some sort of background processor.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Something also seems incorrectly shuffled in your query.<o:p></o:p></p><p class=MsoNormal>First of all, you want a COMMIT before your end LOOP otherwise not much point to doing this in a loop since it wouldn’t only commit when all is done.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Also may be okay, but looks like you are relying on filename only appearing once in gfdl_03_temp? Might be safe, but just in case, I’d add the ID in your routine<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>I also think it would be better to just have this in a stored procedure instead of a DO command, so that you can easily call it in separate connections.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>So here is my revised<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>CREATE TABLE clipped_rasters(<o:p></o:p></p><p class=MsoNormal>id serial PRIMARY KEY,<o:p></o:p></p><p class=MsoNormal>clipped_raster raster,<o:p></o:p></p><p class=MsoNormal>filename text, id_tile integer);<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>CREATE INDEX ix_clipped_rasters_id_tile ON clipped_rasters(id_tile);<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>CREATE OR REPLACE PROCEDURE clip_rasters(param_start integer, param_end integer) AS<o:p></o:p></p><p class=MsoNormal>$body$<o:p></o:p></p><p class=MsoNormal>DECLARE <o:p></o:p></p><p class=MsoNormal>min_lon numeric := -98; -- Minimum Longitude<o:p></o:p></p><p class=MsoNormal>raster_row record;<o:p></o:p></p><p class=MsoNormal>min_lat numeric := 23; -- Minimum Latitude<o:p></o:p></p><p class=MsoNormal>max_lon numeric := -74; -- Maximum Longitude<o:p></o:p></p><p class=MsoNormal>crs integer := 4326; -- Coordinate Reference System (CRS)<o:p></o:p></p><p class=MsoNormal>max_lat numeric := 41; -- Maximum Latitude<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>BEGIN<o:p></o:p></p><p class=MsoNormal>FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp AS t WHERE id BETWEEN param_start AND param_end <o:p></o:p></p><p class=MsoNormal> AND NOT EXISTS(SELECT 1 FROM clipped_rasters AS n WHERE n.id_tile = t.id ) )<o:p></o:p></p><p class=MsoNormal>LOOP<o:p></o:p></p><p class=MsoNormal>RAISE NOTICE 'Starting filename: %, row: %, time: %', raster_row.file_name, raster_row.id, clock_timestamp();<o:p></o:p></p><p class=MsoNormal>INSERT INTO clipped_rasters (filename, clipped_raster_f, id_tile)<o:p></o:p></p><p class=MsoNormal>SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, max_lon, max_lat, crs)), raster_row.id<o:p></o:p></p><p class=MsoNormal>FROM gfdl_03_temp<o:p></o:p></p><p class=MsoNormal>WHERE id = raster_row.id;<o:p></o:p></p><p class=MsoNormal>COMMIT;<o:p></o:p></p><p class=MsoNormal>RAISE NOTICE 'Ending filename: %, row: %, time: %', raster_row.file_name, raster_row.id, clock_timestamp();<o:p></o:p></p><p class=MsoNormal>END LOOP;<o:p></o:p></p><p class=MsoNormal>RETURN;<o:p></o:p></p><p class=MsoNormal>END $body$ language plpgsql;<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>-- then to run for tiles 1 to 10 do below. You want to run a different range in different sessions so they don’t run the risk of conflicting each other.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>CALL clip_rasters(1,10);<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b>From:</b> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org">postgis-users-bounces@lists.osgeo.org</a>> <b>On Behalf Of </b>Manaswini Ganjam via postgis-users<br><b>Sent:</b> Tuesday, November 14, 2023 1:44 PM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br><b>Cc:</b> Manaswini Ganjam <<a href="mailto:manu.ganjam@gmail.com">manu.ganjam@gmail.com</a>><br><b>Subject:</b> [postgis-users] par_psql code for queries<o:p></o:p></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><div><div><div><p class=MsoNormal style='margin-bottom:12.0pt'>Hi, I want to do parallel processing for this code below:<br><br>CREATE TABLE clipped_rasters_f (<br>id serial PRIMARY KEY,<br>clipped_raster raster<br>filename text,<br>DECLARE<br>DO $$<br>);<br>min_lon numeric := -98; -- Minimum Longitude<br>raster_row record;<br>min_lat numeric := 23; -- Minimum Latitude<br>max_lon numeric := -74; -- Maximum Longitude<br>crs integer := 4326; -- Coordinate Reference System (CRS)<br>max_lat numeric := 41; -- Maximum Latitude<o:p></o:p></p><div><p class=MsoNormal>BEGIN<br>FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp)<o:p></o:p></p><div><p class=MsoNormal>LOOP<br>INSERT INTO clipped_rasters (filename, clipped_raster_f)<br>SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat, max_lon, max_lat, crs))<br>FROM gfdl_03_temp<br>WHERE id = <a href="http://raster_row.id">raster_row.id</a>;<o:p></o:p></p></div><div><p class=MsoNormal>END LOOP;<o:p></o:p></p><div><p class=MsoNormal>END $$; --&<o:p></o:p></p></div><div><p class=MsoNormal>Can someone guide me on understanding the mandatory edits in changing this code or any postgis functions used in a query to convert to a par_psql compatible query?<o:p></o:p></p></div></div></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thank you,<o:p></o:p></p></div><div><div><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Trebuchet MS",sans-serif'>Manaswini Ganjam</span><o:p></o:p></p></div></div></div></div></div></div><p class=MsoNormal><o:p> </o:p></p></div></div></div></div></body></html>