St_ClosestPoint within St_MakeLine Query problem

Question

I am currently trying to create the closest point (closest_ports_point_test) on the grid (lng_grid_test) from the original points (ports_point), and to create the line connecting the original and the closest points.

CREATE TABLE closest_ports_point_test
AS 
SELECT * 
FROM ST_MakeLine(
    ST_ClosestPoint(ports_point.geom, lng_grid_test.geom),
    ports_point.geom
    );

And I also tried :

CREATE TABLE closest_ports_point_test
AS 
SELECT * 
FROM ST_MakeLine(
    ST_ClosestPoint(
        lng_grid_test.geom::geometry(LineString, 4326), 
        ports_point.geom::geometry(Point, 4326)
        ), 
    ports_point.geom::geometry(Point, 4326)
    );

But I get the following error warning for both results:

ERREUR: entrée manquante de la clause FROM pour la table « lng_grid_test » LINE 2: AS SELECT * FROM ST_MakeLine(ST_ClosestPoint(lng_grid_test.g... ********** Error ********** ERREUR: entrée manquante de la clause FROM pour la table « lng_grid_test » SQL state: 42P01 Character: 310

Do you have any idea about what could be wrong with that?

Answer 1

You need to define how the tables included are related.

Also, for this purpose you have st_shortestline, that creates the line for you.

The syntax is: Select st_shortestline(a.geom, b.geom) From a, b

Then you probably want to put some where clause there or you will get all possible combinations between the tables.