<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Georgia;
panose-1:2 4 5 2 5 4 5 2 3 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.gmail-
{mso-style-name:gmail-;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-GB" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hi Mickael,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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 <b>school</b> and my road network lines in a table called
<b>roadlink.</b> 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">FROM school a, roadlink b<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"> WHERE ST_DWithin(a.geometry, b.geometry, 120)<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">ORDER BY a.schoolname, ST_Distance(a.geometry, b. geometry),ST_LineLocatePoint(b. geometry, a.geometry);<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">schoolname ; target_eid ; distance ; target_pos<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">"Aberlemno Primary School" ; 67931 ; 24.2037555387589 ; 0.83357091437109<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">"Airlie Primary School" ; 71209 ; 20.546270421497 ; 0.35921301881136<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">"Andover Primary School" ; 65434 ; 29.4759054143719 ; 0.384613989707959<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">"Arbirlot Primary School" ; 74089 ; 24.1250850414291 ; 0.71569150125111<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">"Arbroath Academy" ; 63976 ; 42.0475920832573 ; 1<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Hope that helps<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">Ross<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span lang="EN-US" style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Mickael MONSIEUR<br>
<b>Sent:</b> 27 April 2017 17:44<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] Select closest line<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal"><span class="gmail-"><span lang="EN" style="font-family:"Georgia","serif";color:#134F5C">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!</span></span><span lang="EN" style="font-family:"Georgia","serif";color:#134F5C"><br>
<span class="gmail-">They are scattered in 1000 tables... (!!!!) Thanks phpPGAdmin...
</span><br>
<span class="gmail-">I am looking for how ogr2ogr can make ONE table with ALL polylines and not a table for each...</span></span><span style="font-family:"Georgia","serif";color:#134F5C"><o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">2017-04-27 15:28 GMT+02:00 Felix Kunde <<a href="mailto:felix-kunde@gmx.de" target="_blank">felix-kunde@gmx.de</a>>:<o:p></o:p></p>
<p class="MsoNormal">Would ST_ShortestLine help?<br>
<br>
<a href="https://postgis.net/docs/ST_ShortestLine.html" target="_blank">https://postgis.net/docs/ST_ShortestLine.html</a><br>
<br>
<br>
<br>
Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr<br>
Von: "Mickael MONSIEUR" <<a href="mailto:mickael.monsieur@gmail.com">mickael.monsieur@gmail.com</a>><br>
An: <a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
Betreff: [postgis-users] Select closest line<br>
<br>
Hi,<br>
<br>
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.<br>
<br>
I would like with a GPS coordinate, select the closest lines.<br>
<br>
I found this function: <a href="https://postgis.net/docs/ST_ClosestPoint.html" target="_blank">
https://postgis.net/docs/ST_ClosestPoint.html</a> but I can not put it into practice.<br>
<br>
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.<br>
<br>
Someone would have an idea how to do it?<br>
<br>
I admit to not being comfortable with the spacial search and the different types of coordinates (mercator, google, degrees, gps ..) I get lost completely.<br>
<br>
Thanks,<br>
Mickael_______________________________________________ postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users%5bhttps:/lists.osgeo.org/mailman/listinfo/postgis-users%5d" target="_blank">
https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
<div> </div>
<div>
<p class="MsoNormal" style="MARGIN: 0cm 0cm 0pt"><span style="FONT-SIZE: 7.5pt; FONT-FAMILY: 'Arial','sans-serif'; mso-bidi-font-size: 8.0pt; mso-bidi-font-family: 'Times New Roman'"><font size="2">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.<span style="mso-spacerun: yes">
</span>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.
</font></span></p>
</div>
</body>
</html>