Update table based on closest point between its geometry(Point) and another table's geometry (MultiLinestring)

Question

I think my question is rather simple, but being new to PostGIS, I can't figure it out myself nor being able to transfer knowledge of other questions to my issue.

The problem: I have two tables (testme, roads) with points as geometries (b.geom2 intestme --> point; a.geom in roads --> multistring) in my PostgreSQL-database. Now I am trying to find for every point in testme the closest point in roads.

Alter TABLE testme Add Column matched_geom geometry(Point,4326); 
Select a.geom, b.geom2,  ST_Closestpoint(b.geom2, a.geom) as the_geom
from roads as a, testme as b

The result of the query should be to append the closest point found (for every point/row in testme according to roads) in a new column called matched_geom in the table testme...

---

I executed the following query, which doesn't deliver any result.

Alter TABLE testme Add Column matched_geom geometry(Point,4326);
Select a.geom, b.geom2,  ST_Closestpoint(b.geom2, a.geom) as the_geom
from roads as a, testme as b;
WITH closest_points(id, geom) AS 
   (SELECT id, ST_ClosestPoint(a.geom, b.geom2) as closest_geom
    FROM roads a, testme b
    WHERE ST_DWithin(a.geom, b.geom2, 100))
UPDATE testme SET matched_geom = closest_geom
FROM closest_points
WHERE testme.id = closest_points.id;

What is going wrong? The table testme contains about 150 rows, can it take that long to determine the closest points or is there still a mistake in the query?

Answer 1

There are a number of ways of updating a table based on some query involving the same table. However, the easiest, I find, is to use a WITH query or Common Table Expression, (CTE), to calculate the values and then run the update from this. In your case, you need a geometry returned from ST_ClosestPoint and a primary key (or other unique field) to use for updating. As ST_ClosestPoint does not use a spatial index, you might want to use ST_DWithin in the WHERE clause if you have large tables to avoid all possible points and linestrings being compared to find the closest.

WITH closest_points(id, geom) AS 
   (SELECT id, ST_ClosestPoint(a.geom, b.geom) as closest_geom
    FROM roads a, testme b
    WHERE ST_DWithin(a.geom, b.geom, 100)
UPDATE testme SET matched_geom = closest_geom
FROM closest_points
WHERE testme.id = closest_points.id;

I have assumed your primary/unique key is called id and have put a value of 100 for ST_DWithin. You can, of course, omit the where clause, and note that it is dependent on your SRID, so here I have assumed 100 meters. If you do use a ST_DWithin, ensure you have an spatial index on the roads table -- seeing as you will have to do a full table scan on points, having a spatial index on that table will not help the execution of this query.