<xml.lt>

We started working a little bit with location-based services. One of the tasks was to create a webservice endpoint to serve placemark information for a certain location. We chose Google's KML (Keyhole Markup Language) as the representation format. It has become an official Open Geospatial Consortium (OGC) standard.

This piece of code might be useful for those who have a similar need to serialize placemark objects in to KML. It is using PHP's Document Object Model (DOM) extension.

$doc = new DOMDocument("1.0", "UTF-8");
$kmlElem = $doc->createElementNS("http://earth.google.com/kml/2.2", "kml");
$doc->appendChild($kmlElem);
$documentElem = $doc->createElement("Document");
$kmlElem->appendChild($documentElem);

$places = PlacePeer::doSelect(new Criteria()); // retrieve placemark objects, e.g. from Propel model

foreach ($places as $place)
{
	$placemarkElem = $doc->createElement("Placemark");
	$documentElem->appendChild($placemarkElem);

	$nameElem = $doc->createElement("name");
	$placemarkElem->appendChild($nameElem);
	$nameElem->appendChild($doc->createTextNode($place->getName()));

	$descElem = $doc->createElement("description");
	$placemarkElem->appendChild($descElem);
	$descElem->appendChild($doc->createTextNode($place->getDescription()));

	$pointElem = $doc->createElement("Point");
	$coordElem = $doc->createElement("coordinates");
	
	$coordElem->appendChild($doc->createTextNode($place->getLng().",".$place->getLat().",0"));
	$pointElem->appendChild($coordElem);
	$placemarkElem->appendChild($pointElem);
}

$response->write($doc->saveXML()); // use the KML string, e.g. write it out to response

Back from some holiday, I was thinking about how to (automatically) manage user-generated content. On a website where users can add and change data, how does one make sure that it is entered correctly and that it will not be vandalized afterwards? Maybe I am paranoid, but I think if there is a possibility to ruin something online, it will be ruined.

Let us take a website where users can add events and their details as an example. What if an event is fake? Also, what about duplicates (different names for the same actual event)? Last.fm takes an interesting approach letting users vote for the right names of the artists. After some number and/or percent of positive votes, it would be possible to assume that event details are correct and lock it to prevent further editing.

But what if the event details actually need to be changed? Should only the creator be allowed to make changes, or everybody? In that case, how to make sure that a description carefully crafted by several users will not be wiped out by somebody? As far as I can see, it needs at least some version tracking to be able to roll back to the right version, as in Last.fm's description wiki and Wikipedia. But the implementation on a relational database does not seem trivial for me. Can anyone provide an example? I think it would be possible to build version tracking into existing ORMs such as Propel.

First post using Firefox 3. I hope we'll set a proper download record today :) The demand must have been so high that Mozilla servers seem to be down once in a while.

For those who do not have (or know!) it, get Firefox!

kbh.dk, a local social network for Copenhagen which I was working on, was officially launched on monday, with talks, demonstrations, free beers and snacks :)

Check out the events section which I was mostly responsible for, including third-party event feed import, simple recommendation system suggesting relevant events, and SMS backend responding with events that take place close to a specified location.

As the W3C best practice goes, cool URIs don't change. While I was aware of it, I stumbled upon it in a real-life situation and was convinced that stable URIs are not only abstract concept, but can also be an issue in development.

In the DIY Framework resources function as real objects connected to domain objects they stand for, and have explicit URIs. They are also stored in a database where URI becomes, logically enough, a primary key. The URIs are relative to the domain, and are usually made “pretty” from the name (and possibly category, date, etc.) of the domain object, for example, Places/Bars/Retro.

