grass-pg(1grass)

1grass-pg(1)                   Grass User's Manual                  grass-pg(1)
2
3
4

PostgreSQL DATABASE DRIVER

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

Creating a PostgreSQL database

10       A new database is created with createdb, see the PostgreSQL manual  for
11       details.
12

Connecting GRASS to PostgreSQL

14       # example for connecting to a PostgreSQL server:
15       db.connect driver=pg database=mydb
16       db.login user=myname password=secret host=myserver.osgeo.org  # port=5432
17       db.connect -p
18       db.tables -p
19
20   Username and password
21       From the PostgresQL manual:
22
23       The file .pgpass in a user’s home directory can contain passwords to be
24       used if the connection requires a password (and no  password  has  been
25       specified  otherwise).  On  Microsoft  Windows  the file is named %APP‐
26       DATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application
27       Data  subdirectory  in  the  user’s profile). Alternatively, a password
28       file can be specified using the connection parameter  passfile  or  the
29       environment variable PGPASSFILE.  This file should contain lines of the
30       following format:
31       hostname:port:database:username:password
32

Supported SQL commands

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

Operators available in conditions

38       All SQL operators supported by PostgreSQL.
39

Adding an unique ID column

41       Import vector module require an unique ID column which can be generated
42       as follows in a PostgreSQL table:
43       db.execute sql="ALTER TABLE mytable ADD ID integer"
44       db.execute sql="CREATE SEQUENCE mytable_seq"
45       db.execute sql="UPDATE mytable SET ID = nextval(’mytable_seq’)"
46       db.execute sql="DROP SEQUENCE mytable_seq"
47

Attribute import into PostgreSQL

49       CSV import into PostgreSQL:
50       \h copy
51       COPY t1 FROM ’filename’ USING DELIMITERS ’,’;
52

Geometry import from PostgreSQL table into GRASS

54       v.in.db creates a new vector (points) map from a  database  table  con‐
55       taining coordinates. See here for examples.
56

PostGIS: PostgreSQL with vector geometry

58       PostGIS: adds geographic object support to PostgreSQL.
59
60   Example: Import from PostGIS
61       In an existing PostGIS database, create the following table:
62       CREATE TABLE test
63       (
64        id serial NOT NULL,
65        mytime timestamp DEFAULT now(),
66        text varchar,
67        wkb_geometry geometry,
68        CONSTRAINT test_pkey PRIMARY KEY (id)
69       ) WITHOUT OIDS;
70       # insert value
71       INSERT INTO test (text, wkb_geometry)
72        VALUES (’Name’,geometryFromText(’POLYGON((600000 200000,650000
73        200000,650000 250000,600000 250000,600000 200000))’,-1));
74       # register geometry column
75       select AddGeometryColumn (’postgis’, ’test’, ’geometry’, -1, ’GEOMETRY’, 2);
76       GRASS can import this PostGIS polygon map as follows:
77       v.in.ogr input="PG:host=localhost dbname=postgis user=neteler" layer=test \
78                output=test type=boundary,centroid
79       v.db.select test
80       v.info -t test
81
82   Geometry Converters
83           ·   PostGIS with shp2pgsql:
84               shp2pgsql -D lakespy2 lakespy2 test > lakespy2.sql
85
86           ·   e00pg: E00 to PostGIS filter, see also v.in.e00.
87
88           ·   GDAL/OGR  ogrinfo  and ogr2ogr: GIS vector format converter and
89               library, e.g. ArcInfo or SHAPE to PostGIS.
90               ogr2ogr -f "PostgreSQL" shapefile ??
91

REFERENCES

100           ·   PostgreSQL web site
101
102           ·   pgAdmin graphical user interface
103
104           ·   GDAL/OGR PostgreSQL driver documentation
105
106       Last changed: $Date: 2018-07-13 11:36:07 +0200 (Fri, 13 Jul 2018) $
107
108       Main index | Topics index | Keywords index |  Graphical  index  |  Full
109       index
110
111       © 2003-2019 GRASS Development Team, GRASS GIS 7.6.0 Reference Manual
112
113
114
115GRASS 7.6.0                                                        grass-pg(1)