Object in 100 m distance, Error: Operation on mixed SRID geometries

Question

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"}

Answer 1

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);