4

I don't understand how I can select a raster and polygon by ID and then clipping them.

Something like:

SELECT ST_CLIP(selected_raser.rast, 1, selected_polygon.geometry)
From
     raster WHERE ID = 99 AS selected_raster,
     Polygon WHERE ID = 999 AS selected_polygon;

But somehow I can't Do WHERE for multiple tables. Do I have to join or combine them before?

1
  • 2
    Your are joining them - with an implicit CROSS JOIN. You can only have a single WHERE block per query, but you can combine its statements logically (e.g. AND). This is very fundamental language syntax - you want to give it a deep dive, if only to save you heaps of pain...
    – geozelot
    Commented Jan 26 at 17:59

1 Answer 1

3

I have a raster table named nh, and a polygon table named polygon.

I want to clip raster with rid=1 with polygon with id=1:

CREATE TABLE test.clipped_raster AS
SELECT 1 AS id, st_clip(r.rast, p.geom) AS rast
FROM test.nh AS r
JOIN test.polygons AS p
ON st_intersects(r.rast, p.geom)
WHERE r.rid=1 AND p.id=1

enter image description here

2
  • 1
    I had to add add the "::geometry" cast method to the inputs of ST_INTERSECTS and to the polygon for ST_CLIP. Then it worked perfectly. Thank you!
    – Squeezie
    Commented Jan 27 at 10:04
  • @Squeezie : can you share full SQL if possible,
    – iamlaksh1
    Commented Apr 30 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.