Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TEMPORARY TABLE ais_data (
  ship_name TEXT,
  lat NUMERIC,
  lon NUMERIC,
  tmstp TIMESTAMP
);

INSERT INTO ais_data VALUES
('foo',1,2,'2021-02-19 12:10:25'),
('foo',1,3,'2021-02-19 12:20:25'),
('foo',1,4,'2021-02-19 12:30:25'),
('bar',1,2,'2021-02-19 12:10:25'),
('bar',1,4,'2021-02-19 12:30:25');

CREATE TEMPORARY TABLE ship_dim (
  ship_id SERIAL PRIMARY KEY,
  ship_name TEXT
);

INSERT INTO ship_dim (ship_name)
SELECT DISTINCT ship_name FROM ais_data;

CREATE TEMPORARY TABLE position_dim (
  position_id SERIAL PRIMARY KEY,
  lon NUMERIC, lat NUMERIC,
  tmstp TIMESTAMP
);

INSERT INTO position_dim (lon,lat,tmstp)
SELECT DISTINCT lon,lat,tmstp 
FROM ais_data;

CREATE TEMPORARY TABLE data_point_fact (
  ship_id INT REFERENCES ship_dim(ship_id),
  position_id INT REFERENCES position_dim(position_id)
);

INSERT INTO data_point_fact 
SELECT s.ship_id,p.position_id FROM ais_data a
JOIN ship_dim s ON s.ship_name = a.ship_name
JOIN position_dim p ON p.lon = a.lon AND p.lat = a.lat AND p.tmstp = a.tmstp;

SELECT * FROM data_point_fact;

5 rows affected

2 rows affected

3 rows affected

5 rows affected

ship_id	position_id
1	1
1	3
2	1
2	2
2	3