I need to update a single attribute of a large table of point data with a result of a spatial join (point on area operation) on a polygon table. I'm using the below query but wondering if this is the best performance approach I could take. I have geometry indexes on both tables and this is a one time operation.

update point_table pt set name = po.name 
from polygon_table po where ST_contains(po.geom, pt.geom);
  • If your polygons are complicated having typically thousands of vertices it might make sense to split them first. If they are simple or if you are not hurry then just go ahead.
    – user30184
    Commented May 3, 2016 at 9:29
  • they are not complex , but they are many and points are millions . Do you belleive this should be the most performant query for this operation ? Commented May 3, 2016 at 10:30
  • PostGIS should be quite well optimized to handle such data. How long does it take to update for example thousand rows with LIMIT 1000?
    – user30184
    Commented May 3, 2016 at 10:36
  • Turn the query inside out and use the polygons to find points. Instead of millions of point-in-poly tests, you'll have thousands of poly-cointains-point.
    – Vince
    Commented May 3, 2016 at 10:50
  • adding a LIMIT 1000 at end of this query ; seems not to work. Any ideia how to limit this query ? Commented May 3, 2016 at 10:50

1 Answer 1


Updating is often quite slow. If it is possible, it is probably a lot faster to recreate the whole table like

create table new_table as 
select pnt.*, poly.name from point_table pnt inner join poly_table poly
on st_within(pnt.geom, poly.geom);

I do this a lot and rename the new table to the original name and recreate indexes and constraints on it. If it is huge tables it is often worth the hazzle.

But be aware that if you have overlapping polygons and points in the overlapping areas, you will get extra rows.

If you use your update approach those cases gives unpredictable result (you don't know which of the overlapping polygons that gives name to the point) Also be aware that points on the boundary gives different result if you use contain/within vs intersects.

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.