PostGIS : closest line from a point

Question

I need to identify the closest line from a point. Elements are stored in a postgreSQL/postGIS database, in the same projection. From a point (geom multipoint to be specific), I need to identify the closest line (multilinestring) element from an other table.

Theoretically it should look like this :

SELECT myColumnLine FROM myLineTable WHERE ST_Closest(geomPoint, geomLine) LIMIT 1

I think ST_ClosestPoint can do the trick but I want to return line attribute, not coordinates.

Answer 1

If you want to find nearest neighbor between features of two different tables,you can try the sql below

SELECT distinct on(table1.id)refid ,table1.*,table2.*
st_distance(table1.geom,table2.geom) as d
FROM table1 
inner join table2 on table1.id!=table2.id 
ORDER BY table1.id,table1.geom <-> table2.geom;

this can also be modified to run for single feature only

Answer 2

If you want the attributes of a feature closest to a given point, it is a case of searching the closest neighbour.

Try this:

SELECT 
  myLineTable.myColumnLine,
  ST_Distance(geomPoint, myLineTable.geomLine) AS dist
FROM myLineTable
ORDER 2
LIMIT 1;

Answer 3

The following can do the trick

SELECT ST_Transform(second.geomPoint, 4326), ST_Transform(first.geomLine,4326),
 *
FROM myLineTable first, myLineTable second
ORDER BY ST_Distance(second.geomPoint, first.geomLine)
LIMIT 1;

similar to Closest line to a point using PostGIS?