Database stuff

First I’ll describe the database that backs the service.

PostGIS database backend

Here is how to make a simple table in PostgreSQL, that can store geo-tagged “observations”. It uses a hstore type for key-value pairs and a geography point for the GPS dot. It’s very versatile, and could store anything from bird observations to endomondo like GPS tracks.

CREATE TABLE observations(
    id SERIAL PRIMARY KEY, 
    utc_timestamp TIMESTAMP, 
    geog GEOGRAPHY(Point, 4326), 
    kvp HSTORE
);

Add spatial index:

CREATE INDEX observations_geog_gix ON observations 
USING GIST (ST_Buffer(geog, 10));

Insert examples

Short run in the park:

INSERT INTO observations (utc_timestamp, geog, kvp) 
VALUES (
    TIMESTAMP '2012-09-11 16:05:06', 
    ST_GeogFromText('POINT(12.6105 55.67032)'), 
    '"type"=>"run","run_id"=>42,"runner_id"=>"skipperkongen"'
),(
    TIMESTAMP '2012-09-11 16:05:46', 
    ST_GeogFromText('POINT(12.6106 55.67255)'), 
    '"type"=>"run","run_id"=>42,"runner_id"=>"skipperkongen"'
),(
    TIMESTAMP '2012-09-11 16:06:25', 
    ST_GeogFromText('POINT(12.6146 55.67330)'), 
    '"type"=>"run","run_id"=>42,"runner_id"=>"skipperkongen"'
);

Bird observation:

INSERT INTO observations (utc_timestamp, geog, kvp) 
VALUES (
    TIMESTAMP '2012-09-04 18:05:06', 
    ST_GeogFromText('POINT(12.6105 55.67032)'), 
    '"type"=>"bird","species"=>"surnia ulula",
     "gender"=>"unknown","spotted_by"=>"skipperkongen"'
);

Select examples

Select a particular run:

SELECT utc_timestamp, ST_AsGeoJson(geog) 
FROM observations 
WHERE kvp @> '"type"=>"run","run_id"=>42'
ORDER BY utc_timestamp;

Select all bird observations:

SELECT * 
FROM observations 
WHERE kvp @> '"type"=>"bird"';

Select the concave hull (PostGIS 2.0) for each bird species:

SELECT 
    kvp -> 'species' as species, 
    ST_Buffer(ST_ConcaveHull(ST_Collect(geog::GEOMETRY), 0.8), 100) as location
FROM observations 
WHERE kvp @> '"type"=>"bird"' 
GROUP BY kvp -> 'species';

Note: something might be wrong with the coordinates, when selecting like above… investigate.

Building the Rest API in Django

TODO