1

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.

  • 2
    If you have Multigeom, it could be wise to start by Dumping them and go to single geometry type before looking for the "closest". Because, you may not find the result you want. postgis.net/docs/ST_Dump.html – Maximilien jaffrès Jul 30 at 11:36
  • You're right. I can fix this with (ST_Dump(geomPoint)).geom and (ST_Dump(geomLine)).geom but I can't see what to do after. – GeoGyro Jul 30 at 13:03
1

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?

  • It's a good way, you're right. Just a syntax precision SELECT p.geomPoint, l.geomLine from myLineTable l, myPointTable p ORDER BY ST_Distance(p.geomPoint, l.geomLine) limit 1; Thanks – GeoGyro Jul 30 at 13:32
  • The ST_Transform function is unnecessary if the geometries on both tables are in the same CRS. And, if they aren't, then one would need to use the ST_Transform function in the input geometries for the ST_Distance function to work correctly. – Carlos MSF Jul 30 at 16:14
3

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

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;

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.