4

I think my question is not that difficult for GIS professionals, but i didn't found an answer to my problem (even not in other questions).

Problem: I have given two tables (table1,table2) with Points as geometries in my PostgreSQL-database. Now I'm searching for each point in table1 the closest point from all points in table2. after that the closest point (from table2) should be build to a linestring with the current point from table1.

my approach:

CREATE TABLE new_table_shortest_line WITH (OIDS) AS
SELECT a.name,a.type, 
       ST_MakeLine(a.the_geom,
              ST_ClosestPoint(a.the_geom,ST_Collect(b.the_geom))
       ) AS closest_line
FROM table1 AS a, table1 AS b
GROUP BY a.name, a.type, a.the_geom, b.the_geom

the result of this SQL-query should be a new table with a linestrings as geometries, which define the closest lines between a point in table1 and the closest point of table2...

  • 1
    A few notes and questions. In your SQL statement you have "FROM table1 AS a, table1 as b". I think you meant "table2 AS b". I'm also unsure exactly what you're trying to accomplish with ST_Collect, could you elaborate on that? – Gregory Arenius Sep 19 '14 at 18:51
  • Collecting all geometries from table 2 is a way to compare a geometry in table 1 with all geomeries in table 2. Another way is ordering by distance and using distinct. – Nicklas Avén Sep 19 '14 at 20:45
  • sorry for answering so late, haven't been able to answer since friday... @GregoryArenius: yes, i mean table2 as b... and as Nicklas said, i used ST_Collect to compare each table1 entry with all objects of table2. but i don't know, if it's the way to go... Maybe through ST_Collect the closest point is here equal to the centroid of the new object (ST_Collect(geom))? – mapImprover___ Sep 22 '14 at 11:52
  • No ST_ClosestPoint, ST_ShortestLine and ST_Distance all behave the same. For a collection they iterate all vertexpoints and test against both the vertex points and the line between the points. In some cases it takes a shortcut but the result is the same. – Nicklas Avén Sep 22 '14 at 16:19
  • good to know, thx @NicklasAvén! – mapImprover___ Sep 22 '14 at 16:42
3

Take a look at ST_ShortestLine. http://postgis.net/docs/manual-2.1/ST_ShortestLine.html

ST_ClosestPoint is the first point in ST_ShortestLine.

Something like this should work.

SELECT DISTINCT ON (a.id) ST_ShortestLine(a.geom,b.geom) FROM table1 a,table2 b ORDER BY a.id,ST_Distance(a.geom,b.geom);

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.