I am not very practiced in PostGIS. However, my volume of data forces me to use it. I have a large polygon layer containing several million polygons and a grid layer that has a point every 10 meters within the bounding box of the polygon layer (see picture). I now want to extract the attributes of the underlying polygon at each point into a new point layer. It should be an outer join where the point id from the original grid and all attributes from my polygon shapefile are added to the resulting point layer. I achieved this in R with a small test data set pretty easily and now need a PostGIS equivalent. My attempts so far have failed.
R version for little data set:
polygons <- st_read("polygons.shp") grid <- st_read("grid.shp") result <- st_intersection(polygons, grid)
My attempt so far in PostGIS which has failed:
DROP TABLE IF EXISTS result; CREATE TABLE result AS SELECT a.geom AS ageom, b.geom AS bgeom, ST_Intersection(a.geom, b.geom) As intersect_ab FROM polygons a INNER JOIN grid b ON ST_Intersection(a,b) WHERE ST_Overlaps(a.geom, b.geom);