Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Using Postgres/PostGIS, I am trying to query the database for the closest arc to a particular GPS point. Each Arc is made up of two nodes that are GPS points on a map. Is there a way I could query this using Postgres and also get the distance of the point from the arc?


I have been trying to find the road to which a GPS point is closest to. I have a postGIS database which i loaded with the map of the road network. According to a previous question asked here Calculate distance of point from an arc/polyline this was to be done be creating a straight line then look for the shortest distance form this line. The road network is however not straight thus this wont work. Anybody with a solution? i would really appreciate it. Thank you in advance

share|improve this question
    
Sorry for the typo. It has been rectified –  Magondu May 2 '12 at 6:23
3  
-1, You have to get an understanding of what you really want. Last time you were asking about edges defined by two gps-points. That is why you did get an answer about hoe to build a line from a series of points and get the distance to it. But you still don't say anything abou how you define a road. What is it that you do want to find. Is it the point on the road that is closest to the gps-point? Is i t the road number? –  Nicklas Avén May 4 '12 at 6:58
    
Im sorry if i might have misled you. I am creating an application that detects traffic congestion by calculating how far you have moved over a period of time. To do this i need to identify on which road segment a GPS device is on after every unit time. I just need to determine the road(defined as an arc in my database) where the device is on. Where the distance is shortest it means the device is most probably on that road –  Magondu May 4 '12 at 7:11
1  
What do you mean by arc? As I pointed out in the other post you probably mean something else. To read about nearest neighbor calculations you can visit bostongis.org/?content_name=postgis_nearest_neighbor#120 –  Nicklas Avén May 4 '12 at 7:26
    
I have a database of nodes and arcs. Nodes are GPS coordinates of where roads intersect. The arcs depict the road network between these nodes i.e they make the road network. –  Magondu May 4 '12 at 7:32

4 Answers 4

EDIT:

SELECT arc_id 
FROM road_table 
ORDER BY ST_Distance(road_table.geom, 'gps-point in wkt') 
LIMIT 1;

Since PostGIS 1.5 you also have the option of using ST_ClosestPoint.

Some discussions of how to use it can be found here:

http://blog.jordogskog.no/2010/02/07/how-to-use-the-new-distance-related-functions-in-postgis-part1/

EDIT

About ST_ClosestPoint you can read more in the PostGIS documentation: http://postgis.net/docs/ST_ClosestPoint.html

As I understand your query correct you don't have any line ready but a list of points in a table.

What you need to do then is writing a query that creates lines from those points and use ST_ClosestPoint on those lines. You do it all in one query. To do that you can design your query in several ways and approaches depending on in more detail what you want to get. If it is just the distance you want, then forget about ST_ClosestPoint then use ST_Distance to get the distance. If you just want the closest Point on the edge and/or the distance, then you can build the whole linestring with ST_MakeLine If you also want to find the points defining the edge that is closest it is probably easiest to make a self join that builds lines from each opint pair in the table instead and use ST_Distance and /or ST_ClosestPoint on that two point lines.

This I guess looks quite messy, but that is because there is a lot of possibilities, what you actually want to get.

BTW I think edge is a more correct word than arc.

EDIT2:

Ok, from your comment I guess that what you want is a query that self-joins to create edges and returns the closest edge and distance. You will need a integer field to make it work like this with integer values ordered as the gps-points and without holes in the series. I call id gid here.

Then you can run something like:

SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table a INNER JOIN point_table b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;

EDIT 3 a.geom references geom in the table that I have put the alias a on. I shouldn't have left out the "AS" key-word, then it looks like this:

SELECT dist, the_edge FROM
(SELECT ST_Distance(e.the_edge, 'PUT YOUR GPS_POINT AS WKT HERE') as dist, the_edge FROM
(SELECT ST_MakeLine(a.geom, b.geom) AS the_edge FROM
point_table AS a INNER JOIN point_table AS b ON b.gid=(a.gid+1)) e) s
ORDER BY dist LIMIT 1;

To get a short intro to sql in general you can test the tutorial on PostGISonline

To see some spatial examples you can try: mixed examples

HTH

Nicklas

share|improve this answer
    
Thank you for your help. I have seen viewed the link posted and to be honest i do not see where i can specify my own GPS point in the query. Could you please rewrite the query with user input GPS points. I have imported a Shape file using POSTGIS. The shapefile comprises of two tables, Arcs and Nodes. The arcs are just specified using the nodes. I need to query the database the closest arc. Thank you –  Magondu May 2 '12 at 11:20
    
I want to have an efficient query that determines the edge to which a point is closest to –  Magondu May 2 '12 at 12:16
    
That seems to be very helpful. Could you please explain the query. I am a newbie to Postgres SQl i started using it recently for the PostGIS capability. I have understood WKT but how do b.geom and co. work? –  Magondu May 2 '12 at 13:33
    
Thank you so much for your Help Nicklas. I will tell you how it goes. I am looking forward to your continued support. Do you have an email address we can continue discussing this issue? –  Magondu May 2 '12 at 15:57
2  
Yes it is absolutely possible. But as a start I think you should update your question and try to explain what you relly want to do and what you have got. Then it is time to start reading about sql in general, how to build a query. Then you can come back with more detailed question about the parts if it is something you don't get right. It also seemsnlikenyou should unmark that your question isnsolved. –  Nicklas Avén May 4 '12 at 4:14

about your question, i think you should use ST_Line_Interpolate_Point function for finding closest point on a polyline and ST_Distance function for calculating distances between the closest point and polyline.

what i understood from your question is that you meant something like this:

interpolation

ST_Line_Locate_Point — Computes de fraction of a Line from the closest point on the line to the given point. link

ST_Line_Interpolate_Point — Returns a point interpolated along a line. Second argument is a float8 between 0 and 1 representing fraction of total length of linestring the point has to be located. link

ST_Distance — For geometry type Returns the 2-dimensional cartesian minimum distance (based on spatial ref) between two geometries in projected units. For geography type defaults to return spheroidal minimum distance between two geographies in meters. link

Example for calculating closest point

SELECT ST_Line_Locate_Point(ST_GeomFromEWKT('LINESTRING (29 35, 29 21, 11 21)'),ST_GeomFromEWKT('POINT (20 28)'));
--Result
0.71875

-- Using ST_Line_Interpolate_Point (see graphic below)
SELECT ST_AsText(ST_Line_Interpolate_Point(line,ST_Line_Locate_Point(line,point))) as interpolate 
FROM (SELECT ST_GeomFromText('LINESTRING (10 11, 15 26, 29 35)') as line, 
      ST_GeomFromText('POINT (27 27)') as point) 
as foo;

--Result
                  interpolate
-----------------------------------------------
 POINT (23.945848375451263 31.750902527075812)
(1 row)

Example for calculating distance

-- Geography example -- same but note units in meters - use sphere for slightly faster less accurate
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist 
FROM (SELECT
    ST_GeographyFromText('SRID=4326;POINT(-72.1235 42.3521)') As gg1,
    ST_GeographyFromText('SRID=4326;LINESTRING(-72.1260 42.45, -72.123 42.1546)') As gg2
    ) As foo  ;

  spheroid_dist   |   sphere_dist
------------------+------------------
 123.802076746848 | 123.475736916397

i think you can calculate what you want with this examples. i am not on my own computer which has postgres on it for writing certain sql for you...

and you can read Solving the Nearest Neighbor Problem in PostGIS and PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution for gaining idea and experience for Nearest Neighbor Problem...

i hope it helps you...

share|improve this answer
    
Thank you for your comment. I am using some shape files that have two tables nodes and arcs. The node one contains gps coordinates of where roads intersect and the Arc table contains info on the arcs between this nodes. Is there a way to have a single query that could determine the arc closest to that GPS point? –  Magondu May 2 '12 at 11:23

How do 2 nodes define an arc? As far as I remember they define a line, don't they?

Anyway if function d(a,x,y) gives you the distance of (x,y) point from "a" (let it be arc or line) then

"SELECT arc_id, d(a,x,y) AS distance FROM arcs ORDER BY distance"

would be the way to start. "a" is your representation in the DB, so if I read your lines it would be x1,y1,x2,y2 (2 nodes as you wrote, each has x,y coordinate)

"SELECT arc_id, d(x1,y1,x2,y2,x,y) AS distance FROM arcs ORDER BY distance"

Now d() depends on what you really want (distance from line or arc or whatever), but the logic is the same, it would work for anything where you can define a function that gives a "distance" from a given point.

Of course probably you have 3 dimensions, so you'll also have x3,y3, but it doesn't matter, as long as you have the function.

share|improve this answer

In PostGIS you need one of those functions:

  • If your coordinates are projected (= x,y): st_distance(geom, geom)

  • If your coordinates are geographic (= lat,lon): st_distance_sphere(geom, geom)

Do you mean Segment instead of Arc maybe?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.