<xml.lt>

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));
	}
}

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 :)

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);
}