PostgreSQL left join two tables to one table how showing duplicates on same row separated by comma

Question

I have three tables in PostGIS, point, poly_a with column "status" and poly_b with column "veg". The point table should get attributes from all polygons it intersects. It works but I noticed that I get duplicates if a point or several points falls inside the overlaps between 2 or several polygons and I can't with my limited SQL get the result that I want to get.

I need for duplicate values to end up on same row but with a separator.

Here is the SQL-code I got now, that I got help from user geozelot to construct.

SELECT
  point.*,
  poly_a."status",
  poly_b."veg"
FROM
  point
  LEFT JOIN poly_a
    ON ST_Intersects(poly_a.geom, point.geom)
  LEFT JOIN poly_b
    ON ST_Intersects(poly_b, point.geom)
;

I attached an image to make my writing more understandable.

Edit: forgot to mention that it should become a view, and point table has around 4 million points.

Besides the solution by Nepluisse below, things you might want to consider from a performance perspective if not already done: a) define Gist indexes on your geometries, b) possibly use materialized views to avoid the Intersects to be computed everytime you query the view, but only once when the source data changes and you refresh the materialized view — Kasper
– Kasper, Commented Nov 4, 2022 at 14:42
Nice input about speed, they have gist index so do i create materialized view in same way? Iam using pgadmin and now i just right click on views and chose create. Are materiallized views dangerous? It would be unfortunate if the tables the view are created on get records deleted. — esset
– esset, Commented Nov 4, 2022 at 15:56
No risk at all for your reference tables. Search for 'Materialized views' in Postgresql documentation and tutorials. They are created in a very similar way you would create views, only difference is that they are only recomputed when you explicitely refresh them. I found DBeaver to be much nicer than PGadmin as a Postgrsql admin tool (including Materialized views creation tools + nice Spatial representation of your data). I discovered this a few weeks ago, with some spatial joins over millions of lines. Spatial indexing + Materialized views were unavoidable meet correct levels of performance — Kasper
– Kasper, Commented Nov 4, 2022 at 17:33

nepluisse · Accepted Answer · 2022-11-04 13:08:38Z

5

You can achieve your desired output with a group by statement and the STRING_AGG() function:

SELECT
  point.id, point.geom, --list all attributes you need from point
  STRING_AGG(poly_a.status,','),
  STRING_AGG(poly_b.veg,',')
FROM point 
  LEFT JOIN poly_a
    ON ST_Intersects(poly_a.geom, point.geom)
  LEFT JOIN poly_b
    ON ST_Intersects(poly_b, point.geom)
GROUP BY point.id, point.geom --you need to name all your desired attributes from point here
;

answered Nov 4, 2022 at 13:08

nepluisse

6384 silver badges11 bronze badges

Add a comment |

Stack Exchange Network

PostgreSQL left join two tables to one table how showing duplicates on same row separated by comma

1 Answer 1

Your Answer

Hot Network Questions

PostgreSQL left join two tables to one table how showing duplicates on same row separated by comma

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions