Create a European city map with population density


- Urban morphological zones 2000 (EU):
- Population count (World):
- Administrative regions (World):

The map is European since the "urban" data from the European Environmental Agency (EEA) only covers Europe.


The UMZ data ended up in PostGIS with srid 900914. You can use to convert the contents of a .prj file to an estimated SRID code. In this case the UMZ .prj file as the contents:


Which translates to 3035 - ETRS89_LAEA_Europe.

How to create a world-wide PostgreSQL database of administrative regions

The GADM database contains geographical data for administrative regions, e.g. countries, regions and municipalities. As always, once you have the data in the right format, it is easy to import it into a database. The data is available from GADM in several formats. All data has the coordinate reference system in longitude/latitude and theWGS84 datum.


  1. Download data for the whole world or by country. For a change, I will use the GeoPackage format.
  2. Create a PostgreSQL database (assumed to exist)
  3. Import the data with ogr2ogr (see instructions below)

Import data instructions

Download data (example for Denmark):


Next, create a database called "gadm" on my local PostgreSQL server; of course you can use another name if you prefer. Install the PostGIS extension:

create extension postgis

Finally, use ogr2ogr with the GPKG (GeoPackage) driver to import the data:

ogr2ogr -f PostgreSQL "PG:dbname=gadm" DNK_adm.gpkg

Now the data is imported an ready to be queried.

As a test, we can query the adm2 table (municipalities) with a coordinate inside the municipality of Copenhagen, Denmark.

SELECT name_2, ST_AsText(wkb_geometry)
FROM dnk_adm2
WHERE ST_Intersects(ST_SetSRID(ST_Point(12.563585, 55.690628), 4326), wkb_geometry)
-- AND ST_Point(12.563585, 55.690628) && wkb_geometry

You can view the selected well-known string geometry (WKT) in an online viewer, such as openstreetmap-wkt-playground. Other viewers are listed on stackexchange.

Alternative sources

For this post I really wanted a dataset of populated/urban areas. However, the GADM data I downloaded only contains adm0-adm2, which is a tessellation of the land area, i.e. cannot be used to discriminate between urban and rural areas.

Other data sources are listed below:


From the rtwilson list, here are some specific datasets that indicate population density and urbanism:

- (does not cover Europe and North America)

How to assess computers on your local area network

I teach children how to programm and do other things with technology in an organisation called Coding Pirates in Denmark, which aims to be a kind of scout movement for geeks. A best seller among the kids is learning how to hack and I see this as a unique opportunity to convey some basic human values in relation to something that can be potentially harmful.

