AdvancedTopic: phase5.sql

CREATE EXTENSION IF NOT EXISTS postgis;

ALTER TABLE coordinates
ADD COLUMN geom GEOGRAPHY(Point, 4326);

UPDATE coordinates
SET geom = ST_SetSRID(
    ST_MakePoint(longitude, latitude),
    4326
)::geography;

CREATE INDEX idx_coordinates_geom
ON coordinates
USING GIST (geom);

--query1
SELECT
    h.name AS hotel,
    a.name AS attraction,
    ROUND(
        ST_Distance(hc.geom, ac.geom)
    ) AS distance_meters
FROM hotels h
JOIN locations hl
    ON h.location_id = hl.id
JOIN coordinates hc
    ON hl.coordinate_id = hc.id

JOIN facultatives f
    ON TRUE

JOIN attractions a
    ON a.facultative_id = f.id

JOIN locations al
    ON f.location_id = al.id

JOIN coordinates ac
    ON al.coordinate_id = ac.id

WHERE h.id = 1
AND ST_DWithin(
        hc.geom,
        ac.geom,
        5000
    )
ORDER BY distance_meters;


--query 2
SELECT DISTINCT ON (h.id)
    h.id,
    h.name AS hotel,
    ap.name AS nearest_airport,
    ap.code,
    ROUND(
        (ST_Distance(hc.geom, ac.geom) / 1000)::numeric,
        2
    ) AS distance_km
FROM hotels h

JOIN locations hl
    ON h.location_id = hl.id
JOIN coordinates hc
    ON hl.coordinate_id = hc.id

CROSS JOIN airports ap

JOIN locations al
    ON ap.location_id = al.id
JOIN coordinates ac
    ON al.coordinate_id = ac.id

ORDER BY
    h.id,
    ST_Distance(hc.geom, ac.geom);


--query3
SELECT DISTINCT ON (h.id)
    h.name AS hotel,
    ts.name AS nearest_transport_station,
    ts.station_type,
    ROUND(
        (ST_Distance(hc.geom, tc.geom))::numeric,
        2
    ) AS distance_meters
FROM hotels h

JOIN locations hl
    ON h.location_id = hl.id
JOIN coordinates hc
    ON hl.coordinate_id = hc.id

CROSS JOIN transport_stations ts

JOIN locations tl
    ON ts.location_id = tl.id
JOIN coordinates tc
    ON tl.coordinate_id = tc.id

ORDER BY
    h.id,
    ST_Distance(hc.geom, tc.geom);