Spatial Wiki 7 - Setup the Spatial Database

We've spent a fair bit of time setting up the server, and installing and configuring PostgreSQL and PostGIS. At this point we have a reasonably well configured cloud based geospatial server so it's time to setup the spatial database. The database will be a simple spatially enabled data model to store points and a bit of metadata sourced from the wiki. Not sure if we'll get there, but the objective will be to implement a bit of custom PHP code to keep the spatial database updated in real-time as wiki entries with spatial references are created or modified. So, in thinking about the data model we'll include the following:

• A spatial reference, a point in lat-long, WGS84 coordinates
• A link to the wiki article that the point is associated with, this will be the unique article id from MediaWiki, the article title will also be stored
• A point type that will be validated against a reference table of allowed point types
• Date/time fields that keep track of when a row is created and when it's modified

Before creating the data model we'll first create a new database.  You create a new PostgreSQL database from the Linux prompt with

sudo -u postgres createdb spatialwiki

This creates the database "spatialwiki" with the postgres user.

Next we'll enable the database to support PL/pgSQL which is required for PostGIS:

sudo createlang --username=postgres --password plpgsql spatialwiki

Then run the following SQL scripts to setup PostGIS functions and other objects in the database:

sudo psql -U postgres -d spatialwiki -f /usr/share/postgresql/8.4/contrib/postgis.sql
sudo psql -U postgres -d spatialwiki -f /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql
sudo psql -U postgres -d spatialwiki -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

Note that depending on what version of PostGIS you have installed the path to postgis.sql and spatial_ref_sys.sql may be slightly different.  To find out exactly where they are on your system you can use the find command like:

sudo find / -name postgis.sql

When complete you can login to the database and check that PostGIS is installed and setup properly with:

sudo -u postgres psql spatialwiki

Then at the PostgreSQL prompt run the SQL query:

SELECT PostGIS_full_version();

And with everything setup properly you should see:

postgis_full_version                                  
----------------------------------------------------------------------------------------
 POSTGIS="1.4.0" GEOS="3.1.0-CAPI-1.5.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
(1 row)

To exit the PostgreSQL prompt do \q.

Creating the Database Structure

With the database "container" in-place we can go ahead and create the database structure. In our case we're just going to build two tables.  The main table will be wikipts and it will contain the spatial references and other information from the wiki articles.  The point_type table will be a reference table containing allowable types for each point.  Following good database design practices there are also sequences to automatically populate the rowids, and a trigger to automatically keep the last modified timestamp current.  The script to create the database is listed below.  The easiest way (or at least one of the ways) to create the database is to write the script to a file (i.e. create_spatialwiki_database.sql) and then run it against the new database as follows:

sudo psql -U postgres -d spatialwiki -f create_spatialwiki_database.sql

The script looks like this:

--
-- PostgreSQL code to setup the spatialwiki database.
--
--
-- drop objects if they exist in the right order
--
DROP TABLE IF EXISTS wikipts;
DROP TABLE IF EXISTS point_type;
--
-- create a function to keep the last modified date updated automatically
--
CREATE OR REPLACE FUNCTION update_lastmodified_column() 
        RETURNS TRIGGER AS '
  BEGIN
    NEW.timestamp_lastmodified_utc = NOW();
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';
--
-- point_type table
--
DROP SEQUENCE IF EXISTS point_type_rowid;
CREATE SEQUENCE point_type_rowid START 1;
CREATE TABLE point_type
(
  rowid INTEGER PRIMARY KEY DEFAULT nextval('point_type_rowid'),
  name VARCHAR(254) NOT NULL UNIQUE,
  description VARCHAR(8000),
  timestamp_loaded_utc TIMESTAMP NOT NULL DEFAULT current_timestamp,
  timestamp_lastmodified_utc TIMESTAMP NOT NULL DEFAULT current_timestamp
);
--
-- build wikipts table
--
DROP SEQUENCE IF EXISTS wikipts_rowid;
CREATE SEQUENCE wikipts_rowid START 1;
CREATE TABLE wikipts
(
  rowid INTEGER PRIMARY KEY DEFAULT nextval('wikipts_rowid'),
  article_name VARCHAR(2000) NOT NULL UNIQUE,
  article_id INTEGER NOT NULL UNIQUE,
  point_type_fk INTEGER NOT NULL REFERENCES point_type,
  timestamp_loaded_utc TIMESTAMP NOT NULL DEFAULT current_timestamp,
  timestamp_lastmodified_utc TIMESTAMP NOT NULL DEFAULT current_timestamp
);
--
-- add the geometry column
--
SELECT AddGeometryColumn('wikipts', 'geom', 4326, 'POINT', 2);
--
-- add triggers to keep the last modified date updated automatically
--
CREATE TRIGGER update_lastmodified BEFORE UPDATE
  ON point_type FOR EACH ROW EXECUTE PROCEDURE
  update_lastmodified_column();
