2

I have 2 points, look like: (lat,lon,altitude in meters). How can I get the distance between them?

I saw only ST_3DDistance(geometry g1, geometry g2);

But I don't know how to set points coordinates right way.. Because I have GPS + altitude also.

As suggested by Niklas

WITH geom_1 AS (
    SELECT ST_Transform(ST_SetSRID(ST_MakePoint(Lon, Lat), 4326),32637) 
    ), point_3d AS (
    SELECT  ST_MakePoint(ST_X(st_transform),ST_Y(st_transform), 0) FROM geom_1
    ), static_points AS (SELECT geom FROM eco.static_points WHERE name='Moscow')
SELECT ST_3DDistance(st_makepoint, geom) FROM point_3d, static_points;
0

For ST_3DDistance you have to have all dimensions in the same unit, or the answer will not make sense. So use ST_Transform to project your GPS points to a suitable meter based (if altitudes are in meters) projection for the area of the points.

edit In Moscow you can use UTM37, which is SRID 32637

So to transform your data use

ST_Transform(geom, 32637)

Then you have to make the altitude the z-value of the geometries. Since you have points it is easy. You can do something like:

-- If you have a table like this:

the_table
(
    lat double precision,
    lon double precision,
    altitude double precision
);

-- First create a PostGIS geometry from lat, lon 
--(I might be wrong about lat, lon, or lon, lat order, I never do this)
CREATE TABLE table_a AS
SELECT ST_SetSRID(ST_MakePoint(lat, lon), 4326) geom, altitude FROM the_table;

--Then transform (project) the data
CREATE TABLE table_b AS    
SELECT ST_Transform(geom) geom, altitude FROM table_a;

-- And create a table with 3D points
CREATE TABLE points_3d
SELECT ST_MakePoint(ST_X(geom), ST_Y(geom), altitude) geom FROM table_b

Now you have PostGIS 3D geometries that you can use with ST_3DDistance.

You can of course put the queries above in one single query.

  • I see, can you suggest which transformation can I use for getting cartesian coordinates from my set (lat, lon, alt). Thank you. – delkov Aug 14 '17 at 6:37
  • That depends on where in the world your points are. – Nicklas Avén Aug 14 '17 at 16:01
  • My geolocation is Moscow city. – delkov Aug 15 '17 at 1:44
  • Thank you so much! I've tried to make single query but no success.. All I finally need is only distance. If you write a query I will appreciate it very much – delkov Aug 15 '17 at 9:08
  • I did it! Please give some comments if there is something to improve. – delkov Aug 15 '17 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.