Postgis convert long lat from 2 different locations into line and calculate the distance in meters

Question

The name of my table is odk_duct.duct_monitoring1_core and the select statement below is what i want to see the distance. The table has 2 longitudes and 2 latitudes.

select 
"_URI" as id,
"_SUBMISSION_DATE" as date,
initcap("INFO_TEAM_NAME") as team_name,
initcap("INFO_SUPERVISOR_NAME") as supervisor_name,
"INFO_LOCATION" as location,
"RPT_FTR_POINT1_LAT" as first_point_lat,
"RPT_FTR_POINT1_LNG" as first_point_long,
"RPT_FTR_POINT2_LAT" as second_point_lat,
"RPT_FTR_POINT2_LNG" second_point_long,
"RPT_FTR_WIDTH" as width_cleared,
formid 
FROM odk_duct.duct_monitoring1_core 

I want to know the distance between point 1 and point 2 in meters using PostGIS query.

Answer 1

Something like this should work:

SELECT 
"_URI" AS id, 
"_SUBMISSION_DATE" AS date, 
initcap("INFO_TEAM_NAME") AS team_name, 
initcap("INFO_SUPERVISOR_NAME") AS supervisor_name, 
"INFO_LOCATION" AS location, 
"RPT_FTR_POINT1_LAT" AS first_point_lat, 
"RPT_FTR_POINT1_LNG" AS first_point_long, 
"RPT_FTR_POINT2_LAT" AS second_point_lat, 
"RPT_FTR_POINT2_LNG" AS second_point_long, 
"RPT_FTR_WIDTH" AS width_cleared, 
formid,
ST_Distance(
ST_SetSrid(ST_Point("RPT_FTR_POINT1_LNG", "RPT_FTR_POINT1_LAT"),4326)::geography,
ST_SetSrid(ST_Point("RPT_FTR_POINT2_LNG",  "RPT_FTR_POINT2_LAT"),4326)::geography
) AS dist
FROM odk_duct.duct_monitoring1_core;

Notice the cast to geography data type to get the result in meters.

Without the cast you will get the answer in degrees which will make no sense.

About your case with many points in one column you need to find a way t pick them apart. There are several ways with the different string functions in PostgreSQL: https://www.postgresql.org/docs/9.6/static/functions-string.html

Here is one way of doing it. There are other ways that may look simplier, but this one should be a good start for stable queries handling cases like this.

with a as 
(
    SELECT "_URI", "_SUBMISSION_DATE", "RPT_FTR_TRACE" as t 
    FROM odk_duct.duct_monitoring_core
)
,b as 
(
    select * from 
    a,
    regexp_split_to_table(t,';') with ordinality as b(p, id) 
    where b.p != ''
)
,c as 
(
    select 
    "_URI", 
    "_SUBMISSION_DATE"
    id, 
    ST_SetSrid(ST_Point(coord_array[2]::double precision, coord_array[1]::double precision),4326)::geography point 
    from 
    b,
    regexp_split_to_array(p,' ') as coord_array 
)
select c1."_URI", c1."_SUBMISSION_DATE", c1.point p1, c2.point p2, ST_Distance(c1.point, c2.point) dist 
from c c1, c c2 
where c1.id > c2.id and c1."_URI" = c2."_URI" and c1.c1."_SUBMISSION_DATE"=c2."_SUBMISSION_DATE";

I might have switched lat lon for lon lat so you have to check that