Join on field with multiple values

Question

I have a field "istat" that contains multiple values ​​of the istat code (municipal code):

and a table containing the centroids of the municipalities

I am looking for a way to join the "istat"-fields of the two tables in order to create a table with multipoint geometry with the centroids of the common.

I thought of creating as many "istat" columns as the maximum number of "istat"-codes present in a cell, doing a multiple join.

Finally combine all the geom fields creating a multipoint field (if it is possible to do such a thing).

Answer 1

You can directly JOIN on the array members:

SELECT a.<id>,
       ST_Collect(b.centroid) AS geom
FROM   <base_table> AS a
JOIN   <centroid_table> AS b
  ON   b.istat = ANY(a.istat)
GROUP BY
       a.<id>
;

This may profit from a GIN index on a.istat.

---

In case you are having a TEXT column rather than an ARRAY, you can pre-serialize your string into an array:

SELECT a.<id>,
       ST_Collect(b.centroid) AS geom
FROM   <base_table> AS a
JOIN   <centroid_table> AS b
  ON   b.istat = ANY(STRING_TO_ARRAY(a.istat, ', ')::<TYPE>[])
GROUP BY
       a.<id>
;

where you need to replace <TYPE> with the actual data type you are working with in both tables, if different than TEXT