NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum


Hi. First of All. I'm new to OSM.

I ask this question in general form because I don't know where to look for this kind of solution.

I need the list of Cities and Villages that will be along the route from from point A (specified by lat,lon) to the point B (also specified by lat,lon). This list should be in correct order from point A to B.

For now I have a database with all Cities, and Villages in Country with lat,lon coordinates for each entry. I don't have connections between those places in this database. Now I'm wondering how to add to this feature to show me the names of the places on the route from point A to point B (or from one place to other)

Can anybody give me some advice how to develop this kind of feature similar to routing. This feature will be developed for the website that is based on PHP and MySQL only.

asked 13 Dec '11, 12:56

Kapucha's gravatar image

accept rate: 0%

edited 07 Dec '13, 22:44

aseerel4c26's gravatar image

aseerel4c26 ♦


Where is OSM in your question ? the places locations ? the path between them ? Or is it just a developer question about routing algorithmes ?

(13 Dec '11, 16:06) Pieren

Several questions come up in my mind. First of all: do you need an exact route between the two places or just an "as the crow flies" straight line? What is the coverage of your app? Whole world or just a single country?

(13 Dec '11, 16:46) Breki

Where did these cities, villages etc come from? OSM? If so, you need to also get the roads or tracks or rivers or railways or cycleways or whatever your route is using to then calculate the route.

Cities and villages are not a single lon-lat. They cover an area. if your route just passes the lon-lat for a village did it go through the edge of a big village or pass a small one by?

(13 Dec '11, 17:30) ChrisH

@Breki One Country. No straight line. along the public roads. Straight line with some kilometers tolerancy is simple for me. I see that I need to calculate the route along the public roads. Then in some way analyze this route and get the cities and villages near this route in correct order.

(13 Dec '11, 21:53) Kapucha

@ChrisH The data is not from OSM. I have database with all the cities and villages from one country but this is not from OSM. I don't have connections between those places in database. I need to get it from some place because without this I can't calculate the route I assume.

(13 Dec '11, 21:56) Kapucha

If you want details for each step, then this question should be broken into multiple questions. At a high level, here's the approach I would take.

  1. Route from Point A to Point B: See
  2. Convert the route into something that the database will understand. This is probably a Linestring in either WKT or WKB format.
  3. Use the database's spatial functions to find cities/villages along the route. (Perhaps use the Buffer function to expand the LineString into a Polygon, then the Intersects function to find cities/villages -- note that I haven't checked if those are supported in MySQL).
  4. Sort Cities / Villages so that they are in route-wise order. A simple approximate answer would be to sort them by distance from the starting point, under the assumption that most routes don't backtrack. A more robust approach might be to find the closest city/village to the starting point, then find the point on the route that is closest to that city/village, then repeat using that point as the new starting point and excluding cities that have already been visited.

Items 3 and 4 may not be very practical to do with MySQL because its support for spatial data is very limited. If you could use any free, open source database for this, then PostgreSQL + PostGIS extension would be a much better choice. If you're locked into MySQL, then you'll probably need to do the spatial calculations externally to the database, and I'd look for a PHP wrapper of GEOS or JTS (Java Topology Suite).

I'd also recommend trying the GIS StackExchange site for questions that aren't primarily about working with OSM data.

permanent link

answered 17 Dec '11, 17:53

DanHomerick's gravatar image

accept rate: 33%

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 13 Dec '11, 12:56

question was seen: 12,825 times

last updated: 07 Dec '13, 22:44

NOTICE: is no longer in use from 1st March 2024. Please use the OpenStreetMap Community Forum