Postgis Query to join based on two individual table to matching two individual table with value being seperated by 'comma" in both table

Question

I have polygon(properties) in postgis table and have a info of excel which need to join on posting table property. But it's not simple join it based on two column with matching individual column with separated value by "," .

Postgis table

Excel table

I want match if any of comma seperated match the value in respective column. In above case all should be matched.

So far I tried this script but not work good.

SELECT tbl1.gid, id,tbl1.matchid1,cast(tbl2."matchid1" AS varchar) ,  tbl1.matchid2,cast(tbl2.matchid2 AS varchar),  tbl1.geom
    FROM public.tbl1 tbl1 left join  tbl2 on (cast(tbl1.matchid1 AS varchar) ~~ cast(tbl2.matchid1 AS varchar)) or
    (cast(tbl1.matchid2 AS varchar) ~~ cast(tbl2.matchid2 AS varchar))

Answer 1

Don't try this with regex comparison. Since your data is already nicely delimited with comma's, just put it into arrays. Here is the way to go: Make new array columns (type TEXT[]) to your tables where you use the function string_to_array(matchid1,',') Join the tables based on matching array values tbl1.matchid2_array && tbl2.matchid2

If this is still slow (it probably is) then make sure you build indexes on the new array columns.