79186601

Date: 2024-11-13 20:46:59
Score: 1
Natty:
Report link

I've had the same issue. It turned out that my function variable names were the same as column names in the table (i.e. lat and lon), and ST_Distance was using the row's values rather than values passed in to the function. This meant it was calculating the distance from itself, which was 0 in each case, so returning the first rows of the table.

Your function has variables called lat and lon too, and you mention you have a "db of city geocode value like lat: 55.8652, lon: -4.2514" so I suspect your column names are also lat and lon, in which case this would be the same issue.

The simple solution was to rename the function variables so they weren't the same as the column names (e.g. to latitude and longitude), and update the variable names in the select statement accordingly.

Reasons:
  • Long answer (-0.5):
  • No code block (0.5):
  • Low reputation (1):
Posted by: Michael Lewis