Sunday 14 May 2017

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


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

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...