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
So if 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 t2
rows?
Best Answer
Run
It's obligatory to have spatial indexes in place on both
wkb_geometry
columns.If maximum accuracy is of importance, use a cast to geography, i.e.
... ORDER BY a.wkb_geometry::GEOGRAPHY <-> t2.wkb_geometry::GEOGRAPHY ...
at a slight increase of execution time.
Uncomment the distance calculation if you need distance in meter.
Note: the cast to geography does only work if your geometry columns are in EPSG:4326! If they are, it's a good idea to use the cast at least for the distance calculation.
This is a typical KNN query; you can find plenty of rerence on this board or elsewhere on the net for more detail on the general matter using PostGIS.
Possibly related: