Select closest line

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Select closest line

Mickael MONSIEUR
​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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Select closest line

Felix Kunde
Would ST_ShortestLine help?

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

Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr
Von: "Mickael MONSIEUR" <[hidden email]>
An: [hidden email]
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 [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Select closest line

Mickael MONSIEUR
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 <[hidden email]>:
Would ST_ShortestLine help?

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

Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr
Von: "Mickael MONSIEUR" <[hidden email]>
An: [hidden email]
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 [hidden email] <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] 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 <[hidden email]>:

Would ST_ShortestLine help?

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

Gesendet: Donnerstag, 27. April 2017 um 11:24 Uhr
Von: "Mickael MONSIEUR" <[hidden email]>
An: [hidden email]
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 [hidden email] https://lists.osgeo.org/mailman/listinfo/postgis-users[https://lists.osgeo.org/mailman/listinfo/postgis-users]
_______________________________________________
postgis-users mailing list
[hidden email]
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.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Select closest line

Mickael MONSIEUR
Thank you for this clarification.

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 ?!





_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users