CODEWITHSUNDEEP
postgresqlgispostgis
91DarioDev
91DarioDev

Reputation: 1690

postgis: ST_Distance between two geography. Syntax error

i am trying to running this on my table

select ST_Distance (
    select location from utenti where user_id=464,
    select location from utenti where user_id=474604
);

having a location column of this type location geography(POINT, 4326)

i am getting a syntax error and i'm not understanding why.

how can i achieve my goal?

for example if i select that column in two queries for each users i get a data like this "0101000020E61000001435F98F528125402AE5B512BAA34540"

and running:

select ST_Distance(%s, %s);

it works but the distance doesn't seem to be true. hm

Upvotes: 1

Views: 643

Answers (2)

tayfun Kılıç
tayfun Kılıç

Reputation: 2834

İf you polygon to point distance select st_distance( st_geomfromtext('POINT (0 0)'), st_geomfromtext('POLYGON ((1 1, 2 1, 2 2, 1 2, 1 1))') )

same base spatial geom column for

select st_distance(
    st_geomfromtext(c.geom), 
    st_geomfromtext(b.geom)
) from  city c , build b where b.c_ID=c.ID

Upvotes: 0

Ian Turton
Ian Turton

Reputation: 10976

As pointed out in the comments above you can rewrite the query as:

SELECT ST_Distance(a.geom, b.geom) FROM utenti a, utenti b WHERE a.user_id=464 AND b.user_id=474604;

BUT this will give you the distance in degrees (as that is what your points are stored as). So you will want to change your function to be:

SELECT ST_Distance_Sphere(a.geom, b.geom) FROM utenti a, utenti b WHERE a.user_id=464 AND b.user_id=474604;

ST_Distance_sphere will take some of the curvature of the Earth in to account and will return a distance in metres. If you require absolute accuracy and are not worried about speed you can account for all of the Earth's curvature by using st_distance_spheroid.

Upvotes: 2

Related Questions