I have two point tables from which I want to extract a value from one table to the other, based on the nearest distance of those two points.
t1 t2 id | value id 1 | 100 1 2 | 105 2 3 | 120 3 4 | 102 4 etc
t2 with id 1 is closest to
t1 with id 2 it should get the value 105. The
t2 table has more entries than
t1 and all points should get a value.
Using the following query I am able to get the distance to the nearest point:
SELECT round(ST_Distance( (SELECT ST_AsText(t2.wkb_geometry) FROM t2 WHERE id = 1), (SELECT ST_AsText(t1.wkb_geometry) FROM t1 ORDER BY ST_AsText(t1.wkb_geometry) <-> (select ST_AsText(t2.wkb_geometry) FROM t2 where id = 1) LIMIT 1 ) )) as Distance;
How would I add the corresponding
t1 values bases on the closest point to
t2 for all the