Search code examples
postgresql

Postgres Function on PostGIS does not return the same rows with SQL Queries

I have a db of city geocode value like lat: 55.8652, lon: -4.2514. When I run the SQL on Supabase SQL Editor, then I get the values that I want.

SELECT *
FROM education
WHERE ST_Distance (education.loc, ST_SetSRID (ST_MakePoint ( -4.2514, 55.8652), 4326)) <= 8000
order by
ST_Distance (
education.loc,
ST_SetSRID (ST_MakePoint ( -4.2514, 55.8652), 4326)
)
LIMIT 10;

However, as soon as I try to use a function to call it on my Flutter App, it never returns the value that I want. It was just returning the 10 rows from the top of my education table.

create
or replace function "education" ( lat float, lon float) returns setof education language sql as $$
SELECT *
FROM education
WHERE ST_Distance (education.loc, ST_SetSRID (ST_MakePoint (lon, lat), 4326)) <= 8000
order by
ST_Distance (
education.loc,
ST_SetSRID (ST_MakePoint (lon, lat), 4326)
)
LIMIT 10;
$$;

I checked the function and it looks like lat and lon's types are set to double precision. I think this is the problem. How can I use lat: 55.8652, lon: -4.2514 this format on Postgres Function? I'd tried to set them as numeric and then add ::float to ST_MakePoint. But, it does not work and I am quite lost right now!

--- changes

  const a = await supabase.rpc("education_search", {
    page: 0,
    lat: 55.8652,
    lon: -4.2514,
  });

  console.log(a);
Solution

  • 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.