2

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 May 3 '16 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 ? – Jorge Vidinha May 3 '16 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 May 3 '16 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 May 3 '16 at 10:50
  • adding a LIMIT 1000 at end of this query ; seems not to work. Any ideia how to limit this query ? – Jorge Vidinha May 3 '16 at 10:50
2

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, privacy policy and cookie policy

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