POSTGIS: ERROR: Operation on mixed SRID geometries. Trying to find overlapping geoms with two different SRIDS/tables

Question

POSTGIS_VERSION=2.1;

I have two tables with two different SRID. My objective is to show which geoms from TABLE_B intersect with a region from TABLE_A.

SELECT tablebname, a.geom FROM TABLE_B as a INNER JOIN (SELECT geom FROM TABLE_A WHERE tableAID = '00001') as b ON ST_Intersects(a.geom, b.geom);

My table structure (truncated) is as follows

TABLE_A
text tableAid
geometry geom (SRID=3577)

TABLE_B
text tableBid
geometry geom (SRID=4326)

I have tried transforming the geoms with ST_TRANSFORM(geom, 3577) but I still get the same error "ERROR: Operation on mixed SRID geometries."

Using the following command
select distinct(ST_SRID(geom)) as srid, count(*) from tableA group by srid;

I get the following

srid | count
3566 | 2196
     |   18

My attempts at changing the last 18 to 3577 are futile. Each time I update the SRID it says it succeeded or did not find any SRID <> 3577.

Any help would be appriciated. I can provided more details in required. Thanks.

Answer 1

Without a subquery, correcting the table alias, and using a transform, does this work?

SELECT b.*, a.*
FROM table_b AS b
INNER JOIN table_a AS a ON
  tableaid = '00001' AND ST_Intersects(ST_Transform(a.geom, 4326), b.geom);

Also note that in your original query, you confuse TABLE_B as a, which possibly explains why your attempt with ST_Transform failed, since it wasn't transforming the intended a.geom column. Furthermore, there is no such version of PostGIS 9.1, so I'm guessing you have 2.x. If setup using typmods (e.g., geometry(Polygon, 4326)), these cannot have mixed SRIDs.