It's no secret that I love the Raspberry Pi. Earlier this year I wrote about using osm2pgsql on a Raspberry Pi to load OpenStreetMap data to PostgreSQL. Less than a year ago I released the PiWS, an open source weather station based around the Raspberry Pi. In fact, my first test PiWS was setup almost exactly one year ago on a single-core, 512 MB RAM Raspberry Pi Zero W and it is still running today.

Since publishing this post, I have also tested PostgreSQL performance on the Pi. Parts One and Two.

Anyway, the point of this post is to explain why I think the Pi is so awesome, why I continue to chose it over other competing hardware available today, and why I think the challenge of developing for low-power devices is so valuable.

View this post on Instagram

The #raspberrypi looks good when rack mounted! They also do quite a bit of work too! #piws #postgresql #openstreetmap #trackyourgarden

A post shared by Track Your Garden (@trackyourgarden) on Dec 20, 2018 at 6:35pm PST

Continue Reading

I wrote about the PgOSM project last month (GitHub) showing how to transform OpenStreetMap data into a more relational format. The goal of this post is to show how you can easily customize the transformations and reductions to suit your needs. The prior post used a command to load a layer_definitions.sql file (view on GitHub) under the section Deploy schema and define layers.

psql -d pgosm -f ~/git/pgosm/db/data/layer_definitions.sql

Customizing the data loaded in that step is the key to changing how PgOSM transforms, reduces and organizes your OpenStreetMap data. This post shows you how to do that!

This post is outdated. The PgOSM project was replaced by PgOSM Flex. See Better OpenStreetMap data using PgOSM Flex 0.6.0 for a more recent approach.

History of PgOSM

I came up with the core of this project in 2015 over the course of a weekend or two. Since then it has continued to serve my needs reliably, mostly without updates, ready to load fresh OpenStreetMap data any time I asked. Recently I decided to clean up the project and release it under the MIT license. I've always felt like it's an ugly solution, it's far from perfect... but being reliable and customizable counts for a lot!

Continue Reading

I love the Raspberry Pi! It's an affordable little box of "I can do it" Linux-goodness. Though, some tasks are not quite as easy to get right on the Pi, and getting osm2pgsql to run reliably on a Raspberry Pi 3B has been a challenge. This post examines one limitation of the Raspberry Pi system, how this can make osm2pgsql go horribly wrong, and how to configure the system to make it work.

If you are new to osm2pgsql, read my initial post first, I do not repeat the overall process here.

Raspberry Pi = Slow I/O

Update: Most of this complaint is resolved by using application class (A1 or A2) SD cards. The rest is resolved with the introduction of the Raspberry Pi 4.

The main storage of the Raspberry Pi is a micro SD card. While this is great from a cost and availability perspective, the performance of the best SD cards is sub-par for most relational database work. I mention this first, because even when working with more powerful hardware, including faster disks, the main limitation is disk I/O. This is even more of an issue on the Pi. In my previous post I warned:

Postgres and osm2pgsql both use a lot of disk I/O during this process!

Continue Reading

My previous post, Load OpenStreetMap data to PostGIS, covered how to use osm2pgsql to load a sub-region export of OpenStreetMap data into PostGIS (PostgreSQL). With the data loaded to Postgres, one quickly finds out that it isn't very easy to jump in and use right away. To help solve this problem, the PgOSM project was created. The main purpose of PgOSM is to restructure the OpenStreetMap data into a more friendly format for relational databases.

This post starts by showing how the PgOSM project is used to transform our OpenStreetMap data and ends with showing why we do it this way.

Continue Reading

PostGIS rocks, and OpenStreetMap is Maptastic! One challenge I've had with this open source pair, though, has been getting a good, comprehensive set of OSM data into Postgres, on non-enterprise hardware. A few years ago I found Geofabrik's download server... a lifesaver! They offer logical regional exports of OpenStreetMap data, updated daily, distributed by a number of regional levels. The one I use the most is the U.S. state-level files, namely Colorado.

Once I had found this stellar data set (updated daily!) I wanted a way to easily get my own spatial database regularly updated with Colorado. One of the commonly mentioned tools is osm2pgsql. The problem is, I was (and still am) trying to run processes on the smallest hardware possible and this process is not exactly lightweight!

PostgreSQL + PostGIS + OpenStreetMap = 100% Open Source, GIS-filled database!

Continue Reading