CODEWITHSUNDEEP
sqlpostgresqlgis
ParveenArora
ParveenArora

Reputation: 751

How to find distance between two columns in different tables in geom datatype

Update the value in the column "distance" of table 1 by finding the distance between center_geom from table2 and home_location from table 3.

Table 1

obu_id     end_location     trip_id   end_loc_adj    distance

1          51               1234     
2          57               1357      44
2          63               1351
3          21               1212      20
3          23               4313

Table 2

id        obu_id          center_geom 
int       int             geom

51        2               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
52        3               "0101000020ED080000AE47E17A35F73341FE65F764723C0841"
57        3               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
21        4               "0101000020ED080000B81E852BC555334186048C9EB1C21141"
  1. Now, see table 1: If we have any value in end_loc_adj then the column : end_loc_adj has to be used rather end_loc Hint: use if-else.
  2. Column name "end_loc" in table 1 has same values as column "id" in table 2.

Table 3

hhid           obu_id          home_location
(character     Int             geometry 
varying)       
11             1               "0101000020ED08000082E2C7A0B2413341BC5818A21F000941"
15             2               "0101000020ED080000B81E852BC555334186048C9EB1C21141"
17             3               "0101000020ED0800006DFFCAA2A2553341B20E4717774E0C41"
17             4               "0101000020ED080000B81E852BC555334186048C9EB1C21141"
22             7               "0101000020ED080000AE47E17A35F73341FE65F764723C0841"

I have tried the following:

 select ST_Distance_Sphere ( ST_AsText(cast(v.house_location AS text)) , ST_AsText(cast(l.center AS text)))

from public.locations l, clean.vehicles v where l.obu_id=v.obu_id and l.obu_id=3

but this works manually for single obu_id

I want to complete it in one go using a function. Please give me some idea about how to do this.

Thanks.

Upvotes: 2

Views: 487

Answers (1)

tx-911
tx-911

Reputation: 447

house_loc:=(select house_location from table1 where obu_id=b.obu_id);

house_loc_geom := ST_Transform(house_loc,4269);
IF b.end_location_adj IS null THEN
    end_loc:= (select center from table2 where id=b.end_location and obu_id= b.obu_id);
else 
    end_loc:= (select center from tabl2 where id = b.end_location_adj and obu_id = b.obu_id);
end if;

center_geom:=ST_Transform(end_loc,4269);


UPDATE table 1 set dist_from_home_in_meter=distance 
where obu_id=b.obu_id and trip_id=b.trip_id and end_location=b.end_location;

this is close to answer, need some modifications.

Upvotes: 1

Related Questions