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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
|
<html>
<head>
<title>PostgreSQL / PostGIS - Advanced Driver Information</title>
</head>
<body bgcolor="#ffffff">
<h1>PostgreSQL / PostGIS - Advanced Driver Information</h1>
The information collected in that page deal with advanced topics, not found in the
<a href="drv_pg.html">OGR PostgreSQL driver Information</a> page.
<h2>Connection options related to schemas and tables</h2>
Starting with GDAL 1.8.0, the database opening should be significantly faster than in previous versions, so using tables= or
schemas= options will not bring further noticeable speed-ups.<p>
Starting with GDAL 1.6.0, the set of tables to be scanned can be overridden by specifying
<i>tables=[schema.]table[(geom_column_name)][,[schema2.]table2[(geom_column_name2)],...]</i> within the connection string.
If the parameter is found, the driver skips enumeration of the tables as
described in the next paragraph.<p>
Starting with GDAL 1.7.0, it is possible to restrict the schemas that will be scanned while establishing
the list of tables. This can be done by specifying <i>schemas=schema_name[,schema_name2]</i> within the connection string.
This can also be a way of speeding up the connection to a PostgreSQL database if there are a lot of schemas.
Note that if only one schema is listed, it will also be made automatically the active schema
(and the schema name will not prefix the layer name). Otherwise, the active schema is still 'public',
unless otherwise specified by the <i>active_schema=</i> option.<p>
Starting with GDAL 1.7.0, the active schema ('public' being the default) can be overridden by specifying
<i>active_schema=schema_name</i> within the connection string. The active schema is the schema where tables
are created or looked for when their name is not explicitly prefixed by a schema name.
Note that this does not restrict the tables that will be listed (see <i>schemas=</i> option above).
When getting the list of tables, the name of the tables within that active schema will
not be prefixed by the schema name. For example, if you have a table 'foo' within the public
schema, and a table 'foo' within the 'bar_schema' schema, and that you specify
active_schema=bar_schema, 2 layers will be listed : 'foo' (implicetly within 'bar_schema') and 'public.foo'.<p>
<h2>Multiple geometry columns</h2>
Starting with GDAL 1.6.0, the PostgreSQL driver supports accessing tables with multiple PostGIS geometry columns.
For such a table, there will be as many layers reported as the number of geometry columns listed for that
table in the <i>geometry_columns</i> table.
For example, if a table 'foo' has 2 geometry columns 'bar' and 'baz', 2 layers will be reported :
'foo(bar)' and 'foo(baz)'. For backward compatibility, if a table has only one geometry column,
the layer name is the table name. Also if a table 'foo' has several geometry columns,
with one being called 'wkb_geometry', the layer corresponding to this geometry column will
be simply reported as 'foo'. Be careful - the behaviour in creation, update or deletion of layers
that are based on tables with multiple PostGIS geometry column is known to have (not well-defined)
side-effects on the other layers as they are closely tied. Thus, that capability should currently
be thought as mostly read-only.<p>
<h2>Layers</h2>
Starting with GDAL 1.6.0, even when PostGIS is enabled, if the user defines the environment variable
<pre>PG_LIST_ALL_TABLES=YES</pre> (and does not specify tables=), all regular user tables and named views
will be treated as layers. However, tables with multiple geometry column will only be reported
once in that mode. So this variable is mainly useful when PostGIS is enabled to find out tables
with no spatial data, or views without an entry in <i>geometry_columns</i> table.<p>
In any case, all user tables can be queried explicitly with GetLayerByName()<p>
Regular (non-spatial) tables can be accessed, and will return features with
attributes, but not geometry. If the table has a "wkb_geometry" field, it will
be treated as a spatial table. The type of the field is inspected to
determine how to read it. It can be a PostGIS <b>geometry</b> field, which
is assumed to come back in OGC WKT, or type BYTEA or OID in which case it
is used as a source of OGC WKB geometry.<p>
Starting with GDAL 1.6.0, tables inherited from spatial tables are supported.<p>
If there is an "ogc_fid" field, it will be used to set the feature id of
the features, and not treated as a regular field. <p>
The layer name may be of the form "schema.table". The schema must exist, and the
user needs to have write permissions for the target and the public schema.<p>
<p>Starting with GDAL 1.7.0, if the user defines the environment variable
<pre>PG_SKIP_VIEWS=YES</pre> (and does not specify tables=), only the regular
user tables will be treated as layers. The default action is to include the
views. This variable is particularly useful when you want to copy the data into
another format while avoiding the redundant data from the views.
<h2>Named views</h2>
When PostGIS is enabled for the accessed database, named views are supported, provided that
there is an entry in the <i>geometry_columns</i> tables. But, note that the AddGeometryColumn() SQL
function doesn't accept adding an entry for a view (only for regular tables). So, that must usually
be done by hand with a SQL statement like :
<pre>"INSERT INTO geometry_columns VALUES ( '', 'public', 'name_of_my_view', 'name_of_geometry_column', 2, 4326, 'POINT');"</pre>
Starting with GDAL 1.6.0, it is also possible to use named views without inserting a row in the geometry_columns
table. For that, you need to explicitly specify the name of the view in the "tables=" option of the
connection string. See above. The drawback is that OGR will not be able to report a valid SRS and figure out
the right geometry type.
<h2>Retrieving FID of newly inserted feature</h2>
Starting with OGR 1.8.0, and for PostgreSQL >= 8.2 databases, the FID of a feature (i.e. usually the
value of the OGC_FID column for the feature) inserted into a table with CreateFeature(), in non-copy
mode, will be retrieved from the database and can be obtained with GetFID(). One side-effect of this
new behaviour is that you must be careful if you re-use the same feature object in a loop that makes
insertions. After the first iteration, the FID will be set to a non-null value, so at the second iteration,
CreateFeature() will try to insert the new feature with the FID of the previous feature, which will fail as
you cannot insert 2 features with same FID. So in that case you must explicitly reset the FID before calling CreateFeature(),
or use a fresh feature object.<p>
Snippet example in Python :
<pre>
feat = ogr.Feature(lyr.GetLayerDefn())
for i in range(100):
feat.SetFID(-1) # Reset FID to null value
lyr.CreateFeature(feat)
print('The feature has been assigned FID %d' % feat.GetFID())
</pre>
or :
<pre>
for i in range(100):
feat = ogr.Feature(lyr.GetLayerDefn())
lyr.CreateFeature(feat)
print('The feature has been assigned FID %d' % feat.GetFID())
</pre>
OGR < 1.8.0 behaviour can be obtained by setting the configuration option OGR_PG_RETRIEVE_FID to FALSE.<p>
<h2>Caveats</h2>
<ul>
<li> The type recognition logic is currently somewhat impoverished.
The types "INT*" and "NUMERIC(width,0)" are mapped to integer, the types
"FLOAT*" and "NUMERIC(width,precision>0)" are mapped to real, date, time,
timestamp and datetime are handled as date types and all other
types are just treated as strings. <p>
<li> A sequence object called <tablename>_ogc_fid_seq is created for new
tables (layer)<p>
<li> Sequential reading is done within a single transaction. Any attempts
to write to a layer within a sequential read will likely result in a "BEGIN"
while already within a transaction type of error message.<p>
</ul>
<h3>Advanced Examples</h3>
<ul>
<li>
<p>
This example shows using ogrinfo to list only the layers specified by the <i>tables=</i> options. (Starting with GDAL 1.6.0)<p>
<pre>
ogrinfo -ro PG:'dbname=warmerda tables=table1,table2'
</pre>
</li>
<li>
<p>
This example shows using ogrinfo to query a table 'foo' with multiple geometry columns ('geom1' and 'geom2'). (Starting with GDAL 1.6.0) <p>
<pre>
ogrinfo -ro -al PG:dbname=warmerda 'foo(geom2)'
</pre>
</li>
<li>
<p>
This example show how to list only the layers inside the schema apt200810 and apt200812.
The layer names will be prefixed by the name of the schema they belong to. (Starting with GDAL 1.7.0)<p>
<pre>
ogrinfo -ro PG:'dbname=warmerda schemas=apt200810,apt200812'
</pre>
</li>
<li>
<p>
This example shows using ogrinfo to list only the layers inside the schema named apt200810.
Note that the layer names will not be prefixed by apt200810 as only one schema is listed. (Starting with GDAL 1.7.0)<p>
<pre>
ogrinfo -ro PG:'dbname=warmerda schemas=apt200810'
</pre>
</li>
<li>
<p>
This example shows how to convert a set of shapefiles inside the apt200810 directory into an
existing Postgres schema apt200810. In that example, we could have use the schemas= option instead. (Starting with GDAL 1.7.0)<p>
<pre>
ogr2ogr -f PostgreSQL "PG:dbname=warmerda active_schema=apt200810" apt200810
</pre>
</li>
<li>
<p>
This example shows how to convert all the tables inside the schema apt200810 as a set of shapefiles inside the apt200810 directory.
Note that the layer names will not be prefixed by apt200810 as only one schema is listed (Starting with GDAL 1.7.0)<p>
<pre>
ogr2ogr apt200810 PG:'dbname=warmerda schemas=apt200810'
</pre>
</li>
<li>
<p>
This example shows how to overwrite an existing table in an existing schema. Note the use of -nln to specify the qualified layer name.<p>
<pre>
ogr2ogr -overwrite -f PostgreSQL "PG:dbname=warmerda" mytable.shp mytable -nln myschema.mytable
</pre>
Note that using -lco SCHEMA=mytable instead of -nln wouldn't have worked in that case
(see <a href="http://trac.osgeo.org/gdal/ticket/2821">#2821</a> for more details).<p>
If you need to overwrite many tables located in a schema at once, the -nln option is not
the more appropriate, so it might be more convenient to use the active_schema connection
string (Starting with GDAL 1.7.0). The following example will overwrite, if necessary,
all the PostgreSQL tables corresponding to a set of shapefiles inside the apt200810 directory :
<pre>
ogr2ogr -overwrite -f PostgreSQL "PG:dbname=warmerda active_schema=apt200810" apt200810
</pre>
</li>
</ul>
<h3>See Also</h3>
<ul>
<li> <a href="drv_pg.html">OGR PostgreSQL driver Information</a><p>
</ul>
</html>
|
