3

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?

  • So, you want to update matched_geom with the value of the_geom, ie, the closest point. If so, you are going to need some kind of unique id (primary key) in testme in order to do the update. You also don't need to select a.geom, and b.geom because that is not part of update. You will also probably want some kind of where condition, possibly using ST_DWithin, if you have big tables to avoid lots of pointless checks for closest point. If this is all correct, I will happily show you how to rewrite this so that it becomes an update. – John Powell Dec 10 '15 at 18:54
  • John, thank you very much for your kind response. The points you mentioned are all correct. I thought about the ST_DWithin function, but didn't got that far. Every row in table testme got a unique serial ID which can probably be used als primary key (id for example 4331109, 4331110 and so on). How can I adress that unique ID? Alter TABLE testme Add Column matched_geom geometry(Point,4326); Select b.id, ST_Closestpoint(b.geom2, a.geom) as the_geom from roads as a, testme as b – MikeH Dec 10 '15 at 20:33
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.

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.