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

Improve this question
1
  • 2
    Thank you @BERA, it works fine. Commented Nov 3, 2020 at 11:25

1 Answer 1

Reset to default
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
3
  • Wonderful! Until now I had never used the array function. Very interesting. Thank you very much Commented Nov 3, 2020 at 11:38
  • @DanielePiccolo make sure you are using the <base_table>.<id> to GROUP BY. Commented Nov 3, 2020 at 12:36
  • 2
    Sorry @geozelot.. just missing a space next to, to define the array. I had already deleted my comment Commented Nov 3, 2020 at 13:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.