I am executing the following query
SELECT
val b1, geom, gid, ggeom
FROM
(SELECT dp.*, g.gid gid, g.geom ggeom
FROM raster_grid g, public.t32ulc_entire_tile rast,
LATERAL ST_PixelAsCentroids(rast.rast, 1) as dp
WHERE ST_Intersects(rast.rast, g.geom)) foo;
and get this result
| b1 | geom | gid | ggeom |
|---|---|---|---|
| 5135.0 | POINT (300005 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 4994.0 | POINT (300015 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 4515.0 | POINT (300025 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 3942.0 | POINT (300035 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 3428.0 | POINT (300045 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 4364.0 | POINT (300055 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
| 4796.0 | POINT (300065 5800015) | 63 | POLYGON ((300620 5800010, 300620 5800020, 300630 5800020, 300630 5800010, 300620 5800010)) |
As you may noticed while values in the geom column are changing, the values in columns gid, ggeom are duplicating which means that is not correct. It should be different, f.e, gid = 1,2,3,.... What is wrong in the query?
CodePudding user response：
Instead of using WHERE ST_Intersects(rast.rast, g.geom)), try using the PostGIS intersection operator. && for 2D intersection and &&& for 3D intersection. This will replace your CROSS JOIN with an INNER JOIN and still apply the intersection logic:
SELECT dp.*
, dp2.*
, g.gid gid
, g.geom ggeom
FROM public.t32ulc_entire_tile rast
, LATERAL ST_PixelAsCentroids(rast.rast, 1) as dp
, LATERAL ST_PixelAsCentroids(rast.rast, 2) as dp2
JOIN raster_grid g ON dp.geom && g.geom OR dp2.geom && g.geom
;