Didn’t notice you were talking about this - https://github.com/gbb/par_psql
New to me, but yes that as a driver should work fine.
I’d still use a procedure and just use par_sql to drive the runs of that
procedure.
Generate the driver query something like
SELECT 'CALL clip_rasters(' || i::text || ', ' || (i + j - 1)::text || '); -- &'
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;
And then copy that into your par_sql script.
Replace the number 10 with whatever iteration you prefer to use.
From: Regina Obe <l...@pcorp.us>
Sent: Tuesday, November 14, 2023 11:07 PM
To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org>
Cc: 'Manaswini Ganjam' <manu.gan...@gmail.com>
Subject: RE: [postgis-users] par_psql code for queries
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.
Something also seems incorrectly shuffled in your query.
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.
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
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.
So here is my revised
CREATE TABLE clipped_rasters(
id serial PRIMARY KEY,
clipped_raster raster,
filename text, id_tile integer);
CREATE INDEX ix_clipped_rasters_id_tile ON clipped_rasters(id_tile);
CREATE OR REPLACE PROCEDURE clip_rasters(param_start integer, param_end
integer) AS
$body$
DECLARE
min_lon numeric := -98; -- Minimum Longitude
raster_row record;
min_lat numeric := 23; -- Minimum Latitude
max_lon numeric := -74; -- Maximum Longitude
crs integer := 4326; -- Coordinate Reference System (CRS)
max_lat numeric := 41; -- Maximum Latitude
BEGIN
FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp AS t WHERE id
BETWEEN param_start AND param_end
AND NOT EXISTS(SELECT 1 FROM clipped_rasters AS n WHERE
n.id_tile = t.id ) )
LOOP
RAISE NOTICE 'Starting filename: %, row: %, time: %', raster_row.file_name,
raster_row.id, clock_timestamp();
INSERT INTO clipped_rasters (filename, clipped_raster_f, id_tile)
SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat,
max_lon, max_lat, crs)), raster_row.id
FROM gfdl_03_temp
WHERE id = raster_row.id;
COMMIT;
RAISE NOTICE 'Ending filename: %, row: %, time: %', raster_row.file_name,
raster_row.id, clock_timestamp();
END LOOP;
RETURN;
END $body$ language plpgsql;
-- 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.
CALL clip_rasters(1,10);
From: postgis-users <postgis-users-boun...@lists.osgeo.org
<mailto:postgis-users-boun...@lists.osgeo.org> > On Behalf Of Manaswini Ganjam
via postgis-users
Sent: Tuesday, November 14, 2023 1:44 PM
To: postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
Cc: Manaswini Ganjam <manu.gan...@gmail.com <mailto:manu.gan...@gmail.com> >
Subject: [postgis-users] par_psql code for queries
Hi, I want to do parallel processing for this code below:
CREATE TABLE clipped_rasters_f (
id serial PRIMARY KEY,
clipped_raster raster
filename text,
DECLARE
DO $$
);
min_lon numeric := -98; -- Minimum Longitude
raster_row record;
min_lat numeric := 23; -- Minimum Latitude
max_lon numeric := -74; -- Maximum Longitude
crs integer := 4326; -- Coordinate Reference System (CRS)
max_lat numeric := 41; -- Maximum Latitude
BEGIN
FOR raster_row IN (SELECT id, filename, rast FROM gfdl_03_temp)
LOOP
INSERT INTO clipped_rasters (filename, clipped_raster_f)
SELECT raster_row.filename, ST_Clip(rast, ST_MakeEnvelope(min_lon, min_lat,
max_lon, max_lat, crs))
FROM gfdl_03_temp
WHERE id = raster_row.id <http://raster_row.id> ;
END LOOP;
END $$; --&
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?
Thank you,
Manaswini Ganjam
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
