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

Padmanabha
  • 1,384
  • 3
  • 9
  • 19
John Smith
  • 113
  • 4

1 Answers1

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);
JGH
  • 41,794
  • 3
  • 43
  • 89
  • 1
    ST_DWithin only takes advantage of the index if a covering index is present. CREATE INDEX houses_gpx ON houses USING gist(Geography(coordinates)) see https://gis.stackexchange.com/questions/247113/setting-up-indexes-for-postgis-distance-queries – Vince Dec 23 '21 at 15:54