I have a table called DE
(let say it's a table of individuals) with multiple fields, two of them are latitude (lat
field) and longitude (long
field).
I also have another point with latitude and longitude (it's unique point I pass as arguments into the function).
I'm trying to get all the DE
records sorted based on the minimum distance between this last point and the DE point.
I've looked at stackoverflow and here is what I got so far, this is full of mistakes but if you had any idea how to achieve that?
create or replace function get_sorted_de(lat float, long float)
returns setof "DE" as $$
BEGIN
with distances as (
select *,point(DE.lat,DE.long)<->point(lat, long) distance from DE
)
return query ( select * from distances
order by distance LIMIT 20 );
END;
$$ language plpgsql;
If those are real-world coordinates, you should be all means use the PostGIS extension, so that you get accurate results. After all, the world is not an Euclidean plane.
If you want to use PostgreSQL's geometry types and want to use a function, try
CREATE FUNCTION get_sorted_de(
long double precision,
lat double precision
) RETURNS SETOF "DE"
BEGIN ATOMIC
SELECT *
FROM "DE"
ORDER BY point("DE".long, "DE".lat) <->
point(get_sorted_de.long, get_sorted_de.lat)
LIMIT 20;
END;
To make that fast, you can use a GiST index:
CREATE INDEX ON "DE" USING gist (point(long, lat));
Hint: avoid naming function parameters with the same name as table columns. It leads to pain and suffering.