function st_distancespheroid(geometry, geometry) does not exist

Question

I have installed postgis, I still have problem:

function st_distancespheroid(geometry, geometry) does not exist

    WITH data AS (
    SELECT a.*,
    CAST(ST_DistanceSpheroid(geometry(location), st_geomfromtext('POINT(' || $7::decimal || ' ' || $8::decimal || ')', 4326))as numeric) / 1000 AS distance
    FROM agent AS a
    WHERE (a.agent_code ILIKE $1
    OR a.name ILIKE $1
    OR a.phone LIKE $1)
    AND
    a.sub_district_name LIKE ANY(string_to_array($4, ','))
    AND
    a.agent_status_id LIKE ANY(string_to_array($5, ','))
    AND CASE WHEN $6 = 'FAVORITE' THEN
    a.is_subscription = true
    WHEN $6 = 'REGULER' THEN
    a.is_subscription != true
    ELSE
    a.location LIKE '%%'
    END
    ORDER BY distance ASC
),
data_counter AS (
    SELECT COUNT(agent_id) AS __total__
    FROM data
)
SELECT * 
FROM data, data_counter
LIMIT $2
OFFSET $3
`

when I run my go repo

Answer 1

How to install PostGIS

After installation you have to create the extension in the database. For instance, to install PostGIS 3.0 in a Debian distribution running PostgreSQL 13:

apt-get install postgresql-13-postgis-3

After that you create the extension in the database

CREATE EXTENSION postgis;

Now you'll be able to use the spatial functions.

How to calculate distances over a spheroid / sphere

The function ST_DistanceSpheroid expects three parameters, namely two geometries and the shperoid. In your code you're calling the function using only two geometries, hence the error function st_distancespheroid(geometry, geometry) does not exist. This is how it should be called (adapt it to the spheroid of your choice):

SELECT
  ST_DistanceSpheroid(geom1,geom2,'SPHEROID["WGS 84",6378137,298.257223563]')
FROM t;

You can also tell ST_Distance to compute the distances using the spheroid if you cast the geometry parameters to geography:

SELECT ST_Distance(geom1::geography,geom2::geography,true) FROM t;

Another option - less accurate - is to use ST_DistanceSphere:

 SELECT ST_DistanceSphere(geom1,geom2) FROM t;