Hello,

WE have a nominatim database setup on AWS with all the streets in the planet. We are trying to find all shops, museums etc on major streets, for example Oxford Street (London). Does anyone know how this is possible using sql? Which tables i.e placex and others we should run a query on and how we can run joins?

asked 10 Sep '13, 16:08

mezbaur's gravatar image

mezbaur
21112
accept rate: 0%

edited 10 Sep '13, 16:19

2

Hi, then tell us first how a computer or a database should know what street is a major street, and which is not. The general classification on street types are defined at http://wiki.openstreetmap.org/wiki/Key:highway ... but does this help?

Or do you have a manual selection of streets that are in your focus?

(11 Sep '13, 08:26) stephan75

We want to search on primary streets http://wiki.openstreetmap.org/wiki/Tag:highway%3Dprimary for example Oxford Street and bring back all shops on that street.

(11 Sep '13, 11:02) mezbaur
1

next question:

How do you define whether a POI belongs to a primary street or not? Is it enough do determine them via addr-tags? in many aread addr-tags are not complete in OSM data.

Or do you aim at a certain distance? is it then 30 meters or 50 meters or 100 meters from the center of the street line?

So before thinking about a concrete SQL query, be aware how you will find your objects in a logical way. Are you really familiar with the OSM data elements, and how they interact to each other and how not?

(11 Sep '13, 15:34) stephan75

Thanks for making asking some valid questions. I am a newbie on OSM and Nominatim.

(11 Sep '13, 17:52) mezbaur

The main problem is indeed to define what is a major road and what is the POI you are looking for. If you can define that in terms of an OSM tag then you can do a simple self-join on placex using distance between geometries and filtering by class and type.

For example, lets say you want all shops close to a highway=primary in Great Britain, you'd do something like:

select shop.place_id, road.name->'name'
  from placex as road, placex as shop
 where road.class = 'highway' and road.type = 'primary' and road.calculated_country_code = 'gb'
   and shop.class = 'shop'
   and ST_DWithin(road.geometry, shop.geometry, 0.01);

If you want to search for all major roads as defined in Nominatim's details view you can also search by rank:

select shop.place_id, road.name->'name'
  from placex as road, placex as shop
 where road.class = 'highway' and road.rank_search = 26 and road.calculated_country_code = 'gb'
   and shop.class = 'shop'
   and ST_DWithin(road.geometry, shop.geometry, 0.01);

This will even be faster because there is an index over rank_search. (The highway constraint is still necessary so you don't end up with things like airports which are also in this rank.)

permanent link

answered 12 Sep '13, 19:17

lonvia's gravatar image

lonvia
5.1k14976
accept rate: 38%

1

@lonvia, many thanks! I will try it and check the results.

(13 Sep '13, 09:16) mezbaur
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×548
×58
×5

question asked: 10 Sep '13, 16:08

question was seen: 2,318 times

last updated: 13 Sep '13, 09:16

powered by OSQA