Nearest Line to Polygon

nearest-line-to-polygon.sql

with distance_cte AS 
(
  SELECT 
    pv.PFI AS pr_view_pfi, 
    tr.PFI AS tr_pfi, 
    pv.SP_GEOMETRY.STDistance(tr.SP_GEOMETRY) AS distance,
    RANK() OVER (PARTITION BY pv.pfi ORDER BY pv.SP_GEOMETRY.STDistance(tr.SP_GEOMETRY)) AS rank_distance
  FROM Flux.vmprop.PROPERTY_VIEW AS pv, Flux.vmtrans.TR_ROAD AS tr
--WHERE pv.pfi = 173589345
)
, unique_cte AS
(
  select 
    pr_view_pfi, 
    tr_pfi, 
    distance,
    RANK() OVER (PARTITION BY pr_view_pfi ORDER BY tr_pfi desc) AS rank_pfi
  from distance_cte
  where rank_distance = 1
)
SELECT 
  pr_view_pfi,
  tr_pfi,
  distance
FROM unique_cte 
WHERE rank_pfi = 1