3

I'm new to Spatial Databases and I'm trying to find the nearest point of a point.

In my application there is a google map and the user can click anywhere on it. The click executes a request to the server which should return the nearest point in the database from the point where the user clicked.

My query looks like this:

SELECT point, strength
FROM signalstrengths 
ORDER BY st_distance(st_makepoint(:lng, :lat)::geography, point) DESC
LIMIT 1;

This query always returns one of two points, no matter where i click, although I have around 2k records in the database.

What is wrong with my query?

  • 1
    shouldnt it be asc if you trying to find the closest point? – ziggy Aug 17 '17 at 15:15
  • ST_ClosestPoint can be also used (depending on your postgis version) postgis.net/docs/ST_ClosestPoint.html – Mapperz Aug 17 '17 at 15:38
  • 2
    @Mapperz not really, ST_ClosestPoint finds the nearest point on a plane, to another point, such that ST_ClosestPoint(point1, point2) will always return point1 because there is no other point to choose from. I would prefer it be called ST_ClosestPlanarPoint but you know.. =( – Evan Carroll Aug 17 '17 at 15:45
  • @ziggy yes you are right thanks, don't know what I've been thinking – Timo Jokinen Aug 17 '17 at 16:21
7

As @ziggy said in the comments,

"shouldnt it be asc if you trying to find the closest point?"

Your code is not correct (reproduced below),

SELECT point, strength
FROM signalstrengths 
ORDER BY st_distance(st_makepoint(:lng, :lat)::geography, point) DESC
LIMIT 1;

What you want is,

SELECT point, strength
FROM signalstrengths 
ORDER BY st_distance(st_makepoint(:lng, :lat)::geography, point)
LIMIT 1;

Notice no DESC so ASC is implied. That said, even that is horrible, what you really want is KNN. You don't care about the distance, you just want the nearest point. Do it the fast way with <->.

SELECT point, strength
FROM signalstrengths 
ORDER BY point <-> st_makepoint(:lng, :lat)::geography
LIMIT 1;

KNN will use a GIST index on signalstrengths(point) if available.

CREATE INDEX ON signalstrengths USING gist(point);

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.