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:


 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);

enter image description here

  • 3
    Join on ST_Intersects (the boolean condition) versus ST_Intersection (the result of the geometric intersection). – John Powell Mar 18 '20 at 11:24

A simple query to do this is:

SELECT pt.id, poly.*
  FROM grid pt
  JOIN polygons poly ON ST_Intersects(poly.geom, pt.geom);

One caveat: this will return multiple records if a point lies in multiple polygons. To ensure only a single record is returned per point, and also to include points which do not lie in any polygon, use:

SELECT pt.id, poly.*
  FROM grid pt
    (SELECT * FROM polygons poly 
       WHERE ST_Intersects(poly.geom, pt.geom) LIMIT 1) AS poly ON true;
  • Thank you, this is very helpful! I have no interest in keeping the points that don't lie in a polygon. How can I adjust the function to leave them out? – fahrtrad Mar 19 '20 at 23:41
  • Use INNER JOIN (or just JOIN) instead of LEFT OUTER JOIN – dr_jts Mar 20 '20 at 5:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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