PostGIS: intersections, multiple SRIDs

Someone asked me to give them a map of all the rivers in the state of Ohio. Sounded like a fun PostGIS exercise for me. Unfortunately I’ve made it more complex because my river database is in SRID 900913 by default, so intersecting that with the Natural Earth admin1 data in a different SRID is not as simple as a one line query. I could just transform Natural Earth on import but ogr2ogr pretends to not know 900913 and that seemed bogus anyway. Some notes from my goofing around:


-- state boundary data is in EPSG:4326
# select ST_SRID(wkb_geometry) from admin1 limit 1;
4326

-- Coerce the test point to the SRID that the state boundary data is in
# select name from admin1 
  where ST_Intersects(wkb_geometry, 
                      ST_Transform(ST_GeomFromEWKT('SRID=4269;POINT(-82.98 39.98)'), 4326));
Ohio

-- try querying with a point inside Ohio but with mismatching SRIDs
# select name from admin1
  where ST_Intersects(wkb_geometry,
                      ST_GeomFromEWKT('SRID=4269;POINT(-82.98 39.98)'));
ERROR:  Operation on mixed SRID geometries

-- Coerce the test point to 900913, which should not work.
-- Bafflingly does not give an error for mixed SRIDs, just returns 0 rows
# select name from admin1
  where ST_Intersects(wkb_geometry,
                      ST_Transform(ST_GeomFromEWKT('SRID=4269;POINT(-82.98 39.98)'), 900913));
(0 rows)

-- Coerce the state boundary geometry to the test point's SRID
# select name from admin1 
  where ST_Intersects(ST_Transform(wkb_geometry, 4269),
                      ST_GeomFromEWKT('SRID=4269;POINT(-82.98 39.98)'));
Ohio