1

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.

Rusty Robot
  • 1,725
  • 2
  • 13
  • 29
  • Which version of PostGIS? The 18 counts without SRIDs are possibly NULL. – Mike T Nov 27 '13 at 05:08
  • 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 '19 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 '19 at 06:39

1 Answers1

3

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.

Mike T
  • 41,085
  • 18
  • 152
  • 203
  • 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 '13 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 '13 at 23:44
  • Your SQL was correct, I was using the wrong SRID on both tables. – Rusty Robot Dec 10 '13 at 00:43