Geo-Distance vlg Haversine as a DB2 UDF

In a hack I'm currently working on (to be published soon) I needed to calculate the distance between two locations (on the surface of our planet). Admittedly, those are close addresses (all in Vienna), so I could have assumed them all to be on a plane (not an airplane, a flat surface...), but I felt like some real math.

This is a workaround for all of you who do not have spatial extender available or installed.

To calculate the distance of two points on (a perfectly spherical earth) you apply the haversine formula.
This is the representation of it as a DB2 user defined function (UDF)

CREATE FUNCTION HAVERSINE( lat1 decimal(10,8), lon1 decimal(10,8), lat2 decimal(10,8), lon2 decimal(10,8))
RETURNS DECIMAL(15,8)
F1: BEGIN ATOMIC
declare dlat, dlon, a, d double;
set dlat = radians(lat2-lat1);
set dlon = radians(lon2-lon1);
set a = sin(dlat/2) * sin(dlat/2) + cos(radians(lat1)) * cos(radians(lat2)) * sin(dlon/2) * sin(dlon/2);
set d = 2*6367*asin(sqrt(a));
return d;
END

The two points are given in latitude and longitude (in degrees) as lat1/lon1 and lat2/lon2. It returns the distance in kilometers. If you want to change this, replace 6367 (which happens to be the Earth's radius im km in Vienna, for which I needed this) with your radius and units,e.g. 3956 if you want miles in London.
Check it simply at Wolfram Alpha with "earth radius in london".

I guess it is not perfect, but it does work well. I should probably make it DETERMINISTIC (because it is) to improve performance.
Go ahead, play around.

Facebook vs Identity - again

Well, it should read "Facebook vs Privacy - again"... but the point I want to make is, that Facebook could have been an Identity platform.
In a way they are... not only in a way, if you go and count the sites that allow you to login with your facebook account. But that only makes them an Identity provider.

An Identity platform needs more than just a secure single sign-on.
For example authorization: you should be able to finely tune who has access to what. That's why the social graph (or your address book, if you will) is so valuable. Facebook does have most of the data and means to enable proper authorization: who can see my wall-posts, who is allowed to contact me, ...

It would have been easy to extend from there.

But with Places they once again proved that they rather go the pure marketing platform way, instead.

1. Places is mostly opt out. It is somewhat (but not fully, it seems) enabled by default, until you disable it.
   Not a good default - privacy-wise.
2. Other people (your friends) can check you in at any place they want.
3. You can't control your places. Anyone can check in at your home...[1]

This is all good and fine for a geo platform (like foursquare)... but not for an identity platform

To Facebook (the company) it seems more important to publish stuff about you (and make money from the ads) than to have you properly manage your identity. That's fine, too, but that makes them a marketing platform only...

Sorry.

--
[1] different issue, I know, but it still troubles me.

Shapes from the crowd

Here's an excellent post from Christian about flickr (geo-) shapes - derived from crowd-sourced tags.

Shapes from the crowd - Christian Spanring

The 2 examples he gives (Vienna boundaries, and Vienna inner city) to me are typical examples of the "wisdom of crowds" effect: quite accurate with a large enough sample and good mixture of people within the crowd (Vienna boundaries) and sometimes "totally off" with a specific narrow topic and (therefore) a badly composed crowd (Inner city vs Stephansdom).

It also highlights the problem with tags, that - especially on flickr - it is unclear what the are used for: the place where the picture was taken from, or the motif...

Thanks.

Dopplr and city data

Dopplr is a great service... I really like it.
However, the quality of the city/location data ... well ... sucks.
At least for Austria.
Try Saalbach (major skiing resort) or the city of Krems ... totally unknown to Dopplr and replaced with nearby villages that don't make any sense.
Dopplr say that rely on data from the GeoNames project, but Geonames has all those data...(try Saalbach or Krems)... so why doesn't Dopplr know about them...

Strange.