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

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

Thank you and Happy Christmas

CodePudding user response：

The geometry literal 'POINT(-0.374930 39.478400)'::geometry has no SRID while the geometries in houses.coordinates have an SRID of 4326. You need to pass a geometry with the same SRID, i.e. using ST_GeomFromText:

...
WHERE ST_Distance(coordinates, ST_GeomFromText('POINT(-0.374930 39.478400)', 4326)) < 100

But keep in mind, that the distance is returned in units of the SRID, which are degrees for SRID 4326 not meters. To retrieve the distance in meters you could use ST_DistanceSphere.