1

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?

  • I also tried : CREATE TABLE closest_ports_point_line_test(gid serial, geom geometry(LineString,4326)) AS SELECT * FROM( SELECT ST_MakeLine(ST_ClosestPoint(ports_point.geom::geometry(Point,4326), lng_grid_test.geom::geometry(LineString,4326)), ports_point.geom::geometry(Point,4326)) AS close_line FROM ports_point, lng_grid_test )AS geom; But there I get: ERREUR: erreur de syntaxe sur ou près de « AS » LINE 8: AS SELECT * FROM( – user37103 Apr 26 '16 at 14:22
2

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.

  • Sorry, I am not sure what you're implying by "how the tables inclued are related"...? Even with a simple query like : SELECT ST_ShortestLine(ports_point.geom, lng_grid_test.geom) FROM ports_point, lng_grid_test; I get an error (ERREUR: Operation on mixed SRID geometries) – user37103 Apr 26 '16 at 16:00
  • And If i try try to set the SRID: SELECT ST_ShortestLine(ports_point.geom::geometry(Point,4326), lng_grid_test.geom::geometry(LineString,4326)) FROM ports_point, lng_grid_test; I get the error : ERROR: Geometry SRID (0) does not match column SRID (4326) – user37103 Apr 26 '16 at 16:04
  • The syntax geom(geometry,4326) doesn't set the srid.what it does is like adding a constraint on srid 4326 on the geometry. To set the srid use st_setsrid, to change srid use st_transform – Nicklas Avén Apr 26 '16 at 16:09
  • With find_srid I just found out that my table ports_point was SRDI=0, so I think this is the problem, but I thought it would be ok with setting ports_point.geom::geometry(Point,4326) for the ShortestLine clause....? – user37103 Apr 26 '16 at 16:09
  • Ok I used : ALTER TABLE ports_point ALTER COLUMN geom TYPE geometry(Point, 4326) USING ST_SetSRID(geom,4326); to set my SRID to 4326 for port_points, and it's currently running for the shortestline. Hope it will go well, Thanks :) – user37103 Apr 26 '16 at 16:30

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.