-- informal: closest airports to a location --
-- <start id="code_indexed_airports_geom_geog" > --
CREATE INDEX idx_airports_geom_gist_cast 
 ON ch10.airports USING gist (geometry(geog));
 
CREATE INDEX idx_navaids_geom_gist_cast 
 ON ch10.navaids USING gist (geometry(geog));
-- <end id="code_indexed_airports_geom_geog" > --

-- take 43 ms returns 631 rows within 10,000 km --
-- <start id="code_airports_dwithin" > --
SELECT name, iso_country, iso_region
FROM ch10.airports
WHERE ST_DWithin(geog
  , ST_Point(-75.0664, 40.2003)::geography, 100000);
-- <end id="code_airports_dwithin" > --

-- takes 20-30ms --
-- <start id="code_airports_dwithin_limit" > --
SELECT ident, name
FROM ch10.airports 
 CROSS JOIN (SELECT 
   ST_Point(-75.0664, 40.2003)::geography AS ref_geog) As ref
WHERE ST_DWithin(geog
  , ref_geog, 100000)
ORDER BY ST_Distance(geog, ref_geog)
LIMIT 5;
-- <end id="code_airports_dwithin_limit" > --

-- takes 15-17 seconds to compute for 45,000 airports
-- <start id="code_airports_dwithin_distinct_on" > --
SELECT DISTINCT ON(a.ident) -- <co id="co_code_airports_dwithin_distinct_on_1" /> --
  a.ident, a.name As airport
 , n.name As closest_navaid
 , (ST_Distance(a.geog,n.geog)/1000)::integer As dist_km
FROM ch10.airports As a
 LEFT JOIN ch10.navaids As n 
   ON ST_DWithin(a.geog, n.geog,100000) -- <co id="co_code_airports_dwithin_distinct_on_2" /> -- 
ORDER BY a.ident -- <co id="co_code_airports_dwithin_distinct_on_3" /> -- 
 , dist_km;  -- <co id="co_code_airports_dwithin_distinct_on_4" /> -- 
-- <end id="code_airports_dwithin_distinct_on" > --
--[1] comma separated list of fields that define uniqueness
--[2] max distance to check
--[3] distinct fields repeated
-- [4] prefer closest

-- Finding N closest objects --
-- Section Intersects with tolerance
SELECT 
    ST_DWithin(
        ST_GeomFromText('LINESTRING(1 2, 3 4)'), 
            ST_Point(3.00001,4.000001),
        0.0001
 ) ;
 
-- <start id="code_indexed_dist_search_1" /> --
SELECT loc.pid
  FROM ch10.land AS loc
  WHERE loc.land_type = 'apartment'
  ORDER BY loc.geom <-> ST_Transform(
        ST_SetSRID(ST_Point(-71.09368, 42.35857) -- <co id="co_code_indexed_dist_search_1_1" /> --
         ,4326),26986)  -- <co id="co_code_indexed_dist_search_1_2" /> --
  limit 10;
--<end id="code_indexed_dist_search_1" /> --
-- [1] express location in wgs 84 lon lat (4326)
-- [2] transform to mass stateplane meters (26986)

-- <start id="code_indexed_dist_search_2" /> --
SELECT loc.pid
  FROM ch10.land AS loc
  WHERE loc.land_type = 'apartment'
  ORDER BY loc.geom 
    <-> (SELECT l.geom FROM ch10.land AS l 
          WHERE l.pid = '58-162')
  limit 10;
--<end id="code_indexed_dist_search_2" /> --

-- <start id="code_indexed_dist_search_3" /> --
SELECT loc.pid
        , (SELECT sh.pid
    FROM ch10.land As sh 
      WHERE sh.land_type = 'shopping'
     ORDER BY sh.geom <-> loc.geom LIMIT 1) As closest_shopping
 from ch10.land AS loc;
-- <end id="code_indexed_dist_search_3" /> --

-- <start id="code_indexed_dist_search_4" /> --
SELECT loc.pid, sh.pid As n_closest_shopping
FROM ch10.land As loc CROSS JOIN LATERAL 
(SELECT  pid FROM ch10.land As b 
   WHERE b.land_type = 'shopping'
   ORDER BY b.geom <-> loc.geom -- <co id="co_code_indexed_dist_search_4_1" /> --
   LIMIT 3  ) As sh; -- <co id="co_code_indexed_dist_search_4_2" /> -- 
-- <end id="code_indexed_dist_search_4" /> --


-- <start id="code_indexed_dist_search_dist_1" /> --
WITH first_hop AS ( -- <co id="code_indexed_dist_search_dist_1_1" /> --
   SELECT loc.pid, loc.geom
     , (SELECT l.geom FROM ch10.land AS l 
          WHERE l.pid = '58-162') As ref_geom
  FROM ch10.land AS loc
  WHERE loc.land_type = 'apartment'
  ORDER BY loc.geom <-> (SELECT l.geom FROM ch10.land AS l 
          WHERE l.pid = '58-162')
  limit 100
  )
