Using ST_Distance in a table

Question

I have a table with id, x ,y value ,I need to find the distance between two points using a function in posgresql.

    CREATE OR REPLACE FUNCTION distances(lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS float AS $dist$
    BEGIN
        SELECT ST_Distance(
  ST_GeogFromText('SRID=4326;POINT(lat1 lon1 )')
, ST_GeogFromText('SRID=4326;POINT(lat2 lon2)'), false);
    END;
$dist$ LANGUAGE plpgsql;

But error throws out when i pass the value:

Kindly give me a sloution.

Jim Jones · Accepted Answer · 2020-06-16T08:31:10.440

There are a few things:

You were trying to concatenate a variable in a string without a ||
The right order is lon lat, not lat lon
You do not need a function for this. A simple query with the ST_Distance function would suffice

But in case you need it for other purposes:

CREATE OR REPLACE FUNCTION distances(lat1 float, lon1 float, lat2 float, lon2 float)
RETURNS float AS $$
BEGIN
  RETURN (
  SELECT 
    ST_Distance(
      ST_SetSRID(ST_MakePoint(lon1,lat1),4326),
      ST_SetSRID(ST_MakePoint(lon2,lat2),4326), false));
END;
$$ LANGUAGE plpgsql;

Otherwise this SQL query would do:

SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(51.23,8.83),4326),
  ST_SetSRID(ST_MakePoint(51.24,8.55),4326), false);

Cheers

Using ST_Distance in a table

1 Answers1