Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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

Ask 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.
                I have the same issue under Debian Stretch but when I run from the command line with psql -d"mydb" -c "SELECT ..." it works perfectly showing that its a problem inside pycopg2 specifically. Could you fix it?
– Eamonn Kenny
                Jul 23, 2019 at 13:05
                Scratching the very bottom of my memory, if I recall correctly, I may have just set all the NULL SRIDs to 1 or matched them with the SRID of the rest of the data.
– Rusty Robot
                Jul 24, 2019 at 6:39
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.
                Thanks Mike, while the SQL query executes it does not return any matches. I know that regions I have do overlap however none are return by the query. Do you have a possible explanation? Thanks.
– Rusty Robot
                Nov 27, 2013 at 23:35
                Your SQL query did work in finding neighboring (intersecting) regions on the same table. I do require intersecting regions across tables. Any further help you can provided is appreciated. I will continue to tackle the problem and let you know of any developments.
– Rusty Robot
                Nov 27, 2013 at 23:44
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.