Marginal Structure |

these are basically my notes as a total n00b coming to postgres and postgis from previous experience with mysql. hopefully, they will be useful to others as well in finding their feet. i am using ubuntu, but except for the package names below these instructions should be rather generic for non-windozz systems.

first thing’s is first.

1. install postgres (sudo apt-get install postgresql)

2. install postgis libraries and postgres postgis support (sudo apt-get install postgis postgresql-8.3-postgis).

the approach to postgis is a little different than something like mysql, which many people (like me) are used to. the default postgres user is called postgres (usually) and this is the default superuser. the first time you connect to the postgres manager, you need to su to the postgres user. it will connect to he default database (also named postgres). in general, if you are connecting as user x, and you do not specify a database name it will default to a database of the same name as the current user.

another subtle difference is that postgres has several distinct commands which you can run from the command line, which arent necessarily obvious. where mysql uses mysqladmin <some command>, postgres just uses separate commands without a common prefix- eg. createdb (create a database, assuming the user you connect as has sufficient privileges), createuser, etc. i find this a little weird since in some cases it’s not really obvious that, for example, createuser has anything to do with postgis.

3. you might want to create a user with the same username as your regular account username to simplify interacting with postgres. to do this you can either connect to postgres and do this from within the DBMS, or you can use the createuser postgres command from the command line:

$ sudo -u postgres createuser -s new_superuser

or better yet:

$ sudo -u postgres createuser -s $USER

where $USER is the environment variable that set on most unix style machines that will expand to your username. some people recommend for maximum convenience to also create a database with the same name, to enable connecting to postgres without specifying a database explicitly.

$ createdb $USER

so now we’re set up, and you can connec to postgres by just typing

$ psql

in general to connect as an arbitrary user to an arbitrary database (for which you have permissions of course):

$ psql -U username -d dbname

Once you’re logged in, these commands will come in handy.

• \du lists existing roles (users)
• \l list all databases
• \d tablename (of current database- like ‘describe’ in mysql)

each database that will use postgis has to explicitly enable the postgis functions and datatypes. yes– even though postgis support has been enabled through the packages you installed, you still have to manually enable the specific functions and data types for each database that will use them. there is a standard procedure for doing this. standard enough, in fact, that you can simplify the process by using postgres’ notion of database templates. think of it like creating templates in an office program– like a template for an invoice, or a newsletter. even the default new database is an instantiation of a template: the basic or raw template, called template1. as you can see from the instructions below, a template database is not anything different or special from a regular database.

to create a spatially enabled template, do the following (ref: clear and simple instructions from this blog here):

$ sudo su postgres #optional, if your current user is not a superuser
$ createdb postgistemplate
$ createlang plpgsql postgistemplate
$ psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/lwpostgis.sql
$ psql -d postgistemplate -f /usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql

we then have to make sure the correct users have permissions on the template database (important or they wont be able to use it!), optionally create a schema, and then test it!

$ psql -d postgistemplate

# ALTER TABLE geometry_columns OWNER TO youruser;
# ALTER TABLE spatial_ref_sys OWNER TO youruser;
# CREATE SCHEMA gis_schema AUTHORIZATION gis;

now to create a spatially enabled database (note: this can be done from inside or outside psql):

# CREATE DATABASE gis_test TEMPLATE = postgistemplate;

finally, make sure all is good by defining a table for the new database. here’s a simple example which defines a table that maps to the NMEA gps sentence GPGGA. put these commands in a file, or enter them in on the psql command line:

CREATE TABLE gpgga (
       pk serial PRIMARY KEY,
       utc TIME NOT NULL,
       gps_fix int NOT NULL,
       sats_in_view int NOT NULL,
       horiz_error FLOAT NOT NULL,
       alt_asl FLOAT NOT NULL,
       height_above_WGS84 FLOAT NOT NULL,
       DGPS_ref_id int NOT NULL
);
SELECT AddGeometryColumn('gis_schema', 'gpgga', 'latlong', 4326, 'POINT', 2);

Note that you create the table with all its non-spatial columns first, and then add the POINT (or other spatial) column. The arguments to the AddGeometryColumn are, respectively (schema, table_name, column_name, SRID or spatial reference id, data_type, dimensions). the SRID is required by open GIS, and is actually a foreign key into the SPATIAL_REF_SYS table. it tells the table what the reference system or projection of the data is. you can look these up depending on your data type.

To read this in from the command line is pretty standard:

$ psql -d gis_test < table_definition.sql

upon execution, you will likely see output like the following:

$ psql -d gis_test < table_definition.sql
NOTICE:  CREATE TABLE will create implicit sequence "gpgga_pk_seq" for serial column "gpgga.pk"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "gpgga_pkey" for table "gpgga"
CREATE TABLE
                addgeometrycolumn
--------------------------------------------------
 public.gpgga.latlong SRID:4326 TYPE:POINT DIMS:2
 
(1 row)

Congrats! Coming soon– parsing your nmea files.