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?
No comments:
Post a Comment