0

I perform the following query:

SELECT * FROM "houses" WHERE (ST_Distance(coordinates, 'POINT(-0.374930 39.478400)'::geometry) < 100)

To find houses around 100 meter distance from: 39.478400, -0.374930

I'm getting the following error:

PG::InternalError: ERROR: Operation on mixed SRID geometries

What is wrong here?

"Coordinates" is of type: geometry "coordinates", limit: {:srid=>4326, :type=>"geometry"}

4

The point that is created has no SRID, i.e. 0, which is different than 4326.

That being said, st_distance uses the unit of the CRS, so degrees in this case.

One solution is to use geography instead of geometry, as the unit is meters, and to use st_dwithin() instead of st_distance as it makes use of spatial index. Eventually, you will want to add an index on the geography transformation.

SELECT * FROM "houses" 
WHERE st_dwithin(coordinates::geography, 'POINT(-0.374930 39.478400)'::geography) , 100);
1

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.