6

[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

6

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;
1
  • oh thanks i read it just now and it help me a lot.the error is due to the data When i import the data i forget make SRID Tkanks again. Dec 14 '13 at 2:45
4

@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.

1
  • I'am so happy you give me this suggestion.and I will use it in the future you are a nice person thanks Dec 15 '13 at 4:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.