Setting up PostGIS
Setting up PostGIS on Debian Squeeze is quite easy. The only gotcha is
due to permissions you have to switch users in between. Only the user
'postgres' is allowed to run the commands in 'postgis.sql', which
enhances your database with function specialized on working with geo
data.
As user 'root'
As user 'postgres'
After that you can use the geo function like so...
apt-get install postgis
createdb your_db
createlang plpgsql your_db
psql -d your_db -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
CREATE TABLE waypoints (id serial PRIMARY KEY, location geography(POINT, 4326));
INSERT INTO waypoints (location) VALUES (ST_GeographyFromText('SRID=4326;POINT(47 8.5)'));
INSERT INTO waypoints (location) VALUES (ST_GeographyFromText('SRID=4326;POINT(47.5 8.5)'));
INSERT INTO waypoints (location) VALUES (ST_GeographyFromText('SRID=4326;POINT(47.5 9)'));
SELECT id FROM waypoints WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(47 8.5)'), 10000);
SELECT ST_AsGeoJSON(ST_MakeLine(gps.location::geometry)) AS track FROM (SELECT location FROM waypoints ORDER BY id ASC) AS gps;