That functions well on resource lookup and creation. Resource that needs to be processed is retrieved by querying the database (with the help of Propel ORM) with current request URI. URI of a new resource is concatenated of all the necessary URL-encoded parts.
However, things become complicated when a resource has a lot of child resources, which have the same URI prefix but different ending, for example, Places/Bars/Retro/Events/ or Places/Bars/Retro/Pictures/123 (and there could be hundreds of those). In the current architecture, the only relationship between these resources is the URI string. Now, if a user recognizes he/she has made a mistake, or for some other reason wants to change the name of the place from Retro to something else, it would be also logical to change the URI as well. But that would also affect all the child resources, and changing one name might involve changing URIs of all of them, which could probably mean hundreds of queries. The same goes for deleting.

I don't really see an easy way out of this situation. One of them would be simply not to allow changing names of objects or whatever that goes into URIs :) Cool URIs don't change, right? Made a mistake — create a new one. But I guess that is not too user-friendly, huh?...
The other would mean refactoring the whole resource design by making the parent-child relationship explicit and making the URIs only relative to the parent. Maybe that could be implemented using the new nested-set support in Propel. It would make it easier to get the parent resource (instead of checking their URIs), which is sometimes useful. On the other hand, it would also make resource retrieval, URI matching and constructing much more complicated compared to the trivial string matching and concatenation which is currently used.

Me and my colleague have finally defended our master thesis titled “New Generation of Social Networks and Underlying Web Frameworks Based on Semantic Web Technologies” at IT University of Copenhagen and got 12, the top score on the danish scale. I have to say that was totally unexpected, but still feels nice :)

I have built a simple recommendation for one of the websites I work with. It is only based on social relationships and does not take into account other parameters. The basic idea is if I like an item, select other items liked by people who also like this item and it can be used to build lists of similar items as seen on Amazon or YouTube. If we use as an example people who like movies (tables Person, Movie and many-to-many relationship PersonMovie), this can be expressed in SQL using several joins:

SELECT DISTINCT Movie.*, COUNT(Person.ID) AS PersonCount
FROM Movie
JOIN PersonMovie ON Movie.ID = PersonMovie.MovieID
JOIN Person ON PersonMovie.PersonID = Person.ID
JOIN PersonMovie AS PersonMovie1 ON Person.ID = PersonMovie1.PersonID
JOIN Movie AS Movie1 ON PersonMovie1.MovieID = Movie1.ID
WHERE Movie1.ID = 1 AND Movie.ID != Movie1.ID
GROUP BY Movie.ID
ORDER BY PersonCount DESC

First we join movies to people and then backwards to the specific movie with ID = 1 that we want to get similar movies for. PersonCount is the relevance factor — the higher it is, the more people like the movie.

The algorithm can also be implemented in PHP using Propel ORM, for example, as a method in the Movie class:

public function getSimilarMovies(Criteria $c = null)
{
	if ($c === null) $c = new Criteria();
	elseif ($c instanceof Criteria) $c = clone $c;
	
	MoviePeer::addSelectColumns($c);

	$c->addAlias("PersonMovie1", PersonMoviePeer::TABLE_NAME);
	$c->addAlias("Movie1", MoviePeer::TABLE_NAME);
	$c->addAsColumn("PersonCount", "COUNT(Person.ID)");
	$c->addJoin(MoviePeer::ID, PersonMoviePeer::MovieID);
	$c->addJoin(PersonMoviePeer::PERSONID, PersonPeer::ID);
	$c->addJoin(PersonPeer::ID, "PersonMovie1.PersonID");
	$c->addJoin("PersonMovie1.MovieID", "Movie1.ID");
	$c->add("Movie1.ID", $this->getID());
	$c->add(MoviePeer::ID, $this->getID(), Criteria::NOT_EQUAL);
	$c->addGroupByColumn(MoviePeer::ID);
	$c->addDescendingOrderByColumn("PersonCount");

	return MoviePeer::doSelect($c);
}

I recently noticed, that most services needed to develop, manage, and deploy a website are free, except for hosting perhaps. Version control, project management, bug tracking — all that a developer or a small team might need is online for $0, thanks to some far-sighted providers. Most of the services have completely free accounts with some limitations, others provide free trial. And paying a few backs when you need more space or features does not feel too bad since you have been treated well.

