Question 248421 in PostgreSQL Question posted in
The official documentation can be found here.

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);
Tags:

2

Answers


  1. 0

    When using latitude and longitude values in your PostgreSQL function, you can fix problems with data types and precision by making these adjustments.

    Preserve accuracy for latitude and longitude values by converting the data types of the lat and lon parameters in your PostgreSQL function to numeric.

    CREATE OR REPLACE FUNCTION "education" (lat numeric, lon numeric) RETURNS SETOF education LANGUAGE SQL AS $$
-- Rest of your function code
$$;

    Make sure to use.toDouble() to pass the latitude and longitude values as double precision to match the parameter types of your PostgreSQL function when using the PostgreSQL function from your Flutter app. This keeps geographic calculations as precise as possible.

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

console.log(a);

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    You are calling a function with 3 parameters (page, lat, lon) but the function you have shown expects two only. It can’t work.

    –> remove the page parameter.

    While there, it is better to use st_dwithin in the where clause as it can make use of a spatial index.

    Make sure that your education.loc is a geography, not a geometry, else the distance check is done in degrees, not meters, and the distance ordering will be worth close to nothing.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search