0

I generate the OSM data of Africa postgis and I want to launch queries for a list of gps points to project them into the closest line. I made this request to a single point but took around 3 seconds which is not acceptable because i'll repeat it for dozens of points. 

SELECT osm_name,ST_Distance(st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326),geom_way) AS dist
FROM af_2po_4pgr
WHERE geom_way && expand(st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326), 0.01)
ORDER BY dist limit 1;

1) Is there a better approach to achieve what I wanted to do. 2) How to optimize this query.

I want the result illustrated in the first image.

I want this result

But i have this one. But i have this one

enter image description here

khairy
  • 3
  • 2
  • Do you use an index for geom_way? Have a look into the created sql-file (last line). There you will find sth. like this here: -- CREATE INDEX idx_hh_2po_4pgr_geom_way ON hh_2po_4pgr USING GIST (geom_way GIST_GEOMETRY_OPS); And it's a good idea to use a bounding-box around your points. Nevertheless this question rather belongs to the postGIS area. – Carsten Mar 10 '14 at 18:41
  • Thanks Carsten for your response.yes I create an index on the geom column and it accelerate the query but it has not given me a convincing result (view illustration) – khairy Mar 11 '14 at 07:02

2 Answers2

0

About query 

WHERE geom_way && expand(st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326), 0.01)

It could be faster with :

WHERE ST_DWithin(geom_way , st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326), 0.01)   -- (did not test)

If you want closest point on line see ST_ClosestPoint(geom,geom) So query should be something like this assuming that your green line is one geom (which it probably is not).

SELECT osm_name, ST_ClosestPoint(linegeom, pointgeom) as point_on_line, ST_Distance(st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326),geom_way) AS dist
FROM af_2po_4pgr WHERE ST_DWithin(geom_way , st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326), 0.01) ORDER BY ST_Distance(st_geomfromtext('Point('|| 10.426925 || ' ' || 36.611049 ||')',4326),geom_way) ASC LIMIT 1 --(not tested)

Now if that green line is actually a route which is returned with pg_dijkstra or similar pgrouting algorithm you can use than return set as table for closest point query. If it in osmpo you probably can get all those ids out from it, maybe,i don't know. And use those ids like this. As long as there is indexes it should be way faster than 3s.

SELECT ..... FROM (select * FROM af_2po_4pgr WHERE some_id IN ( 123,31232,321,...) as data

You can replace that IN ( ) with IN ( SELECT edge_id from shortest_path(...)) ( <- is not correct pgrouting shortest_path function, pgr_results and pgr_costresults3 returns edgeids, so it can be used to limit closest point search and if you do routing with pgrouting you can get all results in one query )

simpleuser001
  • 3,864
  • 18
  • 21
  • Tanks a lot for the given information, it may help me somedays.But i probably didn't set up correctly my question.I have a list of gps point as inputs and i want to choose the more adequat lines to set up a path between them.The green line is made by the service OSRM, but i want that my data persist in the database, so i would like to extract my path from the table generated by osm2po (or osm2pgrouting).I hope that i didn't bother you by my inaccurate qustion. – khairy Mar 11 '14 at 09:15
  • so you are using pgrouting ? And you want start and end points onto linestring and find fastest route between them ? Or do you want resolve traveling salesperson problem , or do you want solve shortest path from 1. to 2. gps point , then 2. to 3.. etc.. – simpleuser001 Mar 11 '14 at 10:12
  • it seems to be the third option, i have to preserve the order of the points.If i linked the point like in the third image it doesn't fit the route. What should i do. – khairy Mar 11 '14 at 10:40
  • you know correct order for points ? – simpleuser001 Mar 11 '14 at 10:54
  • yes, i know the correct order. – khairy Mar 11 '14 at 11:06
  • well, first try this. find closest edge id for 2 points, calculate shortest path between those and see if its near what you want. – simpleuser001 Mar 11 '14 at 11:16
  • Sorry, i didn't understand, if you mean ' shortest path between the 2 points' why should i find the closest edge.If not, is there a way to get shortest path between edges. – khairy Mar 11 '14 at 12:49
0

Dijkstra and similar routing algorithms will give you poor results if your GPS device is not following the shortest path. Have a look at this post on map-matching (keyword to google for your problem).

Community
  • 1
Fabrice Marchal
  • 171
  • 2
  • 6
  • Thanks a lot, it's very helpful.(i'll upvoted your answer when i get more reputation's points ;)) – khairy Mar 14 '14 at 09:31