4

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

enter image description here

and a table containing the centroids of the municipalities

enter image description here

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).

5

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

| improve this answer | |
  • Wonderful! Until now I had never used the array function. Very interesting. Thank you very much – Daniele Piccolo Nov 3 at 11:38
  • @DanielePiccolo make sure you are using the <base_table>.<id> to GROUP BY. – geozelot Nov 3 at 12:36
  • 2
    Sorry @geozelot.. just missing a space next to, to define the array. I had already deleted my comment – Daniele Piccolo Nov 3 at 13:09

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.