PostgreSQL/PostGIS: Interaction with Other Applications

offbeatlossΔιαχείριση Δεδομένων

22 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

267 εμφανίσεις

PostgreSQL/PostGIS:
Interaction with Other
Applications
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
PostgreSQL/PostGIS Support
While PostgreSQL and PostGIS are useful for storing and
managing geospatial data and attributes associated with those
data, the data stored in the database achieve their value
through use in other applications.

GRASS

MapServer

uDig

GeoTools, GeoServer

Quantum GIS

OGR

Safe Software FME

Cadcorp SIS

IONIC RedSpider

others ...
2
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Query/Import/Export with OGR
The OGR vector data library includes support for read/write
access to PostGIS enabled PostgreSQL databases. If the version
of OGR on your system supports PostGIS, you will see a listing
for
PostgreSQL
in the output of the
ogrinfo --formats

command.
>
ogrinfo --formats
...
-> “PostgreSQL” (read/write)
...
If your version of OGR supports PostgreSQL/PostGIS, you may
use the ogrinfo and ogr2ogr commands to query a local or
remote PostGIS/PostgreSQL server
3
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Query with OGR
The available PostGIS tables (layers) available from a specified
database server may be obtained through a basic
ogrinfo

query.
>
ogrinfo -ro PG:'host=db.archaeoworld.com user=geog499
password=boguspw dbname=gnis'
INFO: Open of `PG:host=db.archaeoworld.com user=geog499
password=boguspw dbname=gnis'
using driver `PostgreSQL' successful.
: fips55_geom
Early versions of OGR did not access the
geometry_columns

