Geografic-Information-System/db/drivers/postgres/grass-pg.html

<!-- meta page description: PostgreSQL DATABASE DRIVER -->

PostgreSQL database driver enables GRASS to store vector attributes in
PostgreSQL server.

<h2>Creating a PostgreSQL database</h2>

A new database is created with <tt>createdb</tt>, see
the <a href="http://www.postgresql.org/docs/manuals/">PostgreSQL
manual</a> for details.

<h2>Connecting GRASS to PostgreSQL</h2>

<div class="code"><pre>
# example for connecting to a PostgreSQL server:
db.connect driver=pg database=mydb
db.login user=myname password=secret host=myserver.osgeo.org  # port=5432
db.connect -p
db.tables -p
</pre></div>

<h3>Username and password</h3>

From the <a href="https://www.postgresql.org/docs/10/static/libpq-pgpass.html">PostgresQL manual</a>:
<p>
The file <em>.pgpass</em> in a user's home directory can contain 
passwords to be used if the connection requires a password (and no 
password has been specified otherwise). On Microsoft Windows the file 
is named <em>%APPDATA%\postgresql\pgpass.conf</em> (where 
<em>%APPDATA%</em> refers to the Application Data subdirectory in the 
user's profile). Alternatively, a password file can be specified using 
the connection parameter passfile or the environment variable 
PGPASSFILE.

This file should contain lines of the following format:
<div class="code"><pre>
hostname:port:database:username:password
</pre></div>

<h2>Supported SQL commands</h2>

All SQL commands supported by PostgreSQL.

It's not possible to use C-like escapes (with backslash like \n etc)
within the SQL syntax.

<h2>Operators available in conditions</h2>

All SQL operators supported by PostgreSQL.

<h2>Adding an unique ID column</h2>

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

<div class="code"><pre>
db.execute sql="ALTER TABLE mytable ADD ID integer"
db.execute sql="CREATE SEQUENCE mytable_seq"
db.execute sql="UPDATE mytable SET ID = nextval('mytable_seq')"
db.execute sql="DROP SEQUENCE mytable_seq"
</pre></div>


<h2>Attribute import into PostgreSQL</h2>

CSV import into PostgreSQL:
<div class="code"><pre>
\h copy
COPY t1 FROM 'filename' USING DELIMITERS ',';
</pre></div>

<h2>Geometry import from PostgreSQL table into GRASS</h2>

<em><a href="v.in.db.html">v.in.db</a></em> creates a new vector
(points) map from a database table containing
coordinates. See <a href="v.in.db.html">here</a> for examples.

<h2>PostGIS: PostgreSQL with vector geometry</h2>

<a href="http://postgis.refractions.net/">PostGIS</a>: 
adds geographic object support to PostgreSQL.

<h3>Example: Import from PostGIS</h3>

In an existing PostGIS database, create the following table:

<div class="code"><pre>
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);
</pre></div>

GRASS can import this PostGIS polygon map as follows:

<div class="code"><pre>
v.in.ogr input="PG:host=localhost dbname=postgis user=neteler" layer=test \
         output=test type=boundary,centroid
v.db.select test
v.info -t test
</pre></div>


<h4>Geometry Converters</h4>
<ul>
<li><a href="http://postgis.refractions.net/download/">PostGIS with shp2pgsql</a>:<br>
 <tt>shp2pgsql -D lakespy2 lakespy2 test > lakespy2.sql</tt>
</li>
<li><a href="http://e00pg.sourceforge.net/">e00pg</a>: E00 to PostGIS filter,
see also <em><a href="v.in.e00.html">v.in.e00</a></em>.
</li>
<li>GDAL/OGR <a href="http://www.gdal.org/">ogrinfo and ogr2ogr</a>:
GIS vector format converter and library, e.g. ArcInfo or SHAPE to PostGIS.<br>
  <tt>ogr2ogr -f "PostgreSQL" shapefile ??</tt>
</li>
</ul>

<h2>SEE ALSO</h2>

<em>
<a href="db.connect.html">db.connect</a>,
<a href="db.execute.html">db.execute</a>
</em>

<p>
<a href="databaseintro.html">Database management in GRASS GIS</a><br>
<a href="database.html">Help pages for database modules</a><br>
<a href="sql.html">SQL support in GRASS GIS</a><br>

<h2>REFERENCES</h2>

<ul>
<li><a href="http://www.postgresql.org/">PostgreSQL web site</a></li>
<li><a href="http://www.pgadmin.org/">pgAdmin graphical user interface</a></li>
<li><a href="http://www.gdal.org/drv_pg.html">GDAL/OGR PostgreSQL
driver documentation</a></li>
</ul>

<!--
<p>
<i>Last changed: $Date$</i>
-->