0

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?

  • any success here? – ThingumaBob Dec 1 '18 at 19:08
1

Run

SELECT a.id,
       b.value,
       -- ST_Distance(a.wkb_geometry::GEOGRAPHY, b.wkb_geometry::GEOGRAPHY) as dist,
       a.wkb_geometry
FROM t2 AS a
JOIN LATERAL (
  SELECT value
  FROM t1
  ORDER BY a.wkb_geometry <-> t2.wkb_geometry
  LIMIT 1
) AS b
ON true;

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.