# PostGIS: Get closest point between geometry and Multipoint

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...

• 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? Sep 19, 2014 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. Sep 19, 2014 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))? Sep 22, 2014 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. Sep 22, 2014 at 16:19
• good to know, thx @NicklasAvén! Sep 22, 2014 at 16:42