SELECT pid
   , RANK() OVER(ORDER BY ST_Distance(geom, ref_geom)) As act_r -- <co id="code_indexed_dist_search_dist_1_2" /> --
   , ST_Distance(geom, ref_geom) As act_dist
   , RANK() OVER(ORDER BY geom <-> ref_geom) As app_r -- <co id="code_indexed_dist_search_dist_1_3" /> --
   , geom <-> ref_geom As app_dist
  FROM first_hop
  ORDER BY act_r  -- <co id="code_indexed_dist_search_dist_1_4" /> --
  LIMIT 5;
-- <end id="code_indexed_dist_search_dist_1" /> --
-- [1] Top 100 by box centroid distance
-- [2] Ordering by actual distance
-- [3] Ordering by box centroid distance
-- [4] Return top 5 by actual distance

-- Listing  Find 2 closest roads to each school search 1/2 km radius
-- 1961 ms
-- <start id="code_school_road_dwithin_n" /> --
SELECT pid, land_type, row_num, road_name,
      round(CAST(dist_km As numeric),2) As dist_km
FROM (SELECT 
ROW_NUMBER() OVER (PARTITION BY loc.pid    -- <co id="co_code_school_road_dwithin_n_1" />
        ORDER BY ST_Distance(r.geom, loc.geom)) As row_num,
      loc.pid,loc.land_type,r.road_name, 
     ST_Distance(r.geom, loc.geom)/1000 As dist_km
    FROM ch10.land As loc 
LEFT JOIN                 -- <co id="code_school_road_dwithin_n_2" />
        ch10.road As r
ON ST_DWithin(r.geom, loc.geom, 500) 
  WHERE loc.land_type = 'education') As X -- <co id="code_school_road_dwithin_n_3" />
  WHERE X.row_num < 3     -- <co id="code_school_road_dwithin_n_4" />
ORDER BY pid, row_num;
-- <end id="code_school_road_dwithin_n" /> --
-- [1]  sequentially number by proximity
--[2] left join includes no matches
-- [3] wrap in subquery X
--[4] order by pid, proximity


-- <start id="code_geog_cast_dist_op" /> --
WITH ref As (SELECT                       -- <co id="co_code_geog_cast_dist_op_1" /> --
ST_Point(-75.0664, 40.2003)::geography AS ref_geog)
SELECT ident, name
FROM (SELECT ident, name, geog
FROM ch10.airports
ORDER BY (SELECT ref_geog::geometry FROM ref) 
    <-> geog::geometry -- <co id="co_code_geog_cast_dist_op_2" /> --
LIMIT 20) AS x    -- <co id="co_code_geog_cast_dist_op_3" /> --
ORDER BY ST_Distance(geog, (SELECT ref_geog FROM ref))  -- <co id="co_code_geog_cast_dist_op_4" /> --
LIMIT 5;
-- <end id="code_geog_cast_dist_op" /> --

-- [1] define reference geography
-- [2] cast both to geometry use geom index
-- [3] wrap in subquery
-- [4] geog dist check


-- <start id="code_tag_airport_tz" /> --
ALTER TABLE ch10.airports ADD COLUMN tz varchar(30);
UPDATE ch10.airports
    SET tz = t.tzid
FROM ch10.tz_world As t
WHERE ST_Intersects(ch10.airports.geog, t.geog);
-- <end id="code_tag_airport_tz" /> --

-- <start id="code_timestamp_airport_tz" /> --
SELECT ident, name
 , CURRENT_TIMESTAMP AT TIME ZONE tz AS ts_at_airport
FROM ch10.airports
WHERE ident IN('KBOS','KSAN', 'LIRF', 'OMDB', 'ZLXY');
-- <end id="code_timestamp_airport_tz" /> --


-- old code using ST_Line_Interpolate_Point --
SELECT pt_id, ln_id,
ST_Line_Interpolate_Point(ln_geom,
ST_Line_Locate_Point(ln_geom, pt_geom)
) As snapped_point
FROM
(SELECT DISTINCT ON (pt.gid)
ln.geom AS ln_geom,
pt.geom AS pt_geom, ln.gid AS ln_id, pt.gid AS pt_id
FROM
ch10.sites AS pt INNER JOIN
ch10.roads AS ln
ON
ST_DWithin(pt.geom, ln.geom, 10.0)
ORDER BY
pt.gid,ST_Distance(ln.geom, pt.geom)
) AS subquery;
-- old --

-- <start id="code_snap_closest_point" /> --
SELECT DISTINCT ON (pt.gid) -- <co id="co_code_snap_closest_point_1" /> --
  pt.addr_num || ' ' || full_str AS parc_addr 
 , ln.road_name AS ln_road
 , ST_ClosestPoint(ln.geom,pt.geom) As snapped_point -- <co id="co_code_snap_closest_point_2" /> --
FROM
  ch10.land AS pt INNER JOIN
  ch10.road AS ln
 ON ST_DWithin(pt.geom, ln.geom, 20.0) -- <co id="co_code_snap_closest_point_3" /> --
ORDER BY
   pt.gid,ST_Distance(ln.geom, pt.geom); -- <co id="co_code_snap_closest_point_4" /> --
-- <end id="code_snap_closest_point" /> --
--[1] 1 record per parcel
--[2] find closest point on line to location
--[3] pick roads within 20 meters if parcel
--[4] pick road closest to parcel










