CODEWITHSUNDEEP
postgresqlpostgis
Mahmoud M. Abdel-Fattah
Mahmoud M. Abdel-Fattah

Reputation: 1527

operator does not exist: geography <-> geography

I've postgresql column with type 

geography(Point,4326)

And I've inserted some rows to it using

POINT(LONG LAT)

That data have been inserted successfully and I can retrieve it with no problems, now I want to get the nearest entries to a specific point using the following query

SELECT "cafes".* FROM "cafes" ORDER BY "latlng" <-> (SELECT latlng FROM cafes WHERE id = '3') LIMIT 1

But I'm getting the following error

ERROR:  operator does not exist: geography <-> geography
LINE 1: ...es".* FROM "cafes" ORDER BY "latlng" <-> (SELEC...
                                                         ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

Upvotes: 1

Views: 1122

Answers (1)

Patrick
Patrick

Reputation: 32336

The <-> "distance between" operator applies to PostgreSQL geometric data types, not to the PostGIS geography data type. With the geography data type you can use the PostGIS function ST_Distance() and find the minimum value.

WITH this_cafe (latlng) AS (
  SELECT latlng FROM cafes WHERE id = '3'
)
SELECT cafes.*, ST_Distance(cafes.latlng, this_cafe.latlng, 'false') AS dist
FROM cafes, this_cafe
ORDER BY dist ASC
LIMIT 1

Note that the third argument useSpheroid to the function is set to false which will make the function much faster. It is unlikely going to affect the result because cafes tend to lie close to each other.

This assumes that there is only 1 cafe with id = 3. If there could be more, then restrict the CTE to return just 1 row.

Upvotes: 2

Related Questions