closest bus stop in respect to each school.

closest_bus_school_pairs.sql

-- notes:
--	Use a CROSS JOIN when you need a simple Cartesian product without any dependency between the rows of the joined tables.
--	Use a CROSS JOIN LATERAL when the second table's rows depend on values from the first table's current row, allowing for 
--	   dynamic and row-specific computations during the join process. This is powerful for queries requiring a contextual 
--	   relationship between the data sets. Lateral means "to the side"
--

-- using CROSS JOIN and subquery

SELECT * FROM ( 
    SELECT 
        s.id as school_id, 
        s.object_nom, 
        sp.id as spirka_id,  
        sp.kod_spirka, 
        ST_Distance(s.geom, sp.geom) AS distance_meters,
        ROW_NUMBER() OVER (PARTITION BY s.id 
                           ORDER BY ST_Distance(s.geom, sp.geom)) AS row_number
    FROM poi_schools s
    CROSS JOIN mgt_spirki_2020 sp
) subquery
WHERE row_number = 1 and school_id in (143,168,166,158,209);

--- a more compact way to state the above

SELECT DISTINCT
    s.id as school_id,
    FIRST_VALUE(sp.id) OVER w as spirka_id,
    FIRST_VALUE(ST_Distance(s.geom, sp.geom)) OVER w as distance_meters
FROM 
    poi_schools s
    CROSS JOIN mgt_spirki_2020 sp
WHERE
    s.id IN (143, 168, 166, 158, 209)
WINDOW w AS (PARTITION BY s.id ORDER BY ST_Distance(s.geom, sp.geom));
   
-- fastests with LATERAL join
-- supported by most modern version of DBs

SELECT
    s.id AS school_id,
    dcbst.dist as distance,
    dcbst.id as bus_id
FROM
    poi_schools s
cross join lateral
    (
        SELECT
           s.geom <-> b.geom as dist,
            b.id
        FROM
            mgt_spirki_2020 b
        ORDER BY
            s.geom <-> b.geom
        LIMIT 1
    ) AS dcbst    
where 
	s.id in (143, 168, 166, 158, 209);

-- with CTE - common table expressions

WITH nearest_bus_stops AS (
    SELECT
        s.id AS school_id,
        bs.kod_spirka AS bus_stop_code,
        ST_Distance(s.geom, bs.geom) dist, 
        ROW_NUMBER() OVER (PARTITION BY s.id ORDER BY ST_Distance(s.geom, bs.geom)) AS rn
    FROM
        poi_schools s, mgt_spirki_2020 bs 
    where s.id in (143, 168, 166, 158, 209)
)
SELECT
    school_id,
    bus_stop_code,
    dist
FROM
    nearest_bus_stops
WHERE
    rn = 1 
  
-- partial solution for only distance

select
	poi_schools.id,
	min(st_distance(poi_schools.geom, mgt_spirki_2020.geom))
from
	poi_schools,
	mgt_spirki_2020
where poi_schools.id in (143, 168, 166,158, 209)
group by
	poi_schools.id