[postgis-users] Select closest line
Mickael MONSIEUR
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.
Felix Kunde
https://postgis.net/docs/ST_ShortestLine.html
Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr
Von: "Mickael MONSIEUR" <mickael....@gmail.com>
An: postgi...@lists.osgeo.org
Betreff: [postgis-users] Select closest line
Hi,
Mickael_______________________________________________ postgis-users mailing list postgi...@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]
_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
Mickael MONSIEUR
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...
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
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.
Mickael MONSIEUR
I encounter at the moment is that ogr2ogr created a table for each folder in my KML.
I'm looking for a way to put everything in one table ... if not how to do a search on +1000 tables ?!