table for information about geospatial tables, and therefore
provide listings of system tables and spatial tables in the
specified database.
4
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Query with OGR
Once the name of the layer(s) in the specified database have
been determined, the summary layer attributes may be
obtained using ogrinfo -s.
>
ogrinfo -ro -s PG:'host=db.archaeoworld.com user=geog499
password=******** dbname=gnis' fips55_geom
INFO: Open of `PG:host=db.archaeoworld.com user=geog499
password=******** dbname=gnis'
using driver `PostgreSQL' successful.
Layer name: fips55_geom
Geometry: Unknown (any)
Feature Count: 158553
Extent: (-176.658005, -14.361000) - (178.878006, 71.298004)
<continued ...>
5
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Query with OGR
More output ...
>
Layer SRS WKT:
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84",6378137,298.257223563,
AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4326"]]
fips55_uid: Integer (0.0)
feat_class: String (0.0)
feat_name: String (0.0)
fips_class: String (0.0)
state_alpha: String (0.0)
county_name: String (0.0)
6
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Import with OGR
OGR provides an efficient mechanism for importing data into
PostgreSQL/PostGIS through the ogr2ogr command line utility.
In essence, the import is accomplished through the conversion
of one data format (i.e. shapefile) to another format
(PostgreSQL/PostGIS)
>
ogr2ogr -f PostgreSQL
PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499' -a_srs
EPSG:4326 tgrcnty00shp tgrcnty0
This command imports a shapefile representing the New
Mexico counties into PostgreSQL/PostGIS. To import the data,
the required connection information for the database must be
provided:
PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499'
7
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Import with OGR
Since the provided shapefile does not have an associated .prj
file (defining the projection of the dataset), the destination
coordinate system must be specified in the command:
-a_srs EPSG:4326
Finally, the source dataset
and
layer are provided to specify the
exact data to be imported.
tgrcnty00shp tgrcnty0
8
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Import with OGR
The result of this import may be checked through execution of
the ogrinfo command:
>
ogrinfo -s PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499' tgrcnty0
INFO: Open of
`PG:host=db.archaeoworld.com
user=geog499 dbname=geog499'
using driver `PostgreSQL' successful.
Layer name: tgrcnty0
Geometry: Unknown (any)
Feature Count: 34
Extent: (-109.050781, 31.332279) -
(-103.002449, 37.000313)
<continued ...>
9
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Import with OGR
Additional output ...
Layer SRS WKT:
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84"
6378137,298.257223563,
AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4326"]]
area: Real (13.3)
perimeter: Real (13.3)
tgr_: Integer (11.0)
tgr_id: Integer (11.0)
fipsstco: String (5.0)
fips: String (3.0)
county: String (66.0
)
10
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
OGR Where Clauses
Queries against an OGR dataset based upon PostgreSQL/
PostGIS may provide an SQL
where
clause that limits the
information returned to those records that match the specified
where
clause
>
ogrinfo -s PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499' -where
"county = 'Bernalillo'" tgrcnty0
INFO: Open of `PG:host=db.archaeoworld.com
user=geog499 dbname=geog499'
using driver `PostgreSQL' successful.
Layer name: tgrcnty0
Geometry: Unknown (any)
Feature Count: 1
Extent: (-107.196762, 34.869091) - (-106.150146,
35.219677)
<continued ...>
11
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
OGR Where Clauses
Additional Output
Layer SRS WKT:
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84",6378137,298.257223563,
AUTHORITY["EPSG","7030"]],
AUTHORITY["EPSG","6326"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4326"]]
area: Real (13.3)
perimeter: Real (13.3)
tgr_: Integer (11.0)
tgr_id: Integer (11.0)
fipsstco: String (5.0)
fips: String (3.0)
county: String (66.0)
12
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Exporting PostGIS data with OGR
The process of exporting data from PostgreSQL/PostGIS also
uses ogr2ogr to essentially convert data from the PostgreSQL/
PostGIS
format
to another format (like shapefile or GML).
>
ogr2ogr -f GML tgrcnty0.gml
PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499' -where
"county = 'Bernalillo'" tgrcnty0
This command will access the
tgrcnty0
table in PostgreSQL/
PostGIS, extract the record(s) that meet the specification in
the
where
clause, retrieve those records, and output a GML
document that contains the retrieved data.
13
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Exporting PostGIS data with OGR
The resulting GML file contains the following:
<?xml version="1.0" encoding="utf-8" ?>
<ogr:FeatureCollection
xmlns:xsi="http://www.w3c.org/2001/XMLSchema-instance"
xsi:schemaLocation=". tgrcnty0.xsd"
xmlns:ogr="http://ogr.maptools.org/"
xmlns="http://ogr.maptools.org/"
xmlns:gml="http://www.opengis.net/gml">
<gml:boundedBy>
<gml:Box>
<gml:coord><gml:X>-107.19676208</
gml:X><gml:Y>34.86909103</gml:Y></gml:coord>
<gml:coord><gml:X>-106.15014648</
gml:X><gml:Y>35.21967697</gml:Y></gml:coord>
</gml:Box>
</gml:boundedBy>
<continued ...>
14
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Exporting PostGIS data with OGR
More output
<gml:featureMember>
<tgrcnty0 fid="0">
<area> 0.299</area>
<perimeter> 2.703</perimeter>
<tgr_>17</tgr_>
<tgr_id>16</tgr_id>
<fipsstco>35001</fipsstco>
<fips>1 </fips>
<county>Bernalillo
</county>
15
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Exporting PostGIS data with OGR
More output
<ogr:geometryProperty><gml:Polygon><gml:outerBoundaryIs><gml:
LinearRing><gml:coordinates>-107.19676208,35.21936035
-107.18930817,35.21956635 -107.18899536,35.21957779
-107.18228149,
...
-107.1914978,35.20697784 -107.19606781,35.21770096
-107.19676208,35.21936035</gml:coordinates></
gml:LinearRing></gml:outerBoundaryIs></gml:Polygon></
ogr:geometryProperty>
</tgrcnty0>
</gml:featureMember>
</ogr:FeatureCollection>
16
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Data Visualization and Management
in GRASS
GRASS has a number of tools for interacting with PostgreSQL/
PostGIS

ps.postgisdb
- A command that may be used to create
and destroy PostGIS databases.

db.connect
and
v.db.connect
- Commands that establish
a database connection and link a vector map to a database
table (for attributes only)

v.in.ogr
-
Import
an OGR supported dataset into GRASS

v.external
- Create a
read-only
connection to an external
OGR supported dataset
Key to GRASS’ ability to interact with PostGIS enabled
PostgreSQL databases depends upon how GRASS was built
(enabling PostgreSQL support) and the formats supported by a
specific installation of OGR (try
ogrinfo --formats
).
17
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
GRASS Data Import from PostGIS
External PostgreSQL/PostGIS data may be imported into
GRASS for use as a local dataset by executing the v.in.ogr
command, using the correct OGR connection specification to
define the database to be accessed.
>
v.in.ogr
dsn=PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499'
layer=tgrcnty0 output=tgrcnty0
location=geo_wgs84
This command will import the layer (table)
tgrcnty0
from the
class database (specified with the PG specification in the dsn),
create a new location named
geo_wgs84
and import the data
into a layer named
tgrcnty0
in that new location.
18
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
GRASS Data Import from PostGIS
The imported data may be visualized by first opening GRASS
using the newly created location, then entering the standard
display commands:
>
d.mon start=x0
using default visual which is TrueColor
ncolors: 16777216
Graphics driver [x0] started
>
d.vect map=tgrcnty0
19
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Display of External PostGIS Data in
GRASS
External PostgreSQL/PostGIS data may be displayed in GRASS
(with other local or remote GRASS data) through use of the
v.external GRASS command. This command creates a read-only
linkage to the external data source and retrieves the data when
requested for display or analysis.
>
v.external
dsn=PG:'host=db.archaeoworld.com
user=geog499 dbname=geog499'
output=tgrcnty0_external layer=tgrcnty0
In contrast to the previously executed v.in.ogr command, this
command registers an external connection to the specified
database (defined by the
dsn
) and layer (specified by the
layer

parameter), and names the connection according to the
output

parameter. This layer may then be accessed like other GRASS
vector datasets, but will be based upon the current data in the
database.
20
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Display of External PostGIS Data in
GRASS
A standard
d.vect
command results in the following:
Which is the same result as displaying the data that were
imported into GRASS.
21
Open Source Geographic Information Technologies - PostgreSQL/PostGIS: Interaction with Other Applications
Display of External PostGIS Data in
GRASS
Caveats:

The version of GRASS on the class disks generates an
error message when creating the connection and when
displaying the data
ERROR 1: ERROR: column "fid" does not exist

While this error is not necessarily fatal, it may impact the
display of some vector objects.

The problem is supposed to have been addressed in the
current GRASS release.
22