# PostgreSQL driver in GRASS

The driver name is 'pg'.

## Creating a PostgreSQL database

A new database is created with 'createdb', see the PostgreSQL manual for details.

## Connecting GRASS to PostgreSQL

    # example for connecting to a PostgreSQL server:
    db.connect driver=pg database="host=myserver.osgeo.org,dbname=mydb"
    # password is asked interactively if not specified:
    db.login user=myname [pass=secret]
    db.connect -p
    db.tables -p

## Supported SQL commands

All SQL commands supported by PostgreSQL. It's not possible to use C-like escapes (with backslash like \n etc) within SQL syntax.

## Operators available in conditions

All SQL operators supported by PostgreSQL.

## Adding an unique ID column

Import vector module require an unique ID column which can be generated as follows in a PostgreSQL table:

    echo "
     ALTER TABLE mytable ADD ID integer;
     CREATE SEQUENCE mytable_seq;
     UPDATE mytabe SET ID = nextval('mytable_seq');
     DROP SEQUENCE mytable_seq;
    " | db.execute

## Attribute Converters

CSV import into PostgreSQL:

    \h copy
    COPY t1 FROM 'filename' USING DELIMITERS ',';

pg2xbase: DBF to PostgreSQL converter.

## Geometry import from PostgreSQL table

v.in.db creates a new vector (points) map from a database table containing coordinates. See here for examples.

## PostGIS: PostgreSQL with vector geometry

PostGIS: adds geographic object support to PostgreSQL.

### Example: Import from PostGIS

In an existing PostGIS database, create the following table:

    CREATE TABLE test
    (
     id serial NOT NULL,
     mytime timestamp DEFAULT now(),
     text varchar,
     wkb_geometry geometry,
     CONSTRAINT test_pkey PRIMARY KEY (id)
    ) WITHOUT OIDS;

    # insert value
    INSERT INTO test (text, wkb_geometry)
     VALUES ('Name',geometryFromText('POLYGON((600000 200000,650000
     200000,650000 250000,600000 250000,600000 200000))',-1));

    # register geometry column
    select AddGeometryColumn ('postgis', 'test', 'geometry', -1, 'GEOMETRY', 2);

GRASS can import this PostGIS polygon map as follows:

    v.in.ogr dsn="PG:host=localhost dbname=postgis user=neteler" layer=test \
             output=test type=boundary,centroid
    v.db.select test
    v.info -t test

#### Geometry Converters

- PostGIS with shp2pgsql:
  `shp2pgsql -D lakespy2 lakespy2 test > lakespy2.sql`
- e00pg: E00 to PostGIS filter, see also v.in.e00.
- GDAL/OGR ogrinfo and ogr2ogr: GIS vector format converter and library, e.g. ArcInfo or SHAPE to PostGIS.
  `ogr2ogr -f "PostgreSQL" shapefile ??`

## SEE ALSO

db.connect, db.execute,
Database management in GRASS GIS,
Help pages for database modules,
SQL support in GRASS GIS
PostgreSQL web site,
pgAdmin graphical user interface

Book: PostgreSQL: Introduction and Concepts by Bruce Momjian
PostgreSQL Documentation
PostgreSQL Technical Documentation
GDAL/OGR PostgreSQL driver documentation
MapServer Wiki

Last changed: $Date: 2011-02-07 18:59:50 +0100 (Mon, 07 Feb 2011) $

Help Index