PostGIS: Assign weights to one set of points based on distance from another set of points

Question

In my PostgreSQL/PostGIS (Windows 10) database, I have two tables, reference and targets. I want to calculate the distance from each reference point to all the target points within 50 m buffer, and assign weights to the targets such that the nearest ones get the highest weights and so on.

At the moment, I am taking the simplest approach, i.e., if the targets are within a certain threshold (e.g. within 10 m of reference), then identify them as "neighbors" and assign 1 else assign 0. This is how the query looks like:

Select
    a.gid r_id,
    a.x,
    a.y,
    b.gid t_id,
    ST_Distance(a.geom,b.geom) dist,
    case 
    when ST_Distance(a.geom,b.geom) < 10
    then 1 else 0
    end as wgt,
    a.geom
From 
    reference a
left join targets b 
on st_dwithin(a.geom,b.geom,50)
where b.gid is not null -- ignore all targets not within 50 m
order by a.gid, ST_Distance(a.geom,b.geom) ASC

However instead of 0 and 1, I would like to assign the targets continuous/fractional weights (e.g. 0, 0.35, 0.65, 0.99) based on the proximity as described above, the nearest targets get the highest weights. Can someone help with or give hints about how this can be done?

PS. I found this relevant question taking a weighted raster approach.

Answer 1

Use a LATERAL join to fetch all targets in proximity of each reference, then calculate the weights based on their distance:

SELECT
  r.id AS r_id,
  t.id AS t_id,
  t.dist,
  1.0 - ( t.dist / 50.0 ) AS wgt
FROM
  reference AS r
  CROSS JOIN LATERAL (
    SELECT
      _t.id,
      ST_Distance(r.geom, _t.geom) AS dist
    FROM
      targets AS _t
    WHERE
      ST_DWithin(r.geom, _t.geom, 50)
  ) AS t
-- ORDER BY
 --  1, 4 DESC
;