PostGIS: Difference between revisions

From GRASS-Wiki
Jump to navigation Jump to search
m (\*output=PG:dbname=pgis_nc*\)
 
(24 intermediate revisions by 3 users not shown)
Line 1: Line 1:
When accessing [http://www.postgis.net PostGIS] data in GRASS you can
* [[#Import into GRASS|import]] PostGIS (simple features) data into GRASS topological format or,
* [[#Link to GRASS|link]] PostGIS data as GRASS vector maps
See also [[Working with external data in GRASS 7|working with external data in GRASS 7]].
See also [[Working with external data in GRASS 7|working with external data in GRASS 7]].
__TOC__
__TOC__
== Help pages ==
== Help pages ==


* {{cmd|vectorintro|desc=Vector data processing in GRASS GIS}} help page
* {{cmd|databaseintro|desc=Database management in GRASS GIS}} help page
* {{cmd|databaseintro|desc=Database management in GRASS GIS}} help page
* {{cmd|sql|desc=SQL support in GRASS GIS}} help page
* {{cmd|sql|desc=SQL support in GRASS GIS}} help page
Line 8: Line 13:
* {{cmd|grass-pg|desc=PostgreSQL DB driver in GRASS}} help page
* {{cmd|grass-pg|desc=PostgreSQL DB driver in GRASS}} help page
* {{cmd|grass-odbc|desc=ODBC DB driver in GRASS}} help page
* {{cmd|grass-odbc|desc=ODBC DB driver in GRASS}} help page
== Link to external dataset ==
* input maps: {{cmd|v.external|version=70}}
**in GRASS 6 the map links created by {{cmd|v.external}} are ''read only''
**GRASS 7 supports also write access to the map links, see [[Working with external data in GRASS 7#Vector data|working with external data in GRASS 7]] for details
* output maps: {{cmd|v.external.out|version=70}} (GRASS 7 only)


== Import into GRASS ==
== Import into GRASS ==


# {{cmd|v.in.ogr}} (import only geometry column from postgresql/postgis)
# {{cmd|v.in.ogr}} - imports geometry data from PostGIS into native GRASS vector format, attributes are stored using default DB settings (see {{cmd|db.connect}} for details)
# {{cmd|v.clean}}
# {{cmd|v.clean}} - clean up data after import


There is a fundamental difference between the PostGIS format which is
''Note 1:'' There is a fundamental difference between the PostGIS format which is
non-topological (OGC simple feature-based) and the internal GRASS format which is topological and
non-topological (OGC simple feature-based) and the internal GRASS format which is topological and
which, thus, does not really allow for overlapping polygons. You can
which, thus, does not really allow for overlapping polygons. You can
digitize them, but they are not really useful...
digitize them, but they are not really useful...
''Note 2:'' GRASS 7 allows also reading and writing topological PostGIS data (see [http://www.postgis.org/documentation/manual-2.0/Topology.html PostGIS Topology] for details).


{{YouTube|JoqIkL2VRVc|desc=Importing PostGIS layers into GRASS}}
{{YouTube|JoqIkL2VRVc|desc=Importing PostGIS layers into GRASS}}
Line 30: Line 30:
== Link to GRASS ==
== Link to GRASS ==


You can register a PostGIS layer into GRASS using {{cmd|v.external}}, eg. PostGIS layer 'lakes' from database 'pgis_nc'
* input maps: {{cmd|v.external}}
**GRASS 7 supports also write access to the map links, see [[Working with external data in GRASS 7#Vector data|working with external data in GRASS 7]] for details
* output maps: {{cmd|v.external.out}}


v.external dsn=PG:dbname=pgis_nc layer=lakes
You can register a PostGIS table in GRASS using {{cmd|v.external}}. The command below creates a link to the table 'lakes' located in database 'pgis_nc'.


It will create in the current mapset new vector map 'lakes', the module also builds pseudo-topology for this map,
# list available PG tables
v.external input="PG:host=myserver user= myuser dbname=pgis_nc" -l
# link PG table into GRASS GIS as vector map
v.external input="PG:host=myserver user= myuser dbname=pgis_nc" layer=lakes


To link a PostGIS layer from non-public schema
The command creates in the current mapset new vector map 'lakes', the module also builds pseudo-topology for this map,


v.external dsn=PG:<connection sting> layer=<schema>.<layer> output=<layer>
To link a PostGIS table from non-public schema


''Important note:'' In GRASS 6 this link is read-only, in GRASS 7 it's possible to modify linked layers directly via OGR library, see [[Working with external data in GRASS 7#Vector data|working with external data in GRASS 7]].
v.external input=PG:<connection sting> layer=<schema>.<layer> output=<layer>


You can also link a GRASS layer to a PostgreSQL attribute table, though {{cmd|v.db.connect}}.
''Important note:'' In GRASS 6 the created links area ''read-only''. GRASS 7 also allows to modify linked layers directly via OGR or PostGIS data provider, see [[Working with external data in GRASS 7#Vector data|working with external data in GRASS 7]] for details.


{{YouTube|eB3_SmE3E9Q|desc=Link PostGIS layers as GRASS vector maps}}
{{YouTube|eB3_SmE3E9Q|desc=Link PostGIS layers as GRASS vector maps}}
Line 48: Line 54:
== Export to PostGIS ==
== Export to PostGIS ==


To export GRASS vector map layer to PostGIS layer use {{cmd|v.out.ogr}}, eg.
To export GRASS vector map layer as PostGIS table use {{cmd|v.out.ogr}}, eg.


  v.out.ogr in=lakes@PERMANENT dsn=PG:dbname=pgis_nc format=PostgreSQL type=area
  v.out.ogr in=lakes@PERMANENT output=PG:dbname=pgis_nc format=PostgreSQL type=area


Note that exporting data can be quite time-consuming task, especially when input vector map attributes are stored in DBF format. It's recommended to store attribute data in {{cmd|grass-sqlite|desc=SQLite format}} rather then in old-fashioned {{cmd|grass-dbf|desc=DBF format}}. For example when exporting vector map 'lakes' from [http://grass.osgeo.org/download/data.php North Carolina] sample dataset.
In GRASS 7 exists also specialized module {{cmd|v.out.postgis}} which allows to export GRASS vector data as simple features (similarly to {{cmd|v.out.ogr}}) or in topological format (see [[PostGIS Topology]] for defails).


* Attributes in DBF format
Note that exporting data can be quite time-consuming task, especially when input vector map attributes are stored in DBF format. It's recommended to store attribute data in {{cmd|grass-sqlite|desc=SQLite format}} rather then in old-fashioned {{cmd|grass-dbf|desc=DBF format}}.  
 
real    1m15.072s
user    1m6.160s
sys    0m3.824s
 
* Attributes in SQLite format
 
real    0m11.796s
user    0m5.564s
sys    0m4.148s


{{YouTube|DTMmmcVpJKk|desc=Creating model which exports all GRASS vector maps from given mapset to PostGIS database}}
{{YouTube|DTMmmcVpJKk|desc=Creating model which exports all GRASS vector maps from given mapset to PostGIS database}}
Line 76: Line 72:
}}
}}


See also [[wxGUI Modeler]].
See also [[wxGUI Graphical Modeler]].


== Direct access to PostGIS data ==
== Direct access to PostGIS data (GRASS 7 only) ==


''Important note:'' Direct read/write access is implemented only in GRASS 7.
''Important note:'' Direct read/write access is available only in GRASS 7.


=== Direct read access ===
=== Direct read access ===


GRASS 7 enables the users to access PostGIS layers directly via virtual mapset 'OGR' and  modules parameters <code>map=OGR_datasource@OGR</code> and <code>layer=OGR_layer</code>, eg. to access PostGIS layer 'lakes' from database 'pgis_nc'
GRASS 7 allows to access PostGIS data directly via virtual mapset called 'OGR'. In this case parameter <tt>map</tt> or <tt>input</tt> is used for OGR data source and <tt>layer</tt> for table. In the command bellow is accessed PostGIS table 'lakes' from database 'pgis_nc'.


  v.info map=PG:dbname=pgis_nc@OGR layer=lakes
  v.info map=PG:dbname=pgis_nc@OGR layer=lakes


The direct read access avoids need of creating a link via {{cmd|v.external|version=70}} and accessing PostGIS data directly without creating any data elements in the current mapset. The main drawback of direct read access is that the pseudo-topology is built each time when accessing the data.
To access table from non-public schema (or schemas which are not in the search path), type
 
<pre>
v.info map=PG:<OGR data source> layer=<schema>.<table>
</pre>
 
When accessing external data directly you don't need to create a map link by {{cmd|v.external}}. The major drawback of direct read access is that the pseudo-topology is built each time when accessing the data. From this point of view the direct access is useful when accessing data once or few times, otherwise is better to link the data by {{cmd|v.external}}.


=== Direct write access ===
=== Direct write access ===


''Note:'' direct write access via OGR library is currently under development (GRASS 7 only).
GRASS 7 allows to write output vector map directly via OGR library. For defining output vector data format is designed {{cmd|v.external.out}} module. For example


GRASS 7 allows to write output vector map directly via OGR library. For defining output vector data format is designed {{cmd|v.external.out|version=70}} module. For example
v.external.out input=PG:dbname=pgis_nc format=PostgreSQL


v.external.out dsn=PG:dbname=pgis_nc format=PostgreSQL
causes that output vector data are written in PostGIS format in the database 'pgis_nc'. GRASS also creates automatically for every PostGIS table a new vector map in the current mapset as the link to the PostGIS table.  


causes that every newly created vector map will be stored as PostGIS layer in database 'pgis_nc' without any data elements created in the current mapset. Such PostGIS layer can be linked afterwards via {{cmd|v.external|version=70}} or accessed directly as described in the section above. For example
PostGIS data can be accessed by the map link or directly as described in the section above. For example


  v.extract input=lakes out=reservoir where="FTYPE = 'RESERVOIR'"
  v.extract input=lakes out=reservoir where="FTYPE = 'RESERVOIR'"
v.info map=reservoir
# or
  v.info map=PG:dbname=pgis_nc@OGR layer=reservoir
  v.info map=PG:dbname=pgis_nc@OGR layer=reservoir


Line 108: Line 113:


== See also ==
== See also ==
* [[Working with external data in GRASS 7]]
* [[PostGIS Topology]]
== External links ==


* [http://www.surfaces.co.il/?p=645 Tutorial by Micha Silver]
* [http://www.surfaces.co.il/?p=645 Tutorial by Micha Silver]
* [http://www.postgis.org PostGIS]
* [http://gdal.org/ogr OGR library]
* [http://gdal.org/ogr OGR library]
* [http://www.dimensionaledge.com/main/postgis/viewshed-analysis-in-postgis-using-plr-and-grass/ Viewshed analysis in PostGIS using PL/R and GRASS] (blog post by Mark Wynter)
* [https://strk.kbt.io/projects/rttopo/ RT Topology Library]


[[Category: FAQ]]
[[Category: FAQ]]
[[Category: Vector]]
[[Category: Vector]]

Latest revision as of 03:12, 23 January 2017

When accessing PostGIS data in GRASS you can

  • import PostGIS (simple features) data into GRASS topological format or,
  • link PostGIS data as GRASS vector maps

See also working with external data in GRASS 7.

Help pages

Import into GRASS

  1. v.in.ogr - imports geometry data from PostGIS into native GRASS vector format, attributes are stored using default DB settings (see db.connect for details)
  2. v.clean - clean up data after import

Note 1: There is a fundamental difference between the PostGIS format which is non-topological (OGC simple feature-based) and the internal GRASS format which is topological and which, thus, does not really allow for overlapping polygons. You can digitize them, but they are not really useful...

Note 2: GRASS 7 allows also reading and writing topological PostGIS data (see PostGIS Topology for details).


Importing PostGIS layers into GRASS

Link to GRASS

You can register a PostGIS table in GRASS using v.external. The command below creates a link to the table 'lakes' located in database 'pgis_nc'.

# list available PG tables
v.external input="PG:host=myserver user= myuser dbname=pgis_nc" -l

# link PG table into GRASS GIS as vector map
v.external input="PG:host=myserver user= myuser dbname=pgis_nc" layer=lakes

The command creates in the current mapset new vector map 'lakes', the module also builds pseudo-topology for this map,

To link a PostGIS table from non-public schema

v.external input=PG:<connection sting> layer=<schema>.<layer> output=<layer>

Important note: In GRASS 6 the created links area read-only. GRASS 7 also allows to modify linked layers directly via OGR or PostGIS data provider, see working with external data in GRASS 7 for details.


Link PostGIS layers as GRASS vector maps

Export to PostGIS

To export GRASS vector map layer as PostGIS table use v.out.ogr, eg.

v.out.ogr in=lakes@PERMANENT output=PG:dbname=pgis_nc format=PostgreSQL type=area

In GRASS 7 exists also specialized module v.out.postgis which allows to export GRASS vector data as simple features (similarly to v.out.ogr) or in topological format (see PostGIS Topology for defails).

Note that exporting data can be quite time-consuming task, especially when input vector map attributes are stored in DBF format. It's recommended to store attribute data in SQLite format rather then in old-fashioned DBF format.


Creating model which exports all GRASS vector maps from given mapset to PostGIS database
Creating model which exports all GRASS vector maps from given mapset to PostGIS database (including schema)

1) create schema with name of input mapset

2) copy vector maps from selected mapset to the current and stores their attributes in SQLite database

3) exports GRASS vector maps to the selected PostGIS database

See also wxGUI Graphical Modeler.

Direct access to PostGIS data (GRASS 7 only)

Important note: Direct read/write access is available only in GRASS 7.

Direct read access

GRASS 7 allows to access PostGIS data directly via virtual mapset called 'OGR'. In this case parameter map or input is used for OGR data source and layer for table. In the command bellow is accessed PostGIS table 'lakes' from database 'pgis_nc'.

v.info map=PG:dbname=pgis_nc@OGR layer=lakes

To access table from non-public schema (or schemas which are not in the search path), type

v.info map=PG:<OGR data source> layer=<schema>.<table>

When accessing external data directly you don't need to create a map link by v.external. The major drawback of direct read access is that the pseudo-topology is built each time when accessing the data. From this point of view the direct access is useful when accessing data once or few times, otherwise is better to link the data by v.external.

Direct write access

GRASS 7 allows to write output vector map directly via OGR library. For defining output vector data format is designed v.external.out module. For example

v.external.out input=PG:dbname=pgis_nc format=PostgreSQL

causes that output vector data are written in PostGIS format in the database 'pgis_nc'. GRASS also creates automatically for every PostGIS table a new vector map in the current mapset as the link to the PostGIS table.

PostGIS data can be accessed by the map link or directly as described in the section above. For example

v.extract input=lakes out=reservoir where="FTYPE = 'RESERVOIR'"

v.info map=reservoir
# or
v.info map=PG:dbname=pgis_nc@OGR layer=reservoir

To switch back to GRASS native format enter

v.external.out -r

See also

External links