[postgis-users] Select closest line

[McDonaldR]

Hi Mickael,

Once you get your ways (lines) and waypoints (points) tables sorted out you can use the following SQL to find the closest edge/line to a point geometry.  In the example below I have points in a view called school and my road network lines in a table called roadlink.  Both the points and lines have a geometry column. The road edge id is ogc_fid.  The ST_DWithin function only searches for edges within the specified distance – adjust as required for your dataset.  Smaller numbers make it faster as it searches less data.

SELECT DISTINCT ON (a.schoolname) a.schoolname, b.ogc_fid AS target_eid, ST_Distance(a.geometry, b.geometry) AS distance, ST_LineLocatePoint(b.geometry, a.geometry) AS target_pos
FROM school a, roadlink b
  WHERE ST_DWithin(a.geometry, b.geometry, 120)
ORDER BY a.schoolname, ST_Distance(a.geometry, b. geometry),ST_LineLocatePoint(b. geometry, a.geometry);

The query will return the schoolname, the id of the nearest line, the distance to the nearest line, the position of the point on the line closest to the school point as a fraction of line length between 0 and 1.

schoolname ; target_eid ; distance ; target_pos
"Aberlemno Primary School" ; 67931 ; 24.2037555387589 ; 0.83357091437109
"Airlie Primary School" ; 71209 ; 20.546270421497 ; 0.35921301881136
"Andover Primary School" ; 65434 ; 29.4759054143719 ; 0.384613989707959
"Arbirlot Primary School" ; 74089 ; 24.1250850414291 ; 0.71569150125111
"Arbroath Academy" ; 63976 ; 42.0475920832573 ; 1

Hope that helps

Ross

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Mickael MONSIEUR
Sent: 27 April 2017 17:44
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Select closest line

Already, I have just understood an error .. is that in "waypoint" (table) there are only points of interest and not the polylines that interest me!
They are scattered in 1000 tables... (!!!!) Thanks phpPGAdmin...
I am looking for how ogr2ogr can make ONE table with ALL polylines and not a table for each...

2017-04-27 15:28 GMT+02:00 Felix Kunde <felix-kunde at gmx.de<mailto:felix-kunde at gmx.de>>:
Would ST_ShortestLine help?

https://postgis.net/docs/ST_ShortestLine.html



Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr
Von: "Mickael MONSIEUR" <mickael.monsieur at gmail.com<mailto:mickael.monsieur at gmail.com>>
An: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
Betreff: [postgis-users] Select closest line

​Hi,

I imported a kml file with ogr2ogr and I have a well-filled database, including a "waypoints" table and hexadecimal coordinates in the "wkb_geometry" column.

I would like with a GPS coordinate, select the closest lines.

I found this function: https://postgis.net/docs/ST_ClosestPoint.html but I can not put it into practice.

In my result, I would like to have the GPS positions of the nearest line, as well as the distance in kilometers between the line and my GPS coordinates.

Someone would have an idea how to do it?

I admit to not being comfortable with the spacial search and the different types of coordinates (mercator, google, degrees, gps ..) I get lost completely.

Thanks,
Mickael_______________________________________________ postgis-users mailing list postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]<https://lists.osgeo.org/mailman/listinfo/postgis-users%5bhttps:/lists.osgeo.org/mailman/listinfo/postgis-users%5d>
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users


This message is strictly confidential. If you have received this in error, please inform the sender and remove it from your system. If received in error you may not copy, print, forward or use it or any attachment in any way. This message is not capable of creating a legal contract or a binding representation and does not represent the views of Angus Council. Emails may be monitored for security and network management reasons. Messages containing inappropriate content may be intercepted. Angus Council does not accept any liability for any harm that may be caused to the recipient system or data on it by this message or any attachment.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170428/ccbd5e42/attachment.html>