Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table  resorts ( id        integer  generated always as identity
                                           primary key 
                      , resort    text
                      , latitude  float
                      , longitude float
                     ) ;
  
insert into  resorts ( resort, latitude, longitude) 
       values ('Hemsedal', 60.9282437, 8.38348693); 
   
select * from resorts;

CREATE TABLE



INSERT 0 1




id
resort
latitude
longitude


1
Hemsedal
60.9282437
8.38348693



SELECT 1


create table snows ( id         integer  generated always as identity
                                         primary key
                    , snowed_on date 
                    , latitude  float
                    , longitude float
                    ); 
                
 insert into snows ( snowed_on , latitude, longitude) 
       values ( date '2022-12-01', 63.125, 68.875)
            , ( date '2022-12-01', 60.875, 8.125);                
 
select * 
  from snows;

CREATE TABLE



INSERT 0 2




id
snowed_on
latitude
longitude


1
2022-12-01
63.125
68.875

2
2022-12-01
60.875
8.125



SELECT 2


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);


id
resort
r_loc
id
dt
s_loc
distance


1
Hemsedal
0101000020E61000003E961D6658C42040E91B87B0D0764E40
2
2022-12-01
0101000020E610000000000000004020400000000000704E40
15232.18193448

1
Hemsedal
0101000020E61000003E961D6658C42040E91B87B0D0764E40
1
2022-12-01
0101000020E610000000000000003851400000000000904F40
3058275.13213405



SELECT 2


-- Define Longitude/Latitude as a geographic point

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

insert into resorts_g (resort, loc) 
     select resort,  ST_SetSRID(ST_MakePoint(longitude, latitude),4326)  
       from resorts;
    
select * 
  from resorts_g;

create table snows_g( id       integer  generated always as identity
                                        primary key 
                   , snowed_on date
                   , loc       geography
                   ); 
insert into snows_g (snowed_on, loc) 
     select snowed_on, ST_SetSRID(ST_MakePoint(longitude, latitude),4326) 
       from snows; 

select * 
  from snows_g;

CREATE TABLE



INSERT 0 1




id
resort
loc


1
Hemsedal
0101000020E61000003E961D6658C42040E91B87B0D0764E40



SELECT 1



CREATE TABLE



INSERT 0 2




id
snowed_on
loc


1
2022-12-01
0101000020E610000000000000003851400000000000904F40

2
2022-12-01
0101000020E610000000000000004020400000000000704E40



SELECT 2


select *, ST_Distance(r.loc, s.loc) distance 
  from resorts_g  r 
 cross join snows_g s
 order by ST_Distance(r.loc, s.loc);


id
resort
loc
id
snowed_on
loc
distance


1
Hemsedal
0101000020E61000003E961D6658C42040E91B87B0D0764E40
2
2022-12-01
0101000020E610000000000000004020400000000000704E40
15232.18193448

1
Hemsedal
0101000020E61000003E961D6658C42040E91B87B0D0764E40
1
2022-12-01
0101000020E610000000000000003851400000000000904F40
3058275.13213405



SELECT 2


 -- get langitude, latitude as seperate columns
 select id, resort
     , ST_Y(loc::geometry)  latitude 
     , ST_X(loc::geometry)  longitude 
 FROM resorts_g;


id
resort
latitude
longitude


1
Hemsedal
60.9282437
8.38348693



SELECT 1


-- if you need the actual coordinated cast Geography to Geometry)
select id 
     , snowed_on
     , loc::geometry::point  "Longitude/Latitude"
  from snows_g
 order by id


id
snowed_on
Longitude/Latitude


1
2022-12-01
(68.875,63.125)

2
2022-12-01
(8.125,60.875)



SELECT 2
id	resort	r_loc	id	dt	s_loc	distance
1	Hemsedal	0101000020E61000003E961D6658C42040E91B87B0D0764E40	2	2022-12-01	0101000020E610000000000000004020400000000000704E40	15232.18193448
1	Hemsedal	0101000020E61000003E961D6658C42040E91B87B0D0764E40	1	2022-12-01	0101000020E610000000000000003851400000000000904F40	3058275.13213405