Pull to refresh
2.67

OpenStreetMap *

OpenStreetMap is a collaborative project to create a free editable map of the world.

Show first
Rating limit
Level of difficulty

«Divide and Conquer» for OpenStreetMap world inside PostgreSQL

Level of difficulty Medium
Reading time 28 min
Views 1.5K

I will continue the story "How to put the whole world into a regular laptop: PostgreSQL and OpenStreetMap" with secrets about OpenStreetMap geodata, on which many companies have built their business, but not everyone shares the details... Well, today we will open crucial details.

The OSM database in PosgreSQL after loading from the dump takes up more than 587 GB. This is already a large database by the standards of a DBMS, and one huge table for each type of object will not work. For manageability, such data must be partitioned, it's good that PostgreSQL supports declarative data partitioning. It remains only to figure out how to split geographical data. After searching and comparing, the H3 hierarchical hexagonal geospatial indexing system came to rescue. All this was implemented in my openstreetmap_h3 project for fast processing and loading of the world dump into the PostGIS database.

I considered following options from geopartitioning systems...

Read more
Rating 0
Comments 0

How to put the whole world into a regular laptop: PostgreSQL and OpenStreetMap

Level of difficulty Easy
Reading time 12 min
Views 2.4K

When a person used to say that he controls the whole world, he was usually placed in the next room with Napoleon Bonaparte. I hope that these times are in the past and everyone can analyze the geodata of the entire Earth and get answers to their global questions in minutes and seconds. I published Openstreetmap_h3 - my project, which allows you to perform geoanalytics on data from OpenStreetMap in PostGIS or in any query engine that can work with Apache Arrow / Parquet.

First of all, I say hello to the haters and skeptics. What I developed is really unique and solves the problem of transforming and analyzing geodata using the usual and familiar tools available to every analyst and data science specialist without bigdata, GPGPU, FPGA. What looks easy to use and code now is my personal project where I have invested my vacations, weekends, sleepless nights and a lot of personal time over the past 3 years. Maybe I will share the background of the project and the rake that I went through, but first I will still describe the end result.

Read more
Total votes 8: ↑8 and ↓0 +8
Comments 0

Roads and building density in North America. 100GB geodata processing OSM data in PostgreSQL

Reading time 15 min
Views 1.3K

Today I will discover America to you based on OpenStreetMap data in PostgreSQL15/PostGIS and my project openstreetmap_h3. Let's run the query and compare its execution time on the Citus column store in PostgreSQL and on the standard 100GB database partitioned by H3 geoindex.

We will find the top15 buildable locations in North America and the total length of roads, as well as their type and surface. I will not overload the publication with program logs, let's focus on the data! You can easily repeat all requests yourself on your laptop/computer.

Read more
Rating 0
Comments 0

The work on the historical development plan case Vladimir city (Russia)

Reading time 3 min
Views 1K
I am a russian cartographer Alexander Pronin and I am going to tell you how we together with my colleague Nikita Slavin, a map of the age of the houses of the city of Vladimir was created.

Data


For the layer with buildings I used a free map from OpenStreetMap. This layer contains the address, which is divided into columns, names of the objects and numbers of storeys. I collect data on the year of construction, address, name of the object and photos from several sources.

The first one is my project владимирдом.рф It’s a plot that I created to show the residents of my hometown how our city was built up. In this case I took the year of construction data from the website of the Ministry of Housing and Communal Services, for kindergartens and schools — from their official websites. A lot of information was sent also by site users. The map only shows the year of construction.

Read more →
Total votes 3: ↑3 and ↓0 +3
Comments 0

How to write the home address right?

Reading time 16 min
Views 1.3K

How Tax Service, OpenStreetMap, and InterSystems IRIS
could help developers get clean addresses


image
Pieter Brueghel the Younger, Paying the Tax (The Tax Collector), 1640

In my previous article, we just skimmed the surface of objects. Let's continue our reconnaissance. Today's topic is a tough one. It's not quite BIG DATA, but it's still the data not easy to work with: we're talking about fairly large amounts of data. It won't all fit into RAM at once, and some of it won't even fit on the drive (not due to lack of space, but because there's a lot of junk). The name of our subject is FIAS DB: the Federal Information Address System database — the databases of addresses in Russia. The archive is 5.5 GB. And it's a compressed XML file. After extraction, it will be a full 53 GB (set aside 110 GB for extraction). And when you start to parse and convert it, that 110 GB won't be enough. There won't be enough RAM either.
Read more →
Total votes 8: ↑6 and ↓2 +4
Comments 0

Authors' contribution