1

I downloaded .osm for state New Hampshire from here. I loaded it into PostgreSQL on Ubuntu 16.04 VB using osm2pgsql. When I use \dt on my db it shows this:

enter image description here

However SELECT COUNT(*) FROM county; returns 0. SELECT COUNT(*) FROM planet_osm_point; returns 55083.

I'd like to get a list of all towns and all streets in those towns. However, there are no counties, nevermind towns!

Also \d+ planet_osm_point shows this;

enter image description here

Then I tried SELECT amenity FROM planet_osm_point LIMIT 10 and it just returns empty space with 10 rows:

enter image description here

EDIT:

Managed to get towns, cities, counties, types of highway. But can't get streets for a given city.

1

osm2pgsql will not create/update/touch/know anything about the tables from the tiger schema. They must come from somewhere else.

For the planet_osm_point query, don't forget to filter results. While all points are saved in this table, not all of them are an amenity

SELECT amenity 
FROM planet_osm_point 
WHERE amenity  IS NOT NULL 
LIMIT 10
  • Good point! I managed to get 10 amenities that way. However, how would I get all the towns and their streets in New Hampshire then? I thought that downloaded .osm file would be enough. I tried SELECT area FROM planet_osm_point WHERE area IS NOT NULL LIMIT 10 but it returned 0 – parsecer Nov 15 '19 at 20:13
  • 1
    OSM tables are "put it all" containers. You should look at which OSM tags you need, and query the tables on these tags – JGH Nov 15 '19 at 20:18
  • I managed to get towns and cities, however I still struggle with roads. Selecting the towns WHERE highway IS NOT NULL returned null. So when it's my town, highway is empty. I tried to SELECT area, highway FROM planet_osm_line where highway is not null and area='Acworth'; to get highways in Acworth city, but it returns 0 rows. – parsecer Nov 15 '19 at 22:27
1

To future readers who might struggle with the same problem, here are the queries that have helped me:

How to get all the streets and their postal codes from a given state (New Hampshire) that has been loaded inside PostgreSQL:

1. To get the list of all counties in NH:

SELECT name FROM planet_osm_point WHERE place='county';

returns 10 rows, which is correct as NH has 10 counties.

2. To get a list of all cities:

SELECT name FROM planet_osm_point where place='city';

returns 13 rows which is correct as NH has 221 towns and 13 cities

3. To get a list of all towns:

SELECT name FROM planet_osm_point where place='town';

returns 45 which is not correct, since we need 221 town.

4. To get a list of all possible values for place:

SELECT DISTINCT place FROM planet_osm_point;

enter image description here

returns 17 rows with unique values. The remaining towns must be somewhere among those other place tags.

5. Then I put all the cities and towns (234 in total) from Wikipedia into a .txt file and got all the rows where name is inside that .txt file of mine (as described in this answer:

The number of unique cities/towns that are in my Wikipedia's list:

SELECT DISTINCT  COUNT(*) FROM planet_osm_point 
WHERE (SELECT COUNT(*) FROM citiestowns 
WHERE citiestowns.name=planet_osm_point.name)>0 AND place is not null;

returned count=236. So there must be 2 extra rows (not sure how it's possible, though).

The table with names and places of those cities:

SELECT DISTINCT  name, place FROM planet_osm_point 
WHERE (SELECT COUNT(*) FROM citiestowns 
WHERE citiestowns.name=planet_osm_point.name)>0 AND place is not null order by name asc;

enter image description here

returns a list that looks same as the one from Wikipedia.

All possible values of place in the previous table:

SELECT place, COUNT(place) FROM planet_osm_point 
WHERE (SELECT COUNT(*) FROM citiestowns 
WHERE citiestowns.name=planet_osm_point.name)>0 
AND place is not null GROUP BY place ORDER BY count DESC ;

returned

enter image description here

so, OSM tagged most of the remaining towns as villages. Btw, what in OSM was tagged is neighbourhood is a Populated places in Wikipedia's table.

6. Getting streets for retrieved towns/cities. Still not sure how to do it

all possible values for highway:

 SELECT DISTINCT highway FROM planet_osm_line;

enter image description here

SELECT area, highway FROM planet_osm_line where highway is not null and area='Acworth';  

returns zero rows

7. Getting postal codes of all the streets in 234 cities/towns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.