Mapnik, osmosis, MongoDB bbox queries

Recently I’ve done a few tests querying data (POIs, Points of Interest) from Open Street Map. I imported the data from OSM into different databases and formats and I wanted to query it.

I’ll show 3 queries: from MongoDB, from PostgreSQL (tables using Osmosis format), from PostgreSQL (tables using osm2psql format).

POIs from MongoDB

I found this one the easiest query to write. I imported the data using the steps described on Derick Rethan’s blogpost (see his Github repository, I used the command:
php5 ./3angle/import-data.php localhost spain-latest.osm to import it.

To query all the POIs for a given bounding box I use:

query = {"l": {"$geoWithin": { "$box": [ [ southwest_lng, southwest_lat], [northeast_lng, northeast_lat] ] } } }
pois = list(pois.find(query))

(replace southwest_lng and the other variables by their positions such as -0.09546518325805664,51.50291256180409,-0.08447885513305664,51.5070930676199 for a small part of London).

This was easy and I liked MongoDB and their GIS (geographical queries) support. It also was my first contact with MongoDB.

POIs from PostgreSQL, Osmosis format

I used osmosis to import data from OSM into PostgreSQL.

The query to get all the POIs for a given bounding box, in my case, is:
SELECT id, tags -> 'amenity' AS amenity, ST_Y(geom) as latitude, ST_X(geom) AS longitude FROM nodes WHERE ST_Intersects(geom, ST_MakeEnvelope(%(southwest_lng)s,%(southwest_lat)s,%(northeast_lng)s,%(northeast_lat)s,4326))

(replace %(southwest_lng)s and the variables by their values, see MongoDB for an example)

Notice that a “\d nodes” (describe nodes) table returns:

tags | hstore |
geom | geometry(Point,4326) |

“tags” column type is hstore (it’s a key-value column). The “geom” type is geometry(Point,4326). 4326 is standard coordinate system for the Earth (see World Geodetic System Wikipedia’s entry).

In this case, the query returns all nodes which geometry (just the position, in this case they are always points) intersects with the given box created using 4326 standard. It’s important that the ST_Intersects projection (4326) matches the projection defined in the column. Forgetting the standard (4326) in the ST_Intersects causes the query to run but not to get the expected results (having some sort of visual validation is important: I thought that the query was working because it returned results… but it wasn’t the case).

POIs from PostgreSQL, osm2psql format

You could import OSM data into PostgreSQL using osm2psql. This format is usually used by Mapnik to render the maps.

In this case the query is:
SELECT osm_id AS id, amenity, ST_Y(ST_Transform(way,4326)) AS latitude, ST_X(ST_Transform(way,4326)) as longitude FROM planet_osm_point WHERE ST_Intersects(way, ST_Transform(ST_MakeEnvelope(%(southwest_lng)s,%(southwest_lat)s,%(northeast_lng)s,%(northeast_lat)s,4326), 900913))

The planet_osm_point_index table contains the position (way column) as:

way | geometry(Point,900913) |

It’s not using the projection 4326 but the projection 900913 (also known as Web Mercator. Wikipedia recognizes that it’s a confusing denomination, and I would like to add that the fact that in PostgreSQL the projections are just numbers instead of names is even more confusing.

In this case the query creates a ST_MakeEnvelope (a bounding box) using the location (using the projection 4326 because my client passes it using this format). After that the function ST_Transform transforms this into the projection 900913 because it’s the one saved in the database (otherwise we might not get the correct results). And, like I did before, the query checks that the POIs are in the bounding box (envelope).

To return the data: it gets the position (way) which converts to the desired projection (ST_Transform) and then returns the latitude or longitude (using ST_X, ST_Y).

I found the PostgreSQL quite confusing because the naming of the functions and a new concept for me was the different projections: if one is not careful they can be easily mixed.

I was surprised that I couldn’t find on the Internet any example queries like these ones: it seems that getting POIs from a bounding box should be the “Hello World!” for OSM and PostgreSQL. So, in this blog post, I hope to fix The Internet adding the missing information.

Foreign travel advice: Spain

A few months ago I was researching how safe it is to travel to a few countries. I used the Foreign Travel Advice webpage from the British government.

I thought to do a bit of “quality control” of their information. What do they say about countries that I know well?

So I went to read what they say for Spain. I could find that in Spain “There is a general threat from terrorism.” (this helps to understand what they mean by “general threat from terrorism” when they say this about other countries). I also learnt that a nurse in Spain tested positive for Ebola (they really update the information often).

But what it surprised me the most is the explicit warning that “balconing” is risky. It says:
“There have been several deaths as a result of falls from balconies. Don’t take any unnecessary risks, especially when under the influence of drink or drugs. See Balcony falls”. It’s so important that it links to a specific section balconing falls which adds more information:

“There have been a number of very serious accidents (some fatal) as a result of falls from balconies. Many of these incidents have been caused by British nationals being under the influence of drink or drugs and most should have been avoidable. Your travel insurance probably won’t cover you for incidents that take place while you’re under the influence of drink or drugs.

Some local councils have introduced laws banning the misuse of balconies with fines for those who are caught.

I’m sure that all the balconing accidents were avoidable!

I’m surprised that this needs to be written there but on the other hand every year a few people die because they jump from a balcony to the swimming pool (usually drunk people). I’m not sure how effective the warning will.

One more public announcement: if you don’t play the lottery and someone sends you an email to say that you won the lottery but that you have to pay some costs: very likely that it’s a scam! (and not only in Spain):

There have been reports of lottery scams in Spain. A person receives what appears to be official notification from the Spanish Inland Revenue office (Hacienda) that they’ve won the Spanish lottery and should deposit money in a bank account to receive their winnings. It’s likely to be a scam if you haven’t entered a lottery, you’re asked to pay anything up-front and the contact telephone number is for a mobile phone.”

Some time ago I did a similar exercise: I read what the Spanish Foreign Travel Advice says about the United Kingdom. Well, I have to say that after reading that text it seems that Oxford Street is one of the most dangerous places in the world!

Llibre: Los Últimos Libres

Fa poc que he acabat el llibre “Los Últimos Libres” de Victor M. Valenzuela. Es pot comprar en format epub o en format paper.

M’ha agradat molt! L’autor és enginyer informàtic, el llibre és fàcil de llegir i m’ha enganxat molt. Es pot llegir en pocs dies ja que és curt i fàcil de llegir.

Si podeu done-li una oportunitat, a veure si us agrada. Va d’una parella que són informàtics i usen programari lliure.

El llibre està situat en un “futur proper” i m’ha agradat com barreja coses que passen avui dia, coses que podrien passar i coses que són més ficció. I tot té prou sentit.

London LOOP finished

Last June I mentioned in another blog post: on the last weekend of April I started the London LOOP (London Outer Orbital Circle).

In the first weekend of October I finished it! 240 Km, 15 stages (officially 23 stages but I followed a book that divided it into 15 stages).

The London LOOP has been my first hike in multiple stages. It visits different parts of Greater London. I started in spring and finished in autumn: I saw the changes in nature (especially the starting/ending point… where I was 5 months later).

Many people have asked about my favourite stage. I’m not sure which is my favourite stage… They are so different! To mention a few random good memories:

  • I walked part of the Grand Union Canal (in Uxbridge). A friend lives there and he joined me on 2 stages
  • I started (and finished!) in Cockfosters: where the Piccadilly Line trains that I use come from or go to
  • I walked through corn fields (yes, in London, just 20 minutes from an underground station there are corn fields)
  • I saw amazing views of London (from South London)
  • I walked in nice woods (I like woods!)
  • A few friends joined me on the way (thanks to all of them!)
  • I walked 20 Km. and finished near a house warming party of a friend… what a good coincidence!
  • I met Derick Rethans: a mapper from Open Street Map that we discovered on IRC that we could meet easily
  • I walked just next to Heathrow airport seeing and hearing the planes (did you know that there is a nice wood just next to Heathrow with a river?)
  • I got really exhausted in a few stages… which was rewarding
  • I went to parts of London that I wouldn’t visit otherwise: I like London and I like to discover unknown parts of London
  • I could see the Queen Elizabeth bridge from far away
  • I crossed Greenwich Meridian twice
  • I closed a circle: arriving to the same point 5 months later

The official stage number 1 (it wasn’t the first stage for me): it’s nice because I arrived

I did the London LOOP in an unusual direction: instead of anti-clockwise I did clockwise. I started at Cockfosters because it's handy to go from my home. And I thought that I would walk to the west: to perhaps visit a friend in Uxbridge after a few stages and then perhaps the Heathrow airport... and I liked it so much that I did it all!

Because I did the unusual direction I could say "hi" to a few London Loopers and it was easier to meet with Derick as I mentioned before.

Sadly the directions from the book were not useful for me (the book is written to walk anti-clockwise) but using the mobile app OruxMaps, the GPS track from Open Street Map and also the sign posts this wasn't a problem.

And guess what? I wanted a break of hiking in loops but I have started the Capital Ring. It’s a similar idea to London LOOP but smaller: closer to the city, shorter stages, but it seems that it goes through interesting bits of London too. So far I like it.

So, if you want: have a look at London LOOP. My favourite resources were Luphen’s webpage and Waymarked Trails.

Python UK Conference 2014: very personal point of view

Like in 2013 I attended the Pycon-UK conference in Coventry. Amazing conference, I went to many talks with different topics: Python itself, high performance Python, testing software, some more social talks, the always interesting lightning talks, etc.

I could write about many of the talks but some people are already doing it in the PostConf Wiki page.

For the second year the conference happens on the Open House London weekend (which is not great, since I have to miss it) and the conference, for second year, is on my birthday.

Last year I thought “oh no, the conference on my birthday?!”. But actually I’m getting used to it, and it’s not too bad.

I spend the birthday with some friends who come to the conference… and many other Python developers, all of them are nice. In a way, I consider the Python-UK my very personal birthday present that I buy for myself.

I also buy some chocolates to enjoy with friends. Next year I might get some more sweets to share more widely after the dinner, this year I didn’t have enough I think.

I would like to say thank you to tall the organizers, sponsors and also the delegates themselves to make this conference quite special! The fact that the schedule is on a Wiki and organized partially on the Wiki shows how open it is.

Anyway, next confirmed conference: FOSDEM!

London-Barcelona metro unexpected differences

There are many webpages talking about the London Underground. The oldest in the world, very iconical, unused stations, etc.

Here I just want to compare a few things between the London Underground and Barcelona metro.

One of the subtle differences is the escalator speed. Yes, the escalators in the London Underground are noticeably faster than the Barcelona ones. One effect is that now when I’m in Barcelona and I use an escalator I lose my balance because I expect it to be faster. I didn’t notice this when I moved to London.

Another different thing is the train frequency. In London it is much higher than in Barcelona. When I’m in Barcelona if I go to the metro and I see “6 minutes” the first thing that I think is “ohhhhhh noooooo!”. In London, in zone 1 or 2 it’s quite unusual to wait for more than 5 minutes, usually it’s 2 or 3 minutes. More than 5 minutes usually indicates that a problem has occurred (this is only usual further away from the centre).

And the last thing that I wanted to mention here is the distance between stations. In London, outside zone 1, the distance between stations is much bigger than in Barcelona. In Zone 1 the stations are ridiculously close but in zone 3 or zone 4 some stations are quite far away. One consequence is that walking between stations might take too long. Another one and with bigger impact: when there is a lot of distance trains must leave a station before the train ahead arrives at the next station. If the train ahead has a problem (a broken door, someone stuck on the door, someone needs help because feeling unwell, etc.) the train behind must wait until the issue is resolved… and the train behind must wait in the tunnel which is quite stressful.

In Barcelona because the stations are closer and the trains don’t run so frequently the trains usually don’t leave if they can’t make it to the next station, or at least this is what I’ve observed in Barcelona in the zones that I use.

If you like this topic you might this other external blog post. Since living in London I’ve also noticed quite a few things from that blogpost.

Barcelona Super Computing Centre: Mare Nostrum visits

Last month I went to Barcelona and I visited Mare Nostrum: a supercomputer in Barcelona.

For information about the Mare Nostrum you could read the Wikipedia (obviously Dr. Watson), the Barcelona Supercomputer Centre webpage (or see the photo gallery). They even have a Youtube channel with interesting videos.

Mare Nostrum is inside a building that it used to be a chapel (this is very unique among the super computers). The guided tour last a bit more than an hour. It consist on a video, see the super computer, explain a few facts, questions and answers to the engineer, another video and then there is a small museum of previous Mare Nostrum computers.

If you are or go to Barcelona it’s really interesting and worth the visit (if you are into computers).

In order to request a guided tour please visit the Visits to the BSC webpage. Enjoy!

Geek music

Helen Arney, during one of the Festival of the Spoke Nerd, sang this song:

She did amazingly, as always! I’ve talked about her here in Pintant (with a video or the Festival of the Spoken Nerd).

And another song worth a mention:

Prime numbers have always been special for me. My second webpage, in 1996, was Prime Numbers.

Recipe for a bad programming day

Do you want to have an non-productive and bad programming day? I can help you! Keep reading, be my guest. Everything will go bad, don’t worry!

First of all, and for this particular example: you need a bug. A non trivial one and a bit obscure is better. Let’s say that to test if the bug it’s fixed you need to do a few steps involving the mouse.

Recipe for the bad day:

  1. Ignore your existing unit tests. Don’t even compile them
  2. Don’t add a new unit test to reproduce the bug. Anyway, you have some steps (more or less) for this, right?
  3. Don’t think of the root of the problem. Just use the debugger and change some code here and there (try changing multiple things at the same time)
  4. Don’t think of the consequences of your changes. Just change it
  5. Use the staging servers for the testing, that ones that they are being changed too. Don’t write fake/mockups/double classes for the tests

When you are believe that everything is working: run the existing unit tests. See how a few of them fails. Start again. You can spend a full day or even more.

When tired of this programming by coincidence try a better approach:

  1. Step back and think of: what’s the root of the problem? Ask yourself “Why” a few times, not only once (5 whys?)
  2. Write a unit test that fails because this bug (this will help to understand the problem, possibly with an Eureka moment where you will see a very simple fix)
  3. Fix the problem

I still wonder why I tried the incorrect approach that day.

Telegram, Whatsapp and APIs

At Mendeley we are working on a new API and the documentation is already available at We think that a good API is important to promote Mendeley, create an ecosystem, help niche use cases that we could not tackle, etc.

Since January I have been using Whatsapp. It helps me to keep in contact with some friends and specially with my family. I also installed Telegram which I’ve not been using until recently when some friends from Catux Linux User Group installed it and we created a group on Telegram.

I always wished that I could use Whatsapp on the computer but this is not possible. There are no Whatsapp clients (Web or desktop applications). Whatsapp developers don’t create them, and there is no API to create them. I don’t much like typing on the mobile and many times when I use Whatsapp I have my laptop with a better keyboard just next to me.

I knew that Telegram had a public API. And yes, there is a good desktop application that also runs on Linux. So far I’ve used Telegram Desktop with great success: no problem to install or use. A friend tried the Web Telegram app and he is happy too.

Typing (swiping) on the mobile while I have a good keyboard next to me makes me sad. Now on Telegram I can use the good keyboard if I wish. Or the small.

Telegram has a few interesting features that are not available on Whatsapp: private chats, setup the notifications per group (instead of all groups), the aforementioned API, etc.

I see that some people use WeChat. This one has a Web interface too. I don’t think that it has a public API.

Seems that Whatsapp is very popular in Spain and in a few countries but other applications have better features and almost the same user interface. As always, it’s very difficult to change social applications usage.