Here is a list I personally use and can recommend (this is not an advertisement on someone's request!):

Springloops

Code collaboration for Web developers: SVN hosting, code browser, change reviews, FTP deployment, Basecamp integration

Basecamp

Project management, collaboration, and task software: projects, to-dos, milestones, writeboards

Google Analytics

Website statistics, visitor tracking

Feedburner

RSS statistics and tracking

Dropbox

File sharing and synchronization on different platforms

Tick

Time tracking

Yammer

Internal communication for organizations

GetSatisfaction

Customer support

Know more or better? Please let us know :)

Eventually the simple distance formula that I have blogged about turned out to be too inaccurate, even for locations within city bounds. I needed to use a formula to calculate great-circle distance which takes into account that the Earth is a sphere. So here is the SQL query:

SELECT *,
@lat1 := (RADIANS(l1.lat)) as lat1, @lng1 := (RADIANS(l1.lng)) AS lng1,
@lat2 := (RADIANS(l2.lat)) AS lat2, @lng2 := (RADIANS(l2.lng)) AS lng2,
ACOS(SIN(@lat1) * SIN(@lat2) + COS(@lat1) * COS(@lat2) * COS(@lng2 - @lng1)) * 6371 AS Distance
FROM Location AS l1 INNER JOIN Location AS l2
WHERE l1.id = 1 AND l1.id != l2.id
ORDER BY Distance
LIMIT 10

It uses variables to store temporary calculations. I'm not sure though if the @var syntax is MySQL-specific.

As custom as this query is, it still is possible to build it in an object-oriented fashion using Propel ORM and its Criteria class. It can be used for example to implement a method in a Location class to get other closest locations around it:

class Location extends BaseLocation {
{
	// ...

	public function getClosestLocations(Criteria $c = null)
	{
		if ($c === null) $c = new Criteria();
		elseif ($c instanceof Criteria) $c = clone $c;
		
		LocationPeer::addSelectColumns($c);

		$c->addAlias("Location1", LocationPeer::TABLE_NAME);
		$c->addAsColumn("lat", "@lat := RADIANS(Location.lat)");
		$c->addAsColumn("lng", "@lng := RADIANS(Location.lng)");
		$c->addAsColumn("lat1", "@lat1 := RADIANS(Location1.lat)");
		$c->addAsColumn("lng1", "@lng1 := RADIANS(Location1.lng)");
		$c->addAsColumn("Distance", "ACOS(SIN(@lat) * SIN(@lat1) + COS(@lat) * COS(@lat1) * COS(@lng1 - @lng)) * 6371");
		$c->add(LocationPeer::ID, $this->getID(), Criteria::NOT_EQUAL);
		$c->add("Location1.ID", $this->getID());

		return LocationPeer::populateObjects(LocationPeer::doSelectStmt($c));
	}
}

There was a need recently to calculate distances between locations that have a latitude and longitude defined and retrieve the ones that are closest. I came up with a little SQL query, which is based on the Pythagorean theorem and simple math. It does not take into account that Earth is a sphere (in that case you would need to use something more complicated like the Haversine formula), but should be accurate enough for relatively short distances in range of tens of kilometers.

SELECT *, SQRT(POW(L2.lat - L1.lat, 2) + POW(L2.lng - L1.lng, 2)) AS Distance
FROM Location AS L1
INNER JOIN Location AS L2
WHERE L1.id = 1 AND L1.id != L2.id
ORDER BY Distance
LIMIT 10

The query makes a self join on the Location table, which is assumed to contain lat and lng columns. It retrieves 10 locations closest to location which ID is 1. If you remove that condition and the LIMIT clause, you will get a table of distances between all locations.
You can also put some limit on distance as radius (in degrees), but it does not really translate into (kilo)meters using this formula.