[postgis-users] par_psql code for queries

[Regina Obe]

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-bounces at lists.osgeo.org> On Behalf Of Manaswini Ganjam via postgis-users
Sent: Tuesday, November 14, 2023 1:44 PM
To: postgis-users at lists.osgeo.org
Cc: Manaswini Ganjam <manu.ganjam at 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

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20231114/38cb81ea/attachment.htm>