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