There are four core actions that will need to be supported:

For the purposes of this book, we will support them in turn with:

Note that you could use any web browser, any web scripting language, and virtually any database, to perform these tasks. I hope that the examples here are generalized enough to allow you to translate them into the languages and server software of your choice. For example, if PHP isn't to your taste, these examples should be relatively easy to translate into Ruby or Python.

We will assume that you are running a recent copy of Apache Web Server, configured to allow scripting using PHP 5.3 or later.

Create the following files in a new location on your web server:

Create a new MySQL database table (and, if you like, a new database to house it in) for this example. This will store our retrieved geolocation coordinates, with a timestamp and an identifier for the user.

There are a number of different ways we could store the location information. Geolocation coordinates are returned as longitude and latitude; an angle on the surface of the Earth east and west of the Prime Meridian, and an angle north and south of the Equator, respectively. Recall the example data in the Understanding the geolocation API (Simple) recipe, the angles were returned to 14 decimal places. The more accurately we can store these numbers, the more accurately we can retrieve the user's location.

For the purposes of this tutorial, we are going to store our coordinates as a set of floating point numbers. This is because we're not performing any comparisons on the geographic data; we're simply storing and retrieving it.

All we need, then, is decimal point latitudes and longitudes with the required level of accuracy, as well as an integer identifier for the user, and another for the timestamp. As we'll be searching by user and timestamp, it's a good idea to maintain an index for each of these fields.

We'll call our database table points:

--
Table structure for table 'points'
--

CREATE TABLE IF NOT EXISTS 'points' (
'id' int(11) NOT NULL AUTO_INCREMENT COMMENT 'Our primary index field',
'latitude' float NOT NULL COMMENT 'Our latitude coordinate',
'longitude' float NOT NULL COMMENT 'Our longitude coordinate',
'user_id' int(11) NOT NULL COMMENT 'The unique ID of the user',
'time' int(11) NOT NULL COMMENT 'The UNIX timestamp of the time when the point was recorded',
PRIMARY KEY ('id'),
KEY 'user_id' ('user_id'),
KEY 'time' ('time')
) ENGINE=InnoDB;

Note that I've also included a unique identifier for each row in the database, for ease of access later.

One of the problems with geographic data is that it's very easy to store a huge amount of it, which can cause databases to slow down if you're not careful—particularly, if you're doing a lot of proximity queries, for example, to discover stored geographic points within a certain radius of a location. The mathematics behind this functionality, while not massively complicated, can become expensive in aggregate.

MySQL has a spatial support extension, which allows you to store, retrieve, and compare extensions based on an optimized geographic engine. This uses a standard set from the OpenGIS project to store sets of geographic data. It's often installed by default, and is worth getting to know for more sophisticated geo-aware applications.