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?