--
-- create our database user
--
CREATE ROLE
  locator
LOGIN UNENCRYPTED PASSWORD
  'password'
NOINHERIT VALID UNTIL
  'infinity';

--
-- create our database and use the postgis template
-- so that we get access to all of the gis functions.
--
CREATE DATABASE
  locator
WITH ENCODING = 'UTF8'
OWNER = locator
TEMPLATE = template_postgis;

--
-- the tracklog table stores the information for each of
-- our location events.
--
CREATE TABLE tracklog
(
  id serial NOT NULL,
  created timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  deleted boolean NOT NULL DEFAULT false,
  altitude integer NOT NULL DEFAULT 0,
  description character varying(200),
  "comment" character varying(2000),
  CONSTRAINT enforce_dims_location CHECK (ndims(location) = 2),
  CONSTRAINT enforce_geotype_location CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL),
  CONSTRAINT enforce_srid_location CHECK (srid(location) = 4326)
)
WITH (OIDS=FALSE);
ALTER TABLE tracklog OWNER TO locator;

--
-- the location table stores pre-defined points-of-interest
--
CREATE TABLE "location"
(
  id serial NOT NULL,
  created timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone NOT NULL DEFAULT now(),
  description character varying(100) NOT NULL,
  altitude integer NOT NULL,
  deleted boolean NOT NULL DEFAULT false,
  CONSTRAINT location_id PRIMARY KEY (id),
  CONSTRAINT location_description UNIQUE (description),
  CONSTRAINT enforce_dims_location CHECK (ndims(location) = 2),
  CONSTRAINT enforce_geotype_location CHECK (geometrytype(location) = 'POINT'::text OR location IS NULL),
  CONSTRAINT enforce_srid_location CHECK (srid(location) = 4326)
)
WITH (OIDS=FALSE);
ALTER TABLE "location" OWNER TO locator;

--
-- add the "location" columns to the two tables
--
SELECT AddGeometryColumn( 'tracklog', 'location', 4236, 'POINT', 2);
SELECT AddGeometryColumn( 'location', 'location', 4236, 'POINT', 2);

--
-- a t_location_event is our basic data type when we
-- retrieve tracklog information from the database.
--
DROP TYPE IF EXISTS t_location_event CASCADE;
CREATE TYPE t_location_event AS (
  "tracklog_id" INT4,
  "created" TIMESTAMP,
  "location" POINT,
  "altitude" INT4,
  "description" VARCHAR(200),
  "comment" VARCHAR(2000),
  "cp_id" INT4,
  "cp_description" VARCHAR(100),
  "cp_location" POINT,
  "cp_altitude" INT4,
  "cp_distance" DOUBLE PRECISION
);

--
-- retrieve the 'n' most recent tracklog events, plus
-- corresponding closest-point-of-interest information
-- for each tracklog event.
--
CREATE OR REPLACE FUNCTION tracklog_get ( int ) RETURNS SETOF t_location_event AS '
  DECLARE
    v_count ALIAS FOR $1;
  
    tl_rec RECORD;
    loc_rec RECORD;
    tl_loc GEOMETRY;
    cp_loc GEOMETRY;
    r t_location_event%rowtype;
  BEGIN
  
    FOR tl_rec IN EXECUTE ''SELECT id, created, location, altitude, description, comment
                         FROM tracklog
                     ORDER BY created DESC
                        LIMIT '''' '' || v_count || '' '''';''
    LOOP
    	  SELECT id, created, modified, description, location, altitude INTO loc_rec
    	    FROM location
      ORDER BY ST_Distance_Spheroid(location, tl_rec.location, ''SPHEROID["WGS 84",6378137,298.257223563]'') ASC
         LIMIT 1;
         
      r.tracklog_id := tl_rec.id;
      r.created := tl_rec.created;
      r.location := POINT(tl_rec.location);
      r.altitude := tl_rec.altitude;
      r.description = tl_rec.description;
      r.comment := tl_rec.comment;
      r.cp_id := loc_rec.id;
      r.cp_description := loc_rec.description;
      r.cp_location := POINT(loc_rec.location);
      r.cp_altitude := loc_rec.altitude;
      
      SELECT ROUND(CAST(ST_Distance_Spheroid(loc_rec.location, tl_rec.location, ''SPHEROID["WGS 84",6378137,298.257223563]'') AS numeric), 2)
        INTO r.cp_distance;
      
      RETURN NEXT r;
    END LOOP;
  END;
' LANGUAGE plpgsql;