I am interested in getting the closest road segment from a PostGIS database. I am using a GPS module and according to the tolerance it gives longitude and latitude, which are not recorded in the database. How can I find the closest road segment?
More details, I am using OSM (Open Street Maps) stored in a PostGIS database, and I am interested in getting the closest street segment and read its tag i.e. max allowed speed limit.
Hello Dear Saed Tarapiah, you can easily find your answer from below query.
suppose we want to return top 5 nearest roads to the GPS point within 500 meters circle diameter.
SELECT ST_makePOINT( "+longitude+","+latitude+") as gps_point ,
ST_Distance( ST_Closestpoint( st_setSRID(r.geom,1111), st_setSRID(ST_makePOINT( "+longitude+","+latitude+"),1111) ) ,ST_makePOINT( "+longitude+","+latitude+") ,true ) as distance_with_c_p ,
r.length , r.gid ,
FROM network r WHERE (ST_Distance_Spheroid (st_setSRID(r.geom,1111),st_setSRID(ST_makePOINT("+longitude+","+latitude+"),1111),\'SPHEROID[\"XXXXX\",1111111,2222222]\') < 500 )
ORDER BY 2 LIMIT 5;
Please have look on the description of the st_setSRID(), st_distance_spheriod(), st_distance(), and st_closestpoint() to further understand the aforementioned query.
Note: the value of SRID and spheroid are magic number here, you must use values related your's side..
Thank you Aftab,
What kind of osm openstreet map database you use?
For me, I am importing roads.sh (shape file).
Regards
Dear Saed.
You can use the above query in your case.
I used above query via JDBC to connect with postgresql/postgis.
I hope you find your answer in my answer. Let me know if you have more questions related to the aforementioned problem.
thank you dear Aftab,
I imported roads.shp (attached to this message) database,it has the following columns
gid
osm_id
name
ref
type
oneway
bridge
tunnel
maxspeed
geom
my gps coordinates i.e. Latitude/Longitude:31.788431/35.223923
I would like to get the closest node, and get the corresponding maxspeed (column in the database) with geom srid 4326, i am interested to get the closest node within 100 meter.
could you please, provide me the full sql query.
thank you in advance.
regards
SAed
Dear Aftab,
I used this code, and i test it on many points, it gives me correct answers, Ijust worried about this little part of the code, 'SPHEROID["WGS 84",6378137,298.257223563]')
I found it somewhere in the web, it works fine.
please give me any comments on the code.
moreover, I am interested to plot a trip/track (stored in the postGIS database) on the OSM map, offline, based on the shape file locally stored., could you please give me directions how to do so.
regards
SAed
/*UPDATE roads SET geom = ST_SetSRID(geom, 4326) */
SELECT ST_makePOINT( 35.0213443046824, 32.3118572186978) as gps_point ,
ST_Distance( ST_Closestpoint( st_setSRID(r.geom,4326), st_setSRID(ST_makePOINT( 35.0213443046824, 32.3118572186978),4326) ) ,
ST_makePOINT( 35.0213443046824, 32.3118572186978) ,true ) as distance_with_c_p, r.gid ,r.name,r.maxspeed
FROM roads r WHERE (ST_Distance_Spheroid (st_setSRID(r.geom,4326),st_setSRID(ST_makePOINT(35.0213443046824, 32.3118572186978),4326)
, 'SPHEROID["WGS 84",6378137,298.257223563]') < 500 )
ORDER BY 2 LIMIT 5
Hello Dear, your questions:
(1) "I just worried about this little part of the code, 'SPHEROID["WGS 84",6378137,298.257223563]') "
please find correct spheroid of the road database and your gps point (both should have same values).
(2) moreover, I am interested to plot a trip/track
to plot your gps trip on the road network database, it is map-matching problem (hot research direction currently). You need to study of few map-matching algorithms (ST-Matching, Online map-matching based on Hidden Markov model, etc.).
thank you dear Aftab,
how can I find the correct SEPHROID of the road database? and to the gps point, the point will be random in the range of Palestine and Israel geographical coordinates.
regarding plotting the track on OSM map, i found MAPNIK, with postGIS, and PHP. i think this is useful, have you tried it before, and do you have any recommendation regarding the topic?
thank you in advance for corporation
SAed
you must find SEPHROID of the Palestine and Israel geographical coordinates system.
thank you very much, the value which I have used in the code is correct.
'SPHEROID["WGS 84",6378137,298.257223563]'
regards
SAed
- Badges
- Science topic
- Similar topics
- Geoscience
- Geoinformatics
- Geographic Information System