3

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;
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
delkov
  • 185
  • 1
  • 1
  • 6

1 Answers1

2

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.

Dr G.
  • 103
  • 3
Nicklas Avén
  • 13,241
  • 1
  • 39
  • 48