1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
<!-- 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 <code>createdb</code>, see
the <a href="https://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="https://postgis.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="https://postgis.net/workshops/postgis-intro/loading_data.html#loading-with-shp2pgsql">PostGIS with shp2pgsql</a>:<br>
<code>shp2pgsql -D lakespy2 lakespy2 test > lakespy2.sql</code>
</li>
<li><a href="https://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="https://gdal.org/">ogrinfo and ogr2ogr</a>:
GIS vector format converter and library, e.g. ArcInfo or SHAPE to PostGIS.<br>
<code>ogr2ogr -f "PostgreSQL" shapefile ??</code>
</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="https://www.postgresql.org/">PostgreSQL web site</a></li>
<li><a href="https://www.pgadmin.org/">pgAdmin graphical user interface</a></li>
<li><a href="https://gdal.org/en/stable/drivers/vector/pg.html">GDAL/OGR PostgreSQL
driver documentation</a></li>
</ul>
|
