Convert Longitude-Latitude to a flat map using Python and PostGIS

I've recently had to develop a web app that shows Tweets locations on a map. It's simple enough to extract a tweet's location (when present), just check the API Docs for a Tweet object and you'll find a coordinates field that reportedly:

Represents the geographic location of this Tweet as reported by the user or client application. The inner coordinates array is formatted as geoJSON (longitude first, then latitude).

The next step is to visualize it on a flat map with the widely accepted Mercator projection. There are a few useful references on StackOverflow and Wolfram that gave me the hints to write these simple python functions:

import math

def get_x(width, lng):
    return int(round(math.fmod((width * (180.0 + lng) / 360.0), (1.5 * width))))

def get_y(width, height, lat):
    lat_rad = lat * math.pi / 180.0
    merc = 0.5 * math.log( (1 + math.sin(lat_rad)) / (1 - math.sin(lat_rad)) )
    return int(round((height / 2) - (width * merc / (2 * math.pi))))

where width and height are the size in pixels of the flat projection. The formula works fine, translating the reference from Wolfram to the get_y function was simple enough, but the reason behind some details of the function found on StackOverflow (e.g. multiplying width by 1.5) seemed a bit arbitrary to me and I was too lazy to find the answers.

Turns out my Postgresql database also has PostGIS extensions installed, so I've decided to put them at use. I found that what we usually simply call lng-lat has a formal definition with the standard WGS84, this mapping to PostGIS' spatial reference id.4326. On the other hand, the Mercator Projection is also a standard transformation known as EPSG:3785 mapping to PostGIS id.3785 (same id, thank god).

It's then possible to transform a WGS84 reference to EPSG:3785 by calling PostGIS functions directly in the SQL query:

    ST_X(ST_Transform(ST_SetSRID(ST_Point(t.longitude, t.latitude),4326),3785)) as x,
    ST_Y(ST_Transform(ST_SetSRID(ST_Point(t.longitude, t.latitude),4326),3785)) as y,
from tweet as t

nice! just be aware that transforming lng-lat to EPSG:3785 returns points where the axis origin is at the centre of the map, and boundaries are defined by the standard as -20037508.3428, -19971868.8804, 20037508.3428, 19971868.8804. It's simple to translate the origin of axis to the top left corner and normalize the size in pixels to obtain the same results of the Python function.

uh, one last thing I never managed to permanently store in my brain: LONGITUDE is the X on the map, while LATITUDE is the Y. For me it's easier to remember by visualizing the equivalence X-Y -> LNG-LAT.


Moving my website to Github

I'm moving all my content out of my previous wordpress to host it on github pages with Jekyll. Everything is still a bit broken, but all the content is here. I don't intend to spend time setting up 301 codes for the few posts I have on my old blog, but hopefully the permalink structure should be the same. If you happen to search anything in particular and cannot find it, please contact me via a twitter mention @grudelsud

here a few links I found quite useful while moving things around and getting aquainted with Jekyll:

DevArt wins a Webby for Technical Achievement

Pretty proud to have worked on this project as technical lead and backend developer, DevArt wins a webby for "technical achievement". As described by the organization:

This category is for any Site using new, innovative, Web technology in an outstanding way that improves the overall user experience.

here's a link to the award

Back home after PyCon6

Back from #PyCon6 where I gave a talk called "Art & Music VS AppEngine", where I showed how we implemented some performance enhancement tricks on GAE to make it work smoothly on content intensive projects we developed along with our fellows at Google Creative Lab London. Here the slides for the talk:

Also, I followed a few talks and took notes for future reference, here's my recap (made with reveal.js and published here):

Thank you again to all the crew, you guys have been amazing: venue, food, schedule were absolutely spot on. Bring it on and looking forward to coming over for #PyCon7!

Spero di essere ancora così a 60 anni...


Viaggiare soli per 3000km con il rumore del vento a 2cm dalle orecchie da molto tempo per pensare: si spazia dalle idee su come rifare la cucina all'eventualità di avere un secondo figlio, passando dalle ricette preferite per cucinare il coniglio e le possibilità per il prossimo lavoro.

Confesso che è piuttosto faticoso - sarà colpa del vento che agita tutti questi pensieri oltre a volerti strappare le braccia ancorate al manubrio - e sedersi su una panchina a fine giornata per bere una birra e osservare il tramonto sembra un'enorme ricompensa.

Ci sono momenti in cui ci si ferma anche durante il percorso ovviamente, e durante queste pause si tende a socializzare con individui che vengono riconosciuti come simili: stesso carapace, cavalcature equivalenti, dovranno per forza di cose essere consanguinei.

In due occasioni, entrambi forzate dal passaggio sotto al canale della manica, ho avuto modo di conoscere due distinte coppie di "miei simili" che, così a colpo d'occhio, avevano sulle braccia e nelle orecchie almeno venti anni di cavalcate più di me.

Mi hanno raccontato storie meravigliose e terrificanti, sempre col sorriso sulla faccia. La traversata del grand canyon a partire da Las Vegas, l'odore di ginepro sulla strada da Bilbao a Jerez, gli incidenti mortali visti sul Nurbungring e la paura di non trovare una stazione di servizio attraversando i Pirenei.

Mi hanno fatto tanta tenerezza e tanta invidia, e spero davvero che il vento continui a raccontarmi per lunghi anni avvenire le stesse cose che ha detto loro, perchè di sicuro ci vuole tanta passione e gioia di vivere per dimenticarsi di quanto, in realtà, voglia strappare le braccia da quel dannato manubrio...