Search code examples
sqlpostgresqlpostgisplpgsqlshapefile

Using ST_Distance in a table

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: enter image description here

Kindly give me a sloution.

Solution

  • 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

    Further reading: