v.out.postgis.html

<h2>DESCRIPTION</h2>

<em>v.out.postgis</em> exports an existing GRASS vector map layer to
PostGIS feature table. Features without category are skipped.

<p>
By default GRASS GIS topological features are converted into simple
features
(see <a href="http://www.opengeospatial.org/standards/sfa">OGC Simple
Feature Access</a> specification for details). Flag <b>-l</b> allows
to export vector features as topological elements stored
in <a href="http://postgis.refractions.net/docs/Topology.html">PostGIS
Topology</a> schema. Note that topological export requires PostGIS
version 2 or later.

<p>
Additional creation options can be defined by <b>options</b> parameter:
<ul>
  <li><tt>FID=&lt;column&gt;</tt> - name of column which will be used as
  primary key (feature id), default: <tt>fid</tt></li>
  <li><tt>GEOMETRY_NAME=&lt;column&gt;</tt> name of column which will
  be used for storing geometry data in feature table, default: <tt>geom</tt></li>
  <li><tt>SPATIAL_INDEX=YES|NO</tt> - enable/disable creating spatial
  index on geometry column, default: <tt>YES</tt></li>
  <li><tt>PRIMARY_KEY=YES|NO</tt> - enable/disable adding primary key
  on FID column, default: <tt>YES</tt></li>
  <li><tt>SRID=&lt;value&gt;</tt> - spatial reference identifier,
  default: not defined</li>
</ul>

PostGIS Topology related options (relevant only for <b>-l</b> flag):

<ul>
  <li><tt>TOPOSCHEMA_NAME=&lt;schema name&gt;</tt> - name of PostGIS
  Topology schema, default: <tt>topo_&lt;input&gt;</tt></li>
  <li><tt>TOPOGEOM_NAME=&lt;column&gt;</tt> - name of column which
  will be used for storing topogeometry data in feature table,
  default: <tt>topo</tt></li>
  <li><tt>TOPO_TOLERANCE=&lt;value&gt;</tt> - tolerance for PostGIS
  Topology schema,
  see <a href="http://www.postgis.net/docs/manual-2.0/CreateTopology.html">CreateTopology</a>
  function for defails, default: <tt>0</tt></li>
  <li><tt>TOPO_GEO_ONLY=YES|NO</tt> - store in PostGIS Topology schema
  only data relevant to Topo-Geo data model,
  default: <tt>NO</tt></li>
</ul>

<p>
Creation <b>options</b> are comma-separated pairs
(<tt>key=value</tt>), the options are case-insensitive. Note
that <b>options</b> defined
by <em><a href="v.external.out.html">v.external.out</a></em> are
ignored by <em>v.out.postgis</em>.

<p>
<em>v.out.postgis</em> optionally also creates a new vector map in the
current mapset if <b>output_link</b> is defined.

<h2>NOTES</h2>

By default <em>v.out.postgis</em> exports vector data as <em>simple
features</em>, ie. boundaries and centroids (forming topological
areas) become polygons, isles become holes. Geometry of simple feature
elements is stored in PostGIS feature table in the column named
&quot;geom&quot;. Name of the geometry column can be changed
by <b>options=</b><tt>GEOMETRY_NAME=&lt;column&gt;</tt>. Note that for
exporting vector features as simple features can be alternatively
used <a href="http://gdal.org/drv_pg.html">PostgreSQL driver</a>
from <a href="http://gdal.org/">OGR</a> library
through <em><a href="v.out.ogr.html">v.out.ogr</a></em> module.

<p>
Also note that it's allowed to store in the feature table only
features of the same type, ie. mixing of points and lines is not
currently possible. The feature type is determined for output feature
table from the first vector feature accessing data sequentially
(<b>type=auto</b>). Vector features of other types are during export
skipped. User can choose preferable feature type by <b>type</b>
parameter. Only single type is currently allowed (see TODO section for
details).

<p>
<em>v.out.postgis</em> currently supports only three basic output
simple feature types: Points, Linestrings and Polygons. Also 3D
features of the same type are supported, eg. 3D points are exported
as <tt>PointZ</tt> simple feature. Faces are exported as 3D
polygons. 3D features are written to the output automatically if input
vector map is 3D. If <b>-2</b> flag is given then the output is always
2D (z-coordinate is silently ignored for 3D input vector maps).

<p>
Multigeometries are not currently supported. Features with the same
category are exported as multiple singe features.

<p>
<em>v.out.postgis</em> also allows exporting vector features as
<em>topological elements</em>
in <a href="http://postgis.refractions.net/docs/Topology.html">PostGIS
Topology</a> schema. PostGIS Topology extension uses three tables to
store basic topological elements which forms topological objects like
areas or isles in GRASS terminology. <em>Nodes</em> (0-dimensional
topological elements) are stored in &quot;node&quot;
table, <em>edges</em> (1-dimensional elements) in &quot;edge&quot;
table and <em>faces</em> (2-dimensional elements) in &quot;face&quot;
table.

<ul>
  <li>GRASS nodes are stored in <i>node</i> table</li>
  <li>GRASS points are stored in <i>node</i> table as regular nodes</li>
  <li>GRASS centroids are stored in <i>node</i> table as regular nodes
    (&quot;containing_face&quot; refers to related area)</li>
  <li>GRASS lines are stored in <i>edge</i> table</li>
  <li>GRASS boundaries are stored in <i>edge</i> table</li>
  <li>GRASS areas are stored in <i>face</i> table</li>
</ul>

Tables <i>node</i>, <i>edge</i> and <i>face</i> are stored in given
topological schema. By default <em>v.out.postgis</em> defines its name
as <tt>topo_&lt;input&gt;</tt>. Alternatively, the name for topology
schema can be defined
by <b>options=</b><tt>TOPOSCHEMA_NAME=&lt;name&gt;</tt>.

<h2>EXAMPLES</h2>

<h3>Export Simple Features</h3>

Export vector map &quot;urbanarea&quot; as feature table &quot;urbanarea&quot;
located in database &quot;grass&quot;, schema &quot;public&quot;. Note that this
database schema is automatically used when not defined by the user.

<div class="code"><pre>
v.out.postgis input=urbanarea output="PG:dbname=grass"
</pre></div>

GRASS areas are converted into polygons, isles into holes. We can
check the number or created polygons by simple SQL query below.

<div class="code"><pre>
db.select driver=pg database=grass \
 sql="SELECT ST_GeometryType(geom) as geom_type, count(*) from urbanarea group by geom_type"

geom_type|count
ST_Polygon|657
</pre></div>

<i>Note:</i> same procedure can be done
by <em><a href="v.out.ogr.html">v.out.ogr</a></em> module, eg.

<div class="code"><pre>
v.out.ogr input=urbanarea output="PG:dbname=grass" format=PostgreSQL
</pre></div>

In this case GRASS vector data are exported to PostGIS database using
OGR library, namely using PostgreSQL driver. Contrary to
the <em><a href="v.out.ogr.html">v.out.ogr</a></em>
module, <em>v.out.postgis</em> is using directly PostGIS data provider
which is part of GRASS vector engine. Beside
that, <em>v.out.postgis</em> is optimized for PostGIS export including
topological access to the data.

<h3>Export data into specific database schema</h3>

Database schema for storing exported data can be defined
by <b>output_layer</b> as
<tt>&lt;schema_name&gt;.&lt;table_name&gt;</tt>. If the specified
schema doesn't exist in the database, then it's automatically created.

<p>
Export vector map &quot;bridges&quot; as feature table in database
schema &quot;grassout&quot;.

<div class="code"><pre>
v.out.postgis input=bridges output="PG:dbname=grass" output_layer=grassout.bridges
</pre></div>

<h3>Export data with creation options</h3>

Example below demonstrates how to define name for geometry column and
disable building spatial index. Spatial reference system is defined
by <tt>srid</tt> identifier which corresponds in this case with EPSG
3358 (North Carolina dataset).

<div class="code"><pre>
v.out.postgis input=roadsmajor output="PG:dbname=grass" options="GEOMETRY_NAME=wkb_geometry,SPATIAL_INDEX=NO,SRID=3358"
</pre></div>

<h3>Link exported data</h3>

Exported data can be linked as vector map created in the current
mapset by specifying <b>output_link</b> parameter. In the example below
vector map &quot;busstopsall&quot; from PERMANENT mapset is exported
into &quot;grass&quot; PostGIS database. <em>v.out.postgis</em> after
successful export also creates in the current mapset GRASS vector map
as a link to the PostGIS feature table.

<div class="code"><pre>
v.out.postgis input=busstopsall@PERMANENT output="PG:dbname=grass" output_link=busstopsall_pg
</pre></div>

Created link can be checked
by <em><a href="v.info.html">v.info</a></em>:

<div class="code"><pre>
 v.info busstopsall_pg

...
 |----------------------------------------------------------------------------|
 | Map format:      PostGIS (PostgreSQL)                                      |
 | DB table:        public.busstopsall                                        |
 | DB name:         grass                                                     |
 | Geometry column: geom                                                      |
 | Feature type:    point                                                     |
 | Topology:        pseudo (simple features)                                  |
 |----------------------------------------------------------------------------|
...

</pre></div>

<h3>Export data without attributes</h3>

<em>v.out.postgis</em> allows ignoring attributes when exporting
vector features by specifying <b>-t</b> flag.

Command below exports vector features without attributes. The feature
will contain only two columns, the fid and geometry column.

<div class="code"><pre>
v.out.postgis -t input=railroads output="PG:dbname=grass"
</pre></div>

<h3>Export topological data</h3>

By default <em>v.out.postgis</em> exports data as simple
features. Flag <b>-l</b> allows exporting data as topological elements
instead of simple features. Export topological elements is stored in
<a href="http://postgis.refractions.net/docs/Topology.html">PostGIS
Topology</a> schema.

<div class="code"><pre>
v.out.postgis -l input=busroutesall output="PG:dbname=grass"
</pre></div>

For more info about PostGIS Topology implementation in GRASS see
the <a href="https://grasswiki.osgeo.org/wiki/PostGIS_Topology">wiki
page</a>.

<h2>TODO</h2>

<ul>
  <li>Multi-feature export</li>
  <li>Allow mixed features (points, lines)</li>
  <li>Support other simple feature types like GeometryCollection and others</li>
  <li>Implement missing options
  from <em><a href="v.out.ogr.html">v.out.ogr</a></em>: <b>-a</b>, <b>-s</b>, <b>-c</b>, <b>-p</b>, <b>-n</b></li>
  <li>Add options: <b>cats</b>, <b>where</b></li>
</ul>

<h2>REQUIREMENTS</h2>

<ul>
  <li>PostGIS 2.x or later for topological export (flag <b>-l</b>)
</ul>

<h2>REFERENCES</h2>

<ul>
  <li><a href="http://www.opengeospatial.org/standards/sfa">OGC Simple Feature Access</a> specification</li>
  <li><a href="http://postgis.net/docs/Topology.html">PostGIS Topology</a> documentation</li>
  <li><a href="https://grass.osgeo.org/programming8/vlibPg.html">GRASS-PostGIS data provider</a></li>
</ul>

<h2>SEE ALSO</h2>

<em>
  <a href="v.out.ogr.html">v.out.ogr</a>,
  <a href="v.external.html">v.external</a>,
  <a href="v.external.out.html">v.external.out</a>,
  <a href="v.in.ogr.html">v.in.ogr</a>
</em>

<p>
See also <a href="https://grasswiki.osgeo.org/wiki/PostGIS">PostGIS</a>
and <a href="https://grasswiki.osgeo.org/wiki/PostGIS_Topology">PostGIS
Topology</a> wiki page from GRASS User Wiki.

<h2>AUTHOR</h2>

Martin Landa, Czech Technical University in Prague, Czech Republic
(development supported by Fondazione Edmund Mach and Comune di Trento, Italy)