0

I have polygon data and point data in PostGIS database. I want to add a new column to polygon table that contains some value from point data (one column) if point is inside polygon. If there are multiple points inside polygon I want to add field data from all points in one colum.

Is there any way to do this using postgis?

1

Here is a solution to the query part of the solution. You should be able to turn this into an UPDATE query.

You did not specify how to combine the collected point attribute values into a single column. array_agg is used here, but you may wish to use a different technique.

WITH polys(geom) AS (VALUES
( 'POLYGON ((10 50, 40 50, 40 10, 10 10, 10 50))'::geometry ),
( 'POLYGON ((70 50, 70 10, 40 10, 40 50, 70 50))'::geometry )
),
pts(id, geom) AS (VALUES
( 1, 'POINT (19 39)'::geometry ),
( 2, 'POINT (29 24)'::geometry ),
( 3, 'POINT (50 37)'::geometry ),
( 4, 'POINT (26 4)'::geometry ),
( 5, 'POINT (57 4)'::geometry ),
( 6, 'POINT (66 25)'::geometry )
)
SELECT ids, polys.geom
FROM polys 
JOIN LATERAL (SELECT array_agg(id) AS ids
    FROM pts WHERE ST_Intersects(polys.geom, pts.geom)) AS t ON true;

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.