Issue 62

Quality Assurance process on turn restrictions using SQL

Simona Pop
Map Analyst @ Telenav


There are multiple automotive embedded systems one of which is the car navigation system. OpenStreetMap (OSM)-based GPS navigation apps are increasingly used as OSM is the only crowd-sourced and open-sourced global map. OSM is constantly improved by the worldwide OSM users, and by the map-analysts at Telenav. Therefore, the OSM permanently catches up with the changes from reality. Even though there are several map features that can be used when mapping, there are some tags specific to routing. By way of illustration, turn-restrictions, oneway roads, road name, signposts or road geometry are examples of map features that can influence routing.

If we want the routing software to work well, the underlying OSM data must be of high quality. This means that the map features within the OSM that greatly influence routing must be correctly added by the map-analysts team. To put it more concretely, the highway that can only be used in one direction should be tagged as one-way, turn restrictions should be correctly mapped, the road geometry should be in line with reality and so on. The quality of these map features is assured by a Quality Assurance (QA) process in which multiple tools are used. One of them, pgAdmin, allows us to run SQL queries in order to check turn restrictions, for example. We can check if they have all the members that build the relation or if the time-based turn restrictions have been correctly added.

Queries for turn restrictions

Because of the large number of users and edits, it is inevitable for some map features to remain unbroken. Some turn restrictions have been added with more or fewer members than they must have, so we need to correct them to have the right structure. A correct restriction must have 3 members: a way from which restriction starts (from), a node, a way or multiple ways that connect the beginning and ending of the restriction (via) and a way where the restriction ends (to). We wrote a query which selects all the ways that have turn restrictions and took out those which have 3 members, remaining only those with odd numbers. After that, we created a table with these restrictions, we selected all relation_ids and downloaded them as .csv file. The relation_ids we selected can be copied from the created file and downloaded in the Java OpenStreetMap editor (JOSM) using File-Download object (Fig.3). Just make sure you select Object type: relation (Fig.4).

Fig.1 Identifying restrictions with odd number

Fig.2 Selecting relation_ids in pgAdmin of members

Fig.3 Downloading the identified restrictions in JOSM

There are some tags which are not used in OSM anymore and they have not been changed to the new structure. We need to change their format, so the application can process them. Let's take conditional turn restrictions for example. A conditional turn restriction has a more complex structure than a usual restriction because of its defined period of time. During these periods, a specific maneuver is strictly forbidden. We have a query for conditional restrictions to select all those which have been added with the old structure (Fig.5). After creating the table, we can extract them from the database, and finally correct them in JOSM (Java OpenStreetMap) (Fig.6).

Fig.4 Query for identifying conditional turn restrictions with old format

Fig.5 Old format

Fig.6 New format

To conclude, SQL queries are very efficient in identifying different errors regarding turn restrictions. They are efficient because we can query a large database and obtain the results in a relatively short time, compared to other methods.



  • Accenture
  • BT Code Crafters
  • Accesa
  • Bosch
  • Betfair
  • MHP
  • Connatix
  • BoatyardX
  • .msg systems
  • Yardi
  • Colors in projects