PostgreSQL/PostGIS: PostGIS Installation & Manual Use

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

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

326 εμφανίσεις

PostgreSQL/PostGIS:
PostGIS Installation &
Manual Use
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
PostGIS Introduction
PostGIS represents an enhancement of PostgreSQL through
the creation of new database objects and functions that enable
OGC Simple Features support within a PostgreSQL database.
Adding the PostGIS enhancements to PostgreSQL consists of
several steps:

If PostgreSQL was compiled from source: compile the
PostGIS source code within the ‘contrib’ directory of the
PostgreSQL source tree

Changes to the compilation instructions (the ‘makefile’)
must be made to enable support for PROJ4 and GEOS

If PostgreSQL was installed as a binary, install the PostGIS
binary that is compatible with the installed PostgreSQL
binary.
2
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
PostGIS Introduction
The installation process for PostGIS also requires that:

The PL/pgSQL procedural language be enabled for the
PostGIS database

The PostGIS object and function definitions be loaded into
the database

These object and function definitions are provided as
an SQL file (
lwpostgis.sql
) that may be executed in
the database to add the required objects and functions.
Once added, they are available from that point on
within the so-enabled database.

An optional, but very useful, step, is to import the
provided EPSG coordinate system identifiers into a table
within the database. This is accomplished through
execution of the provided SQL file (spatial_ref_sys.sql)
within the database.
3
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creating GIS Objects
The creation of GIS objects in a PostGIS-enabled database
requires that a table with a
geometry
column already exist for
data to be imported into. This is typically accomplished through
the execution of two SQL commands:
>
CREATE TABLE gtest (
id int4,
name varchar (20) );
>
SELECT AddGeometryColumn(
'',
'gtest',
'geom',
-1,
'LINESTRING',
2);
4
An added PostGIS Function
The Schema ID
The table
The field name
The SRID, from spatial_ref_sys
The geometry type
Dimensions
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creating GIS Objects
Once the table to which GIS objects (attributes and associated
geometries) may be added has been created, new records
representing those objects may be added using the standard
SQL insert command
>
INSERT INTO gtest (
id,
name,
geom)
VALUES (
1,
'First Geometry',
GeomFromText(
'LINESTRING(
2 3,
4 5,
6 5,
7 8)’,
-1)
);
5
An added PostGIS Function
SRID
An OGC Well-known Text (WKT)
definition of a line
x,y coord.
x,y coord.
x,y coord.
x,y coord. and end of LINESTRING function
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Geometries - WKT & WKB
When working with the OGC Simple Features Specification,
you encounter two methods for representing and storing
geometries

Well Known Text (WKT), for example

Point:
POINT(0,0)

Linestring:
LINESTRING(0 0, 1 1, 2 2)

Polygon:
POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1,2
1,2 2,1 2,1 1))

Multipoint:
MULTIPOINT(0 0,1 1)

Multilinestring:
MULTILINESTRING((0 0,1 1,2 2),(2
3,3 2,5 4))

Multipolygon:
MULTIPOLYGON(((0 0,4 0,4 4,0 4,0
0),(1 1,2 1,2 2,1 2,1 1)),((1 2,4 5,7 8,1 2)))

Geometry Collection:
GEOMETRYCOLLECTION(POINT
(0,0), LINESTRING(0 0, 1 1, 2 2))
6
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Geometries - WKT & WKB
While the WKT specification is frequently used for entering
geometries into the database, the Well-known Binary (WKB)
format is preferentially used for storage due to its greater
efficiency for complex geometries.
The current definition of the WKT and WKB specifications can
be found in the Open Geospatial Consortium’s set of
specification documents:
OpenGIS® Implementation Specification for Geographic Information
- Simple feature access - Part 1: Common architecture (OGC
05-126)
7
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creation of POLYGON Geometries
The following is an extended example of the creation of a new
table that contains POLYGON geometry types and associated
attributes.
First, the table and associated entry in the
geometry_columns

table must be created
>
CREATE TABLE gtest_poly ( id int4, name varchar
(20) );
>
SELECT AddGeometryColumn
('','gtest_poly','geom',-1,'POLYGON',2);
>
ALTER TABLE gtest_poly ADD CONSTRAINT id_pkey
PRIMARY KEY (id);
8
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creation of POLYGON Geometries
After the destination table has been created, records may be
added to it:
>
INSERT INTO gtest_poly (id,name,geom)
VALUES(1,'house',GeomFromText('POLYGON((3 1,3 6,8
6,8 1,3 1),(4 4,4 5,5 5,5 4,4 4),(6 4,6 5,7 5,7
4,6 4))',-1));
9
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creation of POLYGON Geometries
Another record:
>
INSERT INTO gtest_poly (id,name,geom)
VALUES(2,'door',GeomFromText('POLYGON((5 1,5 3,6
3,6 1,5 1))',-1));
10
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creation of POLYGON Geometries
Another record:
>
INSERT INTO gtest_poly (id,name,geom)
VALUES(3,'roof',GeomFromText('POLYGON((2 5,5.5
8,9 5,2 5))',-1));
11
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creation of POLYGON Geometries
Another record:
>
INSERT INTO gtest_poly (id,name,geom)
VALUES(4,'plane',GeomFromText('POLYGON((2 8.33,2
8,2.25 7.66,2.75 7.66,3.25 7,3.5 7,3.25 7.66,3.75
7.66,4 8,4 8.66,3.75 8.66,3.75 8.33,3.25
8.33,2.75 9,2.25 9,2.75 8.33,2 8.33),(2.1
8.1,2.25 8.1,2.25 8.23,2.1 8.23,2.1 8.1),(2.5
8,2.6 8,2.6 8.1,2.5 8.1,2.5 8),(2.7 8,2.8 8,2.8
8.1,2.7 8.1,2.7 8),(2.9 8,3 8,3 8.1,2.9 8.1,2.9
8),(3.1 8,3.2 8,3.2 8.1,3.1 8.1,3.1 8),(3.3 8,3.4
8,3.4 8.1,3.3 8.1,3.3 8))',-1));
12
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Creating Geometries from Imported
Data
PostGIS tables containing geometries may be created from
previously imported data through the use of PostGIS functions
and select statements.
For example, the
fips55
dataset, a collection of over 158,000
classified placenames with associated coordinate information
may be imported into PostgreSQL to produce the following
table:
13
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Conversion of Imported
fips55
Data to
PostGIS Geometries
The problem with the imported data, is that the provided
geographic data are not consistent with the OGC Simple
Features specification, therefore limiting the analyses that can
be performed.