Yesterday, I and one of the kids played with nmap, the network surveying tool, to investigate our local area network. The aim was to find information about the computers that were attached, such as operating system, system owner's first name (often part of the computer name) and whether any computer had open server ports (SSH, web etc.). We used nmap in combination with Wireshark.

  1. Tell another person about a fun website (any website will do)
  2. Use wireshark to detect the IP address (e.g. of any computer that accesses that website
  3. Use nmap to scan the IP address we found: nmap -vS

We also learned how to detect that someone logs into your computer and e.g. kick the person (assume an Ubuntu host):

# Monitor login attempts
tail -f /var/log/auth.log
# See active sessions
# List remote sessions
ps fax | grep 'pts/'
# Kill sessions
kill -9 [pid of bash processes connected to session]

Other tricks

List all hosts (ping scan) on your local area network:

nmap -sP 192.168.1.*

Find computers on your local area network that run an SSH server:

nmap -p 22 --open -sV 192.168.1.*

Apache Zeppelin (incubator) rocks!

At Spark Summit Europe 2015, several presenters made use of Apache Zeppeling, which is a notebook (a la IPython) for Spark.

I immediately wanted to try it out myself. I also highly recommend you to download and try it out if you like Spark. But one note: download Zeppelin from GitHub rather than from the apache homepage. The GitHub one is significantly more up to date (today). You do not need to preinstall Spark (but you can if you want), because Zeppelin comes with a stand-alone installation of Spark.

How long is the Doom Loop cycle currently?

Take a look at this Chomsky presentation, time it around 46:30. It seems that the most rational prediction would be that we are heading for another financial crisis, since financial systems are running a quote "Doom Loop": Make huge gambles, make huge gains or fail. In the case of failure, get bailed out. This pattern of behaviour is rational, seen from the point of view of the financial sector, given the current environment. So, the good question is, what would the rational course of action be for us, the citizens, given that the financial sector is apparently acting, fully rationally, inside a Doom Loop?

The rational question would be, when is the next financial crisis coming? Given a good prediction of this point in time, how should we rationally act, e.g. in the real-estate market? If we should aspire to make rational decisions, we should not hope that another financial crisis will be avoided. We should expect it, and make rational decisions based upon it. For our own gain, if we so desire. Now, how do you do that? That is another question. It seems obvious that decisions in many areas should be influenced by this apparent fact, e.g. decisions in real-estate, entrepreneurship, family planning. If there is money to be made, somehow, in betting on the next financial crisis, maybe that would be the rational thing to do.

The purpose of language by Chomsky

In the following Google video, Noam Chomsky raises and answers the interesting question: what amazing insights into language have linguistics revealed, which the public does not know about?.

He answers that human natural language was propably developed to support the human thinking process, not to serve as a means of communication. He believes that language might have evolved long before it was first used for communication. He goes as far as saying that the design of human natural language makes it unfit for communication.

I find his language-is-for-thinking point is very interesting. I'm currently finishing a PhD, and it would explain the difficulties I sometimes have when trying to convert between language for thinking into language for communicating my thoughts. There is even a phd-comic about it.

As very often with Chomsky, the talk weaves in and out between political and linguistic topics. Interestingly enough, he does not shy away from mentioning and criticizing Google's part in state oppression through cooperation with NSA. That might seem like a breach of some sort of social etiquette, however, he was strongly encouraged to "speak truth to power" by the person introducing him. Be careful what you ask for.

What Goes Around Comes Around

Today I read the What Goes Around Comes Around chapter from the "Red Book" by Michael Stonebraker and Joseph M. Hellerstein. The chapter (or paper if you will) is a summary of 35 years of data model proposals, grouped into 9 different eras. This post is a kind of cheat sheet to the lessons learned in the chapter.

The paper surveyed three decades of data model thinking. It is clear that we have come "full circle". We started off with a complex data model (Hierarchical/Network model), which was followed by a great debate between a complex model and a much simpler one (Relational model). The simpler one was shown to be advantageous in terms of understandability and its ability to support data independence.

Then, a substantial collection of additions were proposed, none of which gained substantial market traction, largely because they failed to offer substantial leverage in exchange for the increased complexity. The only ideas that got market traction were user-defined functions (Object-Relational model) and user-defined access methods (Object-Relational model), and these were performance constructs not data model constructs. The current proposal is now a superset of the union of all previous proposals. I.e. we have navigated a full circle.

Hierarchical Data Model (IMS)

Late 1960's and 1970's

  • Lesson 1: Physical and logical data independence are highly desirable
  • Lesson 2: Tree structured data models are very restrictive
  • Lesson 3: It is a challenge to provide sophisticated logical reorganizations of tree structured data
  • Lesson 4: A record-at-a-time user interface forces the programmer to do manual query optimization, and this is often hard. (Key-Value stores anyone?)

Network Data Model (CODASYL)


  • Lesson 5: Networks are more flexible than hierarchies but more complex
  • Lesson 6: Loading and recovering networks is more complex than hierarchies

Relational Data Model

1970's and early 1980's

  • Lesson 7: Set-a-time languages are good, regardless of the data model, since they offer much improved physical data independence
  • Lesson 8: Logical data independence is easier with a simple data model than with a
    complex one
  • Lesson 9: Technical debates are usually settled by the elephants of the marketplace, and often for reasons that have little to do with the technology (Key-Value stores anyone?)
  • Lesson 10: Query optimizers can beat all but the best record-at-a-time DBMS application programmers (Key-Value stores anyone?)

Entity-Relationship Data Model


  • Lesson 11: Functional dependencies are too difficult for mere mortals to understand

Extended Relational Data Model


  • Lesson 12: Unless there is a big performance or functionality advantage, new constructs will go nowhere

Semantic Data Model

Late 1970's and 1980's Innovation: classes, multiple inheritance.

No lessons learned, but the model failed for the same reasons as the Extended Relational Data Model.

Object-oriented: late 1980’s and early 1990's

Beginning in the mid 1980's there was a "tidal wave" of interest in Object-oriented DBMSs (OODB). Basically, this community pointed to an "impedance mismatch" between relational data bases and languages like C++.

Impedance mismatch: In practice, relational data bases had their own naming systems, their own data type systems, and their own conventions for returning data as a result of a query. Whatever programming language was used alongside a relational data base also had its own version of all of these facilities. Hence, to bind an application to the data base required a conversion from "programming language speak" to "data base speak" and back. This
was like "gluing an apple onto a pancake", and was the reason for the so-called impedance mismatch.

  • Lesson 13: Packages will not sell to users unless they are in "major pain"
  • Lesson 14: Persistent languages will go nowhere without the support of the programming language community


Late 1980's and early 1990's

The Object-Relational (OR) era was motivated by the need to index and query geographical data (using e.g. an R-tree access method), since two dimensional search is not supported by existing B-tree access methods.

As a result, the OR proposal added:

  • user-defined data types
  • user-defined operators
  • user-defined functions
  • user-defined access methods
  • Lesson 14: The major benefits of OR is two-fold: putting code in the data base (and thereby bluring the distinction between code and data) and user-defined access methods
  • Lesson 15: Widespread adoption of new technology requires either standards and/or an elephant pushing hard

Semi-structured (XML)

Late 1990's to the present

There are two basic points that this class of work exemplifies: (1) schema last and (2) complex network-oriented data model.

  • Lesson 16: Schema-last is a probably a niche market
  • Lesson 17: XQuery is pretty much OR SQL with a different syntax
  • Lesson 18: XML will not solve the semantic heterogeneity either inside or outside the enterprise