PostGIS

From Gerris

To install on a debian-based system

% sudo apt-get install postgresql-8.2-postgis

To check that the postgres daemon is running

% ps aux | grep postgres
postgres  8550  0.0  0.6  38008  2844 ?        S    16:54   0:00 /usr/lib/postgresql/8.2/bin/postgres -D /var/lib/postgresql/8.2/main -c config_file=/etc/postgresql/8.2/main/postgresql.conf

User creation and privileges setting (see the postgres manual)

% sudo -u postgres psql
postgres=# CREATE USER steph;
CREATE ROLE
postgres=# ALTER ROLE steph WITH CREATEDB;
ALTER ROLE
postgres-# ALTER ROLE steph WITH PASSWORD 'mypassword';
ALTER ROLE
postgres-# \q

Creation of a spatial database

% createdb my_spatial_db
CREATE DATABASE

Enable PostGIS on this database (see the PostGIS manual and the comments in this post)

% sudo -u postgres createlang plpgsql my_spatial_db
% sudo -u postgres psql -d my_spatial_db -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
% sudo -u postgres psql -d my_spatial_db
my_spatial_db=# GRANT ALL ON geometry_columns TO steph;
GRANT

Create a spatial table

% psql -d my_spatial_db
my_spatial_db=> CREATE TABLE topo ( randid INTEGER , height REAL ) WITH OIDS;
CREATE TABLE
my_spatial_db=> SELECT AddGeometryColumn( , 'topo', 'geom', -1, 'POINT', 2 );
            addgeometrycolumn              
--------------------------------------------
 public.topo.geom SRID:-1 TYPE:POINT DIMS:2
 
(1 row)

The WITH OIDS seems to be required in order for qgis to be able to access the table.

Convert a longitude,latitude,height text file into SQL statements

awk -f convert.awk < file > file.sql

with convert.awk

BEGIN {
  print "INSERT INTO topo (randid, height, geom) VALUES"; 
}
{ print "(" int(rand()*1e9) ", " $3 ", GeomFromText('POINT(" $1 " " $2")'))," }
END{ print ";"}

Insert the values in the database

my_spatial_db=> \i file.sql

Create a spatial index

my_spatial_db=> CREATE INDEX topoidx ON topo USING GIST (geom);

Example of spatial queries

my_spatial_db=> \timing
my_spatial_db=> SELECT randid, height, AsText(geom) AS geom FROM topo ORDER BY randid LIMIT 10;
my_spatial_db=> SELECT randid, height, AsText(geom) AS geom FROM topo
WHERE geom && 'BOX(-0.01 -0.01,0.01 0.01)'::box2d;