Standard query methods may be used to determine
locations using simple rectangular coordinates, but more
sophisticated spatial queries are not supported

The spatial data in the original table are not usable in
some visualization applications that support the OGC
Simple Features specification
Just as we used PostGIS functions in creating new records, the
same functions may be used to create a new view of the
original data that is compliant with the OGC specification.
14
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Conversion of Imported
fips55
Data to
PostGIS Geometries
The first step in the conversion process consists of creating a
view based upon the original data that recasts the point
coordinates as an OGC WKT POINT type.
>
CREATE VIEW v_fips55 AS
SELECT
uid as fips55_uid,
feat_class,
fips_class,
state_alpha,
county_name,
GeometryFromText(
'POINT('||
to_char(primary_lon,'9999D999')||
' ' ||
to_char(primary_lat, '999D999')||
')',4326) as geom
FROM fips55
15
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Conversion of Imported
fips55
Data to
PostGIS Geometries
The result of the creation of this view may be seen by
executing a simple
select
command:
>
SELECT * FROM v_fips55
16
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Conversion of Imported
fips55
Data to
PostGIS Geometries
Depending upon the tools that will be used to access the
database, you may use the view by adding an entry to the
geometry_columns
table for the view:
>
INSERT INTO geometry_columns
{‘’,’public’,’v_fips55’,’geom’,2,4326,GEOMETRY}
This command essentially registers the
geom
column of your
view as the column that contains the geometry data for
querying and rendering.
For some applications, this is sufficient. In some situations you
will need to create a new table as opposed to the virtual table
represented by the view. This is accomplished with:
>
select * into fips55_geom from v_fips55
17
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Conversion of Imported
fips55
Data to
PostGIS Geometries
To optimize the performance of your newly created database,
you should create at least two indexed.
A primary key for the new table,
>
alter table fips55_geom add constraint
fips55_geom_pkey PRIMARY KEY (fips55_uid);
and a spatial index for the geometry column:
>
CREATE INDEX fips55_geom_geom_idx ON fips55_geom
USING GIST ( geom GIST_GEOMETRY_OPS )
The primary key will facilitate communication of geometries
and attributes to other applications that access data in
PostGIS, while the spatial index will greatly increase the speed
of spatial queries made against the table.
18
Open Source Geographic Information Technologies -
PostgreSQL/PostGIS - PostGIS Installation and Manual Use
Spatial Queries in PostGIS
PostGIS supports a large number of spatial queries within the
database. These include:

Overlap

Intersection

Equivalence

Proximity

Distance and Area

Containment

Buffer
19
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
PostGIS Spatial Queries - Examples
Intersection/Overlaps (both yield the same result)
>
SELECT id,name FROM gtest_poly WHERE Intersects
(geom,GeometryFromText('POLYGON((1 1,1 3,4 3,4
1,1 1))',-1))
20
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
PostGIS Spatial Queries - Examples
Within
>
SELECT id,name FROM gtest_poly WHERE Within(geom,
(SELECT geom FROM gtest_poly WHERE name =
'house'))
21
Open Source Geographic Information Technologies - PostgreSQL/PostGIS - PostGIS Installation and Manual Use
PostGIS Spatial Queries - Examples
Buffer
>
SELECT AsText(buffer((SELECT geom FROM
fips55_geom WHERE feat_name = 'Albuquerque'),1))
as oneDegreeABQBuffer
22
POLYGON((-105.651 35.084,-105.670214719596 34.8889096779894,-105.727120467484
34.7013165676453,-105.819530387688 34.5284297669944,-105.943893218798
34.3768932188293,-106.095429766957 34.252530387713,-106.268316567604
34.1601204675016,-106.455909677945 34.1032147196044,-106.650999999955
34.084,-106.846090321967 34.1032147195869,-107.033683432313
34.1601204674672,-107.206570232968 34.2525303876632,-107.358106781139
34.3768932187658,-107.482469612262 34.5284297669197,-107.574879532481
34.7013165675623,-107.631785280387 34.8889096779013,-107.651
35.0839999999102,-107.631785280422 35.2790903219226,-107.57487953255
35.4666834322718,-107.482469612362 35.6395702329309,-107.358106781266
35.7911067811072,-107.206570233118 35.9154696122371,-107.033683432479
36.007879532464,-106.846090322143 36.0647852803781,-106.651000000135
36.084,-106.455909678121 36.0647852804306,-106.26831656777
36.0078795325671,-106.095429767106 35.9154696123867,-105.943893218925
35.7911067812977,-105.819530387788 35.6395702331549,-105.727120467553
35.4666834325206,-105.670214719631 35.2790903221868,-105.651
35.0840000001796,-105.651 35.084))