[GIS] PostGIS: Get closest point between geometry and Multipoint

distancelinestringpostgissql

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…

Best Answer

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);

Related Question