I
I
Ivan2021-05-08 11:00:08
PostgreSQL
Ivan, 2021-05-08 11:00:08

How to correctly store coordinates in postgresql and then subtract the distance from the user to a specific object?

I found the Postgis extension , added the position

column in the database and overwrote all the coordinates:

UPDATE product_locations SET
  "position" = ST_SetSRID(ST_MakePoint(longitude, latitude),4326);


but when I make a fetch request:
SELECT
  *,
  ST_Distance ( position::geography, ST_GeographyFromText('SRID=4326;POINT(40.5774008 -73.9647576)') )  / 1000 AS distance
FROM
  product_locations 
ORDER BY 
id asc


I get not quite accurate results, see the entry with id 31 (latitude, longitude) are the same as in the request, i.e. for entry 31 , the distance value should be 0 , I don’t
know where to dig
60964487b6c18109799662.png

Answer the question

In order to leave comments, you need to log in

1 answer(s)
N
nozzy, 2021-05-08
@vhuk1802

point(longitude, latitude)
but you seem to be the opposite

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question