CREATE TRIGGER update_lastmodified BEFORE UPDATE
  ON wikipts FOR EACH ROW EXECUTE PROCEDURE
  update_lastmodified_column();

Note the AddGeometryColumn() section of the script that adds the point geometry column with coordinate system = 4326 which is WGS84 lat-long.

Adding Test Data

The short SQL script below can be used to load some test data. Write it to a .sql file and run the script per the example above that was used to create the database structure. Note the ST_GeomFromText() function that is used to load the lat-long values into the geometry column, and the "SELECT rowid FROM point_type..." that is used to load the proper rowid link to the point type which is Test in this case.

--
-- point types
--
INSERT INTO point_type (name, description) VALUES ('Test', 'Test Points');
INSERT INTO point_type (name, description) VALUES ('Pegmatite', 'Coarse grained intrusive hosted rare earth or gemstone deposit.');
INSERT INTO point_type (name, description) VALUES ('Porphyry', 'Intrusive hosted, distinctly textured and zoned mineral deposit.');
--
-- test points (FOSS4G meeting locations)
--
INSERT INTO wikipts (article_name, article_id, geom, point_type_fk) VALUES (
  'FOSS4G 2011 - Denver', -1,
  ST_GeomFromText('POINT(-104.988790 39.741850)', 4326), 
  (SELECT rowid FROM point_type WHERE name = 'Test')
);
INSERT INTO wikipts (article_name, article_id, geom, point_type_fk) VALUES (
  'FOSS4G 2010 - Barcelona', -2,
  ST_GeomFromText('POINT(2.15170 41.37237)', 4326), 
  (SELECT rowid FROM point_type WHERE name = 'Test')
);
INSERT INTO wikipts (article_name, article_id, geom, point_type_fk) VALUES (
  'FOSS4G 2009 - Sydney', -3,
  ST_GeomFromText('POINT(151.199158 -33.873375)', 4326), 
  (SELECT rowid FROM point_type WHERE name = 'Test')
);
INSERT INTO wikipts (article_name, article_id, geom, point_type_fk) VALUES (
  'FOSS4G 2008 - Cape Town', -4,
  ST_GeomFromText('POINT(18.427470 -33.916017)', 4326), 
  (SELECT rowid FROM point_type WHERE name = 'Test')
);
INSERT INTO wikipts (article_name, article_id, geom, point_type_fk) VALUES (
  'FOSS4G 2007 - Victoria', -5,
  ST_GeomFromText('POINT(-123.366856 48.421684)', 4326),
  (SELECT rowid FROM point_type WHERE name = 'Test')
);

Setup a View

In order to make our points click-able with the SpatialWiki Extension we're also going to setup a view that contains the right fields in the right order.  The SQL to do this is:

CREATE OR REPLACE VIEW view_wikipts  AS
SELECT article_name, article_id, geom, name
FROM wikipts, point_type
WHERE wikipts.point_type_fk = point_type.rowid

The view that's created just needs to contain in-order the article name, the article id, the geometry column, and the point type.


Viewing the Data in Quantum GIS

Quantum GIS (QGIS) is a great cross-platform free and open source desktop GIS system that can directly read PostGIS data so we'll use it here to quickly display the test points and verify that everything in the database is working properly.  Start QGIS and setup a new PostGIS database connection similar to the example shown below.

Once the connection is setup you can test it and open it.  You should see the new wikipts available to add to the map.  The test points with a simple world outline background are shown in the figure below.  Seems like the FOSS4G meeting in Denver is the first one in history that is not right on the coast!

See the next article on Installing GeoServer.