User talk:Krauss/Lembretes
Jump to navigation
Jump to search
I am using (this week)
wget http://download.geofabrik.de/south-america/brazil-latest.osm.pbf
osm2pgsql -E 4326 -c -d osm_br -U postgres -W -H localhost \
--slim --hstore --extra-attributes brazil-latest.osm.pbf
and also try some variations... And the database was prepared with PostGIS and hstore... But no variant produced better results.
The relations that I was looking for are all `type=boundary` and all olds (years in OSM and I have fresh download)
- [1](https://www.openstreetmap.org/relation/242390)
- [2](https://www.openstreetmap.org/relation/298216)
- https://www.openstreetmap.org/relation/296625
so I expected to see them at
SELECT osm_id, name, st_area(way,true)/1000000.0 as km2
FROM planet_osm_polygon where osm_id IN (-242390,-296625,-298216);
... But in the old version of osm2pgsql **only** the first is there (!?). And are sothing wrong with *way* (expected is 532.6 km² and returned is 1.2 km²)... Now with new version and most disk, is running.
Municipoios
- select count(*) from planet_osm_polygon where tags->'wikidata' is not null = 8575.
- select count(*) from planet_osm_polygon where tags->'IBGE:GEOCODIGO' is not null = 14706.
select admin_level, boundary, count(*) from planet_osm_polygon where tags->'IBGE:GEOCODIGO' is not null and boundary>'' group by 1,2;
admin_level | boundary | count
-------------+----------------+-------
10 | administrative | 1765
4 | administrative | 30
5 | administrative | 731
7 | administrative | 984
8 | administrative | 6519
9 | administrative | 4214
| administrative | 8
| census | 413
(8 rows)
CREATE VIEW vw_municipios_km2 AS
SELECT tags->'IBGE:GEOCODIGO' id_ibge, tags->'wikidata' id_wikidata, st_geohash(way) geohash, name, round((st_area(way,true)/1000000.0)::numeric,1) km2
from planet_osm_polygon
where tags->'IBGE:GEOCODIGO' is not null and boundary='administrative' and admin_level='8'
;
copy (select * from vw_municipios_km2 order by 1 ) to '/tmp/osm_areas.csv' CSV HEADER;
Distribuição dos Geohashes
select length(geohash) len, count(*) n, round(avg(st_area(way,true))/1000000) km2 from vw_municipios_km2_base group by 1; len | n | km2 -----+------+------ 0 | 117 | 8798 1 | 389 | 5273 2 | 1876 | 2013 3 | 3064 | 528 4 | 361 | 57 5 | 328 | 0 6 | 291 | 0 7 | 85 | 0 8 | 8 | 0
Log
Processing: Node(89942k 271.7k/s) Way(8335k 17.12k/s) Relation(151170 332.24/s) parse time: 1273s Node stats: total(89942954), max(5949698908) in 331s Way stats: total(8335298), max(630023210) in 487s Relation stats: total(151288), max(8767041) in 455s Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Setting up table: planet_osm_nodes Setting up table: planet_osm_ways Setting up table: planet_osm_rels Using built-in tag processing pipeline Setting up table: planet_osm_nodes Setting up table: planet_osm_ways Setting up table: planet_osm_rels Using built-in tag processing pipeline Setting up table: planet_osm_nodes Setting up table: planet_osm_ways Setting up table: planet_osm_rels Using built-in tag processing pipeline Setting up table: planet_osm_nodes Setting up table: planet_osm_ways Setting up table: planet_osm_rels Using built-in tag processing pipeline Going over pending ways... 4564090 ways are pending Using 4 helper-processes Left to process: 146789.... Finished processing 4564090 ways in 692 s 4564090 Pending ways took 692s at a rate of 6595.51/s Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Committing transaction for planet_osm_point Committing transaction for planet_osm_line Committing transaction for planet_osm_polygon Committing transaction for planet_osm_roads Going over pending relations... 0 relations are pending Using 4 helper-processes Finished processing 0 relations in 1 s 0 Pending relations took 1s at a rate of 0.00/s Committing transaction for planet_osm_point WARNING: there is no transaction in progress Committing transaction for planet_osm_line WARNING: there is no transaction in progress Committing transaction for planet_osm_polygon WARNING: there is no transaction in progress Committing transaction for planet_osm_roads WARNING: there is no transaction in progress Committing transaction for planet_osm_point WARNING: there is no transaction in progress Committing transaction for planet_osm_line WARNING: there is no transaction in progress Committing transaction for planet_osm_polygon WARNING: there is no transaction in progress Committing transaction for planet_osm_roads WARNING: there is no transaction in progress Committing transaction for planet_osm_point WARNING: there is no transaction in progress Committing transaction for planet_osm_line WARNING: there is no transaction in progress Committing transaction for planet_osm_polygon WARNING: there is no transaction in progress Committing transaction for planet_osm_roads WARNING: there is no transaction in progress Committing transaction for planet_osm_point WARNING: there is no transaction in progress Committing transaction for planet_osm_line WARNING: there is no transaction in progress Committing transaction for planet_osm_polygon WARNING: there is no transaction in progress Committing transaction for planet_osm_roads WARNING: there is no transaction in progress node cache: stored: 56005886(62.27%), storage efficiency: 53.41% (dense blocks: 1282, sparse nodes: 47178066), hit rate: 64.46% Sorting data and creating indexes for planet_osm_point Sorting data and creating indexes for planet_osm_line Sorting data and creating indexes for planet_osm_polygon Sorting data and creating indexes for planet_osm_roads Copying planet_osm_point to cluster by geometry finished Creating geometry index on planet_osm_point Copying planet_osm_roads to cluster by geometry finished Creating geometry index on planet_osm_roads Creating osm_id index on planet_osm_roads Creating indexes on planet_osm_roads finished All indexes on planet_osm_roads created in 30s Completed planet_osm_roads Stopping table: planet_osm_nodes Stopped table: planet_osm_nodes in 0s Stopping table: planet_osm_ways Building index on table: planet_osm_ways Creating osm_id index on planet_osm_point Creating indexes on planet_osm_point finished All indexes on planet_osm_point created in 64s Completed planet_osm_point Stopping table: planet_osm_rels Building index on table: planet_osm_rels Stopped table: planet_osm_rels in 5s Copying planet_osm_line to cluster by geometry finished Creating geometry index on planet_osm_line Copying planet_osm_polygon to cluster by geometry finished Creating geometry index on planet_osm_polygon Creating osm_id index on planet_osm_line Creating indexes on planet_osm_line finished All indexes on planet_osm_line created in 250s Completed planet_osm_line Creating osm_id index on planet_osm_polygon Creating indexes on planet_osm_polygon finished All indexes on planet_osm_polygon created in 271s Completed planet_osm_polygon Stopped table: planet_osm_ways in 474s Osm2pgsql took 2472s overall