Find a point in a multipolygon geometry

Question

I need to know which points from Table 1 are contained in Table 2.

In Table 1 I have longitude and latitude and I made points with ST_SetSRID(ST_MakePoint(d.longitude, d.latitude),4326) as geom

Table 2 has a collection of multipolygons: geom geometry(MultiPolygon,4326)

I know it is a collection because when I run select ST_IsCollection(geom)from table2 it is TRUE, but when I want to know how many collections with SELECT ST_NumGeometries(geom)from table 2, it returns 1.

I know that since I have a collection I have to use st_INTERSECTS.

select TABLE1.*, TABLE2.*
from TABLE1, TABLE2
where ST_Intersects(TABLE2.geom, TABLE1.geom)

But it is empty. And I know from plotting the data that they intersect.

Do you know what might be wrong?

Answer 1

Have a look at St_Dump: https://postgis.net/docs/ST_Dump.html and Using multipolygon or polygon features for large intersect operations?

Assuming that Table2 are the polygons, this would be:

select T1.*, T2.*
from TABLE1 as T1, TABLE2 as T2
where ST_Intersects(St_Dump(T2.geom), T1.geom)