2

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.

  • You asked the same question yesterday. Have tried anything yet? You might want to look at ST_DWithin, ST_Intersects, ST_Contains and similar functions to get started. – John Powell Mar 15 '16 at 13:21
  • Yesterday I received no response. Only thing I managed is, that now I have polygons and points. So i learned how to create polygon with function ST_buffer from single point, but now I am lost. I'am very bad programmer. – hanznv Mar 15 '16 at 13:25
  • 1
    Please edit the question with what you have done so far and where you are stuck. – John Powell Mar 15 '16 at 13:26
  • You can always use a GIS like QGIS or the like, if your having no luck with SQL. A simple SQL approach will be to join the two tables to a new one, leaving out the points not in a circle like: CREATE TABLE inspection AS ( SELECT p.*, c.plgn FROM points p, circles c WHERE ST_Contains(c.geom, p.geom) ) – Jakob Mar 15 '16 at 14:09
  • If you want points within 300 m of the polygons, you might actually want to use ST_DWithin(poly.geom, p.geom, 300.0), which does not require an expensive and imprecise buffer op. – Mike T Mar 15 '16 at 22:30
3

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.

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.