1

I have 2 tables, 1st table is the resorts info

Resorts |   Latitude   |   Longitude
Hemsedal     60.9282437   8.38348693

2nd table is my snow info

   Month   |   Latitude   | Longitude
   12/1/2022    63.125       68.875
  12/1/2022     60.875       8.125

The longitude and latitude of both tables are not exact. is there a way that i can join my resorts table with the closest latitude and longitude from the 2nd table?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Zecharia Paras
  • 23
  • 4
  • 2
    Yes, but first of all you have to _define close_. – jarlh Mar 20 '23 at 07:07
  • something like `SELECT * FROM table1 JOIN table2 ON table1.latitude LIKE '6%' AND table2.latitude LIKE '6%';` – k1dr0ck Mar 20 '23 at 07:14
  • @k1dr0ck, LIKE is poor in several ways. Probably wrong data type. Does also pick Latitude 6.333. LIKE '6%' if you have 60.0000 will not find 59.9999. – jarlh Mar 20 '23 at 07:22
  • @jarlh i added another row in my second table which is the closest to resort hemsedal. how can i join those two together? – Zecharia Paras Mar 20 '23 at 07:28
  • Which dbms are you using? Some products have built-in functions for gis/spatial distance. – jarlh Mar 20 '23 at 07:30
  • @jarlh im using postgresql – Zecharia Paras Mar 20 '23 at 07:33
  • 2
    Possibly related: https://stackoverflow.com/q/37827468 – tobifasc Mar 20 '23 at 07:44
  • 1
    try this SELECT r.Resorts, s.Month, s.Latitude, s.Longitude FROM Resorts r CROSS APPLY ( SELECT TOP 1 Month, Latitude, Longitude, 2 * ASIN(SQRT(POWER(SIN((r.Latitude - s.Latitude) * PI()/180 / 2), 2) + COS(r.Latitude * PI()/180) * COS(s.Latitude * PI()/180) * POWER(SIN((r.Longitude - s.Longitude) * PI()/180 / 2), 2))) * 6371 AS Distance FROM SnowInfo s ORDER BY Distance ASC ) s Haversine formula – Temka Mar 20 '23 at 08:06

1 Answers1

0

You can use the Haversine formula for calculating the distance between the locations. However, if you do much with latitude, longitude you would be better off installing PostGis (create extension postgis). It has all the necessary functionality for handling geographic data. In this case:

with r (id, resort, r_loc) as 
       ( select id, resort,  ST_SetSRID(ST_MakePoint(longitude, latitude),4326)::geography
           from resorts 
       )
    , s (id, dt, s_loc) as
        ( select id, snowed_on, ST_SetSRID(ST_MakePoint(longitude, latitude),4326)::geography 
            from snows
        ) 
select *, ST_Distance(r_loc, s_loc) distance 
  from r cross join s
 order by ST_Distance(r_loc, s_loc);

Alternately, you can go a step further and instead of storing longitude and latitude columns and doing the necessary conversion on each query do the conversion once on insertion and store the column as geography data type. So (apply the same for snows table):

create table resorts_g ( id       integer  generated always as identity
                                          primary key 
                       , resort   text
                       , loc      geography
                       );

The query then reduces to just the main query above without the CTEs. If you occasionally need longitude/latitude as separate columns you can extract them individually or together:

select id, resort
     , ST_X(loc::geometry)  longitude
     , ST_Y(loc::geometry)  latitude  
 FROM resorts_g;

See demo here with both versions and then some.

Belayer
  • 13,578
  • 2
  • 11
  • 22