Update one table from another based on spatial join

Question

In beginning i had two tables of points and i wanted create from one of this POINT table new table CIRCLES which will contain circle polygons 300 metres around points. This step i have done here:

INSERT INTO circles (geom) ( SELECT ST_Buffer(point, 300, 'quad_segs=8') FROM points );

Now I have two tables. First table CIRCLES contain 10 polygons (circles) and each circle have unique serial number (1,2,3,..10). Second table POINTS contain 500 points. How can I, get this result: If any point from table POINTS is located inside polygon (circle) from table CIRCLES, so in table POINTS in column PLGN we will write value of serial number of circle in which is this point inside.

In this task we don't consider case, that one point can be inside two or more circles.

Answer 1

In general to update one table from another, and this applies whether there is a spatial join condition or not, you follow the UPDATE...SET...FROM...WHERE pattern, see the docs. In this case, your WHERE condition is an ST_Contains or ST_Intersects, but it is essentially the same as joining on two IDs or some other condition. It is often convenient to use aliases, especially if you are joining on two fields with the same name, such as the two geoms here.

UPDATE points p 
SET plgn = poly.id 
FROM polygon poly 
WHERE ST_Contains(poly.geom, p.geom);

If you are certain that only one point is inside each polygon, then you are done. If not, then look at using Distinct and some rule, to determine which polygon ID to use.