Operation on mixed SRID geometries

Question

[I'm new to the site and my English is poor, so it is difficult for me to explain my problem. But there is no other person I can ask.]

My question is related to this exercise http://postgis.net/workshops/postgis-intro/joins.html, but it gives an error when I run it.

SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';

The error is Operation on mixed SRID geometries

Answer 1

Go back to Section 4 and make sure you input SRID 26918 as the SRID for the shapefiles you loaded (if you loaded them in pgAdmin).

PostGIS will throw and error if the SRID's being operated on directly aren't the same.

There's some description in Section 15 about this.

You can check the SRID on your geom columns using:

SELECT ST_SRID(geom) FROM nyc_streets_or_other_table LIMIT 1;

Answer 2

@DPierce has answered the specific question but more broadly, if you're trying to do an operation on mixed SRID geometries, you can use the ST_TRANSFORM function to cast your geometry into the SRID of the other geometry. I usually call this on the smaller data set just to make it a little more efficient (I'm not sure if there's really a benefit). You're example above would look like this:

SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(ST_Transform(neighborhoods.geom, 26918), subways.geom)
WHERE subways.name = 'Broad St';

Notice the ST_Transform(neighborhoods.geom, 26918) in the join. Its returns the geometry in the matching SRID and then executes the JOIN.