1

I know that it might be dumb question, but I'm searching for some time and can't find proper answer. I have PostgreSQL database with PostGIS installed. In one table I have entries with lon lat (let's assume that columns are place, lon, lat).

What should I add to this table or/and what procedure I can use, to be able to count distance between those places in meters.

I've read that it is necessary to know SRID of a place to be able to count distance. Is it possible to not know/use it and still be able to count distance in meters basing only on lon lat?

chamber5
  • 195
  • 2
  • 8

1 Answers1

0

Short answer:

Just convert your x,y values on the fly using ST_MakePoint (mind the overhead!) and calculate the distance from a given point, the default SRS will be WGS84:

SELECT ST_Distance(ST_MakePoint(lon,lat)::GEOGRAPHY,
                   ST_MakePoint(23.73,37.99)::GEOGRAPHY) FROM places;

Using GEOGRAPHY you will get the result in meters, while using GEOMETRY will give it in degrees. Of course, knowing the SRS of coordinate pairs is imperative for calculating distances, but if you have control of the data quality and the coordinates are consistent (in this case, omitting the SRS), there is not much to worry about. It will start getting tricky if you're planing to perform operations using external data, from which you're also unaware of the SRS and it might differ from yours.

Long answer:

Well, if you're using PostGIS you shouldn't be using x,y in separated columns in the first place. You can easily add a geometry / geography column doing something like this.

This is your table ...

CREATE TABLE places (place TEXT, lon NUMERIC, lat NUMERIC);

Containing the following data ..

INSERT INTO places VALUES ('Budva',18.84,42.92),
                          ('Ohrid',20.80,41.14);

Here is how you add a geography type column:

ALTER TABLE places ADD COLUMN geo GEOGRAPHY;

Once your column is added, this is how you convert your coordinates to geography / geometry and update your table:

UPDATE places SET geo = ST_MakePoint(lon,lat);

To compute the distance you just need to use the function ST_Distance, as follows (distance in meters):

SELECT ST_Distance(geo,ST_MakePoint(23.73,37.99)) FROM places;

   st_distance   
-----------------
 686560.16822422
 430876.07368955
(2 Zeilen)

If you have your location parameter in WKT, you can also use:

SELECT ST_Distance(geo,'POINT(23.73 37.99)') FROM places;
   st_distance   
-----------------
 686560.16822422
 430876.07368955
(2 Zeilen)
Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • Thanks @Jim ! That works, I could calculate distance, with some accuracy. But it is showing different results, than measuring distance in i.e. google maps. `Select ST_DISTANCE(ST_MakePoint(51.501428, -0.191247)::GEOGRAPHY, ST_MakePoint(51.506544, -0.189391)::GEOGRAPHY);` Result is 605m, but when measuring with google maps it shows me 652m. Which one is correct and if mine is wrong, how can I fix it? – chamber5 Sep 21 '18 at 20:15
  • 1
    @chamber5 the `geography` type will implicitly use *spheroidal* algebra (based on the WGS84 elipsoid, implementing Vincenty's formulae for distance); this is possibly the most precise measurement you will get. not 100% sure what *exactly* Google calculates; it either does the mammoth task and takes *elevation* into account (very unlikely), is based on *spherical* algebra (possibly), or based on their *Web Mercator* projection, which is not fit for *anything* distance related (likely, with that difference on that scale). btw., make sure you are spatially indexing your geometry/geography column! – geozelot Sep 23 '18 at 14:41
  • @chamber5 the comment after yours makes a very good point. I would also take into account that this distance is computed in a form of "direct line", while google maps might be using the road network (here I'm just speculating ... never used google maps for GIS operations). Long story short: in PostGIS you won't get any preciser than that. I hope this helps you further.. cheers! – Jim Jones Sep 24 '18 at 07:17