Spatial Databases by Open Standards and Software 5.

(1)

Spatial Databases by Open Standards and Software 5.

Basics of PostGIS

Gábor Nagy

(2)

Spatial Databases by Open Standards and Software 5.: Basics of PostGIS

Gábor Nagy Lector: Zoltán Siki

This module was created within TÁMOP - 4.1.2-08/1/A-2009-0027 "Tananyagfejlesztéssel a GEO-ért"

("Educational material development for GEO") project. The project was funded by the European Union and the Hungarian Government to the amount of HUF 44,706,488.

v 1.0

Publication date 2010

PostGIS is a geospatial addition for the PostgreSQL, which is based in OGC 06-103r4 and OGC 04-104r4.

The right to this intellectual property is protected by the 1999/LXXVI copyright law. Any unauthorized use of this material is prohibited. No part of this product may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system without express written permission from the author/publisher.

(3)

List of Tables

1. Operators of PostGIS ... 16

(5)

Chapter 5. Basics of PostGIS

1. 5.1 Introduction

The PostGIS is an additional package to PostgreSQL, which provides geographic objects and functions based on OGC 06-103r4 and OGC 06-104r4. The PostGIS (like PostgreSQL) is an open source software, developed by the Refractions Research.

PostGIS uses some other open source softwares. PROJ.4 for the reprojection, and the GEOS (Geometry Engine, Open Source) for many geospatial functions.

PostGIS provides geometry and geography type and functions for these types. Most of these functions are based on OGC 06-103r4 and OGC 06-104r4 standards, but PostGIS has some other functions.

PostGIS 1.5.2 contains 10 types, 785 functions and 17 aggregate functions. The functions and aggregate functions are started with “ST_” prefix, but most of them may be used without this prefix. The descriptions of the functions use the name with prefix, where we can not use the function without “ST_” (for example ST_UNION).

Geography type is similar to geometry type. The objects of geography type are determined on a sphere or spheroid. It is different from a geometry type with spherical reference system, because it uses spherical topology instead of plane.

2. 5.2 Installing PostGIS

2.1. 5.2.1 Installing from compiled binaries

Under Linux operating system, the simplest solution is to use the package manager to install the PostGIS package of the distribution. For example use the apt-get install postgis command in a Debian based system. (The name of the required package may be different.)

When we would like to install PostgreSQL to a Windows operating system, we can use more different installer programs. Some installers contain the PostGIS package, and it is selectable during the installation process.

The PostgreSQL and PostGIS could be installed from a ZIP file too. Both of the downloaded files must be unpacked to a directory. The PostgreSQL programs are available from this directory.

2.2. 5.2.2 Installing from source

The PostgreSQL and the PostGIS are open source programs. The source code of these programs can be downloaded from the following home pages (http://www.postgresql.org and http://www.postgis.org).

Both of these programs have regular build system, and could be installed by ./configure, make and make install commands. This work needs some other software. We can install this software by the package manager (need the development packages, with -dev ended name) or build from source also.

2.3. 5.2.3 Installing to a database

The installation of PostGIS to the database server is not sufficient, because PostGIS contains stored procedures.

These stored procedures (and types) must be available in each database, where we should use the geospatial function of PostGIS.

All of the necessary SQL commands, which create the required database objects, are in the postgis.sql file.

This file is in the share/contrib/postgis-version directory, where version is the version of PostGIS.

This file can be run from the psql console. For example:

\i /usr/local/share/contrib/postgis-1.5/postgis.sql

(6)

We can use the -f option of the psql command from the shell:

psql -f /usr/local/share/contrib/postgis-1.5/postgis.sql gisdatabase gisuser After the execution of these commands, PostGIS is available in the database.

The spatial_ref_sys table was created by the postgis.sql, but the table is empty. We can fill the descriptions of the spatial reference systems to this table by the spatial_ref_sys.sql file, which locates in the directory of the postgis.sql.

3. 5.3 Creating spatial tables

PostGIS provides geometry and geography types. Geometry and geography columns can be created by the CREATE TABLE SQL command, together with the other columns of the table:

gisdb=# CREATE TABLE gistable (id serial PRIMARY KEY,

name char(20), geom geometry);

CREATE TABLE

This is not a perfect solution, because many applications need one record in the geometry_columns table for each geometry type columns of the tables of the database. Use the AddGeomemertyColumns function instead to create the geometry type columns of a table, after the table has been created. This function can be called by a SELECT command. For example:

gisdb=# CREATE TABLE gistable (id serial PRIMARY KEY,

name char(20));

CREATE TABLE

gisdb=# SELECT AddGeometryColumn('gistable', 'geom', 23700, 'POLYGON', 2);

addgeometrycolumn

--- public.gistable.geom SRID:23700 TYPE:POLYGON DIMS:2 (1 row)

The first parameter of the function is the name of the table, the second parameter is the name of the new geometry column. The third parameter is the SRID (the identification number of the reference system), the fourth prameter is the type of the geometry, fifth parameter is the dimension of the geometry.

AddGeomemertyColumn function inserts the necessary record into the geometry_columns table, and set constrains to check the geometry type, dimension and SRID (identification number of the spatial reference system).

The spatial index is very important for effective spatial queries. GIST type indexes can be built for the geometry type columns:

gisdb=# CREATE INDEX gistable_geom ON gistable USING GIST (geom);

CREATE INDEX

The GIST (Generalized Search Tree) index provides an R-tree like data structure for fast queries on the basis of the bounding rectangles. The creation of this index is recommended for each geometry column, because it is very useful in many geospatial operations.

The DropGeometryColumn function can be used to remove a geometry column from a table. For example:

SELECT DropGeometryColumn('gistable','geom');

4. 5.4 Input and output functions

These functions provide the creation of geometry objects from text and binary data, and dump the geometry to various text and binary formats.

4.1. 5.4.1 GeomFromText

(7)

Basics of PostGIS

The GeomFromText or GeometryFromText function returns a geometry value.

GeomFromText('POLYGON((4 4,4 7,7 7,7 4,4 4))', 23700)

The first parameter is the WKT representation of the geometry, the second parameter is the identification number of the spatial reference system (SRID).

The ST_GeogFromText is similar to the GeomFromText, but creates geography values. This function needs only one parameter, the WKT representation of the geography; the SRID will be 4326 (WGS84).

4.2. 5.4.2 GeomFromEWKT

The GeomFromEWKT is similar to the GeomFromText function, but uses EWKT (Extended WKT) representation.

GeomFromEWKT('SRID=23700;POLYGON((4 4,4 7,7 7,7 4,4 4))')

This function has only one parameter: The EWKT representation text of the geometry, which contains the SRID too.

4.3. 5.4.3 AsText

AsText function returns the WKT representation of the geometry. The AsEWKT is very similar, but the result contains the SRID of the geometry too. Both of these functions have one parameter: one geometry value.

4.4. 5.4.4 Binary input and output

PostGIS has functions for the binary input and output. These functions are very similar to the text functions, but use WKB or EWKB format for the input (GeomFromWKB, GeomFromEWBK) or the output (AsBinary, AsEWKB).

4.5. 5.4.5 GML and KML input and output

PostGIS can handle GML and KML data. The AsGML and the AsKML functions return the representation of the geometry in these XML based formats. The result is a text value. This text value contains the XML description of the geometry in the required format:

gisdb=# SELECT AsGML(GeomFromText('POLYGON((4 4,4 7,7 7,7 4,4 4))', 4326));

asgml

---

<gml:Polygon srsName="EPSG:4326"><gml:outerBoundaryIs>

<gml:LinearRing>

<gml:coordinates>4,4 4,7 7,7 7,4 4,4</gml:coordinates>

</gml:LinearRing>

</gml:outerBoundaryIs></gml:Polygon>

(1 row)

gisdb=# SELECT AsKML(GeomFromText('POLYGON((4 4,4 7,7 7,7 4,4 4))', 4326));

askml

---

</LinearRing></outerBoundaryIs></Polygon>

(1 row)

The GeomFromGML and GeomFromKML functions provide the data input from these formats.

5. 5.5 Calculating numerical attributes of geometry

5.1. 5.5.1 Area

Area function returns the area of a Polygon or MultiPolygon geometry or the total area of the polygon elements of a GeometryCollection. If the parameter of the function has a different geometry type the result will be zero.

gisdb=# SELECT ST_Area('POLYGON((1 1,1 4,4 4,4 1,1 1))');

st_area

(8)

--- 9 (1 row)

gisdb=# SELECT ST_Area('POLYGON((1 1,1 4,4 4,4 1,1 1), (2 2,2 3,3 3,3 2,2 2))');

st_area --- 8 (1 row)

gisdb=# SELECT ST_Area('LINESTRING(4 5,7 9,11 2)');

gisdb=# SELECT ST_Area('GEOMETRYCOLLECTION(

POLYGON((1 1,1 4,4 4,4 1,1 1)),POINT(7 8))');

The parameter of the ST_Area function (as in the examples above) may be a text value with the WKT representation of the geometry too. This solution will be applied in some of the following examples of other functions.

5.2. 5.5.2 Perimeter

Perimeter function returns the perimeter (length of the boundary) of a Polygon or MultiPolygon geometry or the the total perimeter of the polygon elements of a GeometryCollection. If the parameter of the function has a different geometry type the result will be zero.

gisdb=# SELECT Perimeter('POLYGON((1 1,1 4,4 4,4 1,1 1))');

perimeter --- 12 (1 row)

gisdb=# SELECT Perimeter('POLYGON((1 1,1 4,4 4,4 1,1 1), (2 2,2 3,3 3,3 2,2 2))');

gisdb=# SELECT Perimeter('LINESTRING(4 5,7 9,11 2)');

gisdb=# SELECT Perimeter('GEOMETRYCOLLECTION(

POLYGON((1 1,1 4,4 4,4 1,1 1)),POINT(7 8))');

The Perimeter3D function works similarly to the Perimeter function, but calculates the 3D perimeter, if the geometry has Z coordinates. The Perimeter2D function always returns the 2D perimeter.

gisdb=# SELECT Perimeter2D('POLYGON((1 1 0,1 4 0,4 4 4, 4 1 0,1 1 0))');

perimeter2d --- 12 (1 row)

(9)

Basics of PostGIS

gisdb=# SELECT Perimeter3D('POLYGON((1 1 0,1 4 0,4 4 4, 4 1 0,1 1 0))');

perimeter3d --- 16 (1 row)

5.3. 5.5.3 Length

Length function returns the length of a LineString or MultiLineString geometry or the the total length of the linestring elements of a GeometryCollection. If the parameter of the function has a different geometry type the result will be zero.

gisdb=# SELECT ST_Length('LINESTRING(1 2,5 2,9 5)');

st_length --- 9 (1 row)

gisdb=# SELECT ST_Length('MULTILINESTRING((1 2,5 2,9 5), (1 4,3 4))');

gisdb=# SELECT ST_Length('POLYGON((1 1,1 4,4 4,4 1,1 1))');

gisdb=# SELECT ST_Length('GEOMETRYCOLLECTION(

LINESTRING(1 4,1 10),POINT(7 8))');

The “length” is also the name of the function, which returns the length of a text. PostgreSQL makes a distinction between the two length functions by the type of the parameter. We have to use GeomFromText function in the Length function, or use ST_Length function.

Length3D function works similarly to the Length function, but calculates the 3D perimeter, if the geometry has Z coordinates.

gisdb=# SELECT ST_Length('LINESTRING(1 3 2,5 15 5)');

st_length --- 12.6491106406735 (1 row)

gisdb=# SELECT ST_Length2D('LINESTRING(1 3 2,5 15 5)');

st_length2d --- 12.6491106406735 (1 row)

gisdb=# SELECT ST_Length3D('LINESTRING(1 3 2,5 15 5)');

st_length3d --- 13 (1 row)

Length_Spheroid (and Lebgth3D_Spheroid) function can calculate the length of the geometry on a spheroid (sphere or ellipsoid).

gisdb=# SELECT ST_Length_Spheroid('LINESTRING(18.41281 47.18458, 18.41883 47.18878,18.42706 47.19097)',

(10)

'SPHEROID["WGS84",6378137,298.257223563]');

st_length_spheroid --- 1322.39548875418 (1 row)

6. 5.6 Calculating geometry values

These functions return a new geometry, which are built from the original geometries and sometimes other parameters.

6.1. 5.6.1 Buffer

Buffer function returns a Polygon or MultiPolygon geometry that represents the set of the points whose distance from a geometry specified by the first parameter are less than or equal to the distance specified by the second parameter.

gisdb=# SELECT AsText(Buffer('POINT(3 4)',1));

astext

---

POLYGON((4 4,3.981 3.805,3.924 3.617,3.831 3.444,3.707 3.293, 3.556 3.169,3.383 3.076,3.195 3.019,3 3,

2.805 3.019,2.617 3.076,2.444 3.169,2.293 3.293, 2.169 3.444,2.076 3.617,2.019 3.805,2 4,

2.019 4.195,2.076 4.383,2.169 4.556,2.293 4.707, 2.444 4.831,2.617 4.924,2.805 4.981,3 5,

3.195 4.981,3.383 4.924,3.556 4.831,3.707 4.707, 3.831 4.556,3.924 4.383,3.981 4.195,4 4))

(1 row)

By definition, the boundary of the shape of the buffer contains arc sections, but the Polygon and MultiPolygon geometries are limited to have linear rings. The arc sections will be replaced by more linear sections. By default, a quarter of a circle will be replaced by 8 line segments. The optionally third parameter of the function specifies the number of line segments or other parameters of the buffer generation.

gisdb=# SELECT AsText(ST_Buffer('POINT(3 4)',1,'quad_segs=2'));

astext

---

POLYGON((4 4,3.707 3.293,3 3,2.293 3.293,2 4, 2.293 4.707,3 5,3.707 4.707,4 4))

(1 row)

6.2. 5.6.2 ConvexHull

ConvexHull function returns a geometry that represents the least convex point set, which contains all the points of a geometry, which were specified by the parameter of the function.

gisdb=# SELECT AsText(ConvexHull('POINT(3 5)'));

astext --- POINT(3 5) (1 row)

gisdb=# SELECT AsText(ConvexHull('LINESTRING(2 3,5 8)'));

astext

--- LINESTRING(2 3,5 8) (1 row)

gisdb=# SELECT AsText(ConvexHull('MULTIPOINT(2 2,4 5)'));

astext

gisdb=# SELECT AsText(ConvexHull('MULTIPOINT(2 2,4 5,3 4)'));

astext

(11)

Basics of PostGIS

--- POLYGON((2 2,3 4,4 5,2 2)) (1 row)

gisdb=# SELECT AsText(ConvexHull('POLYGON((1 1,1 4,4 4,4 1,1 1), (2 2,2 3,3 3,3 2,2 2))'));

astext

--- POLYGON((1 1,1 4,4 4,4 1,1 1)) (1 row)

gisdb=# SELECT AsText(ConvexHull('POLYGON((1 5,6 9,4 6,8 2,1 5))'));

astext

--- POLYGON((8 2,1 5,6 9,8 2)) (1 row)

6.3. 5.6.3 Centroid

Centroid function returns the geometric centre of the geometry as a Point geometry.

gisdb=# SELECT AsText(Centroid('POLYGON((1 1,1 4,4 4,4 1,1 1))'));

astext --- POINT(2.5 2.5) (1 row)

gisdb=# SELECT AsText(Centroid('POLYGON((1 1,1 4,4 4,4 1,1 1), (2 2,2 3,3 3,3 2,2 2))'));

astext --- POINT(2.5 2.5) (1 row)

6.4. 5.6.4 Simplify

Simplify function returns a simplified variant of the geometry, which is the first parameter of the function.

This function uses the Douglas-Peuker algorithm, with a tolerance, which is the second parameter.

gisdb=# SELECT AsText(Simplify('LINESTRING(2 4,4 7,7 9)', 1));

astext

gisdb=# SELECT AsText(Simplify('LINESTRING(2 4,4 7,7 9)', 0.5));

astext

--- LINESTRING(2 4,4 7,7 9) (1 row)

ST_SiplifyPreserveTopology function is the same as the Simplify, but preserves the topology relations of parts of the object (linear rings, linestring segments).

SELECT AsText(Simplify(ST_GeomFromText(

'MULTILINESTRING((1 5,5 7,9 5),(1 1,5 6,9 1))'),2));

astext

--- MULTILINESTRING((1 5,9 5),(1 1,5 6,9 1)) (1 row)

SELECT AsText(ST_SimplifyPreserveTopology(ST_GeomFromText(

'MULTILINESTRING((1 5,5 7,9 5),(1 1,5 6,9 1))'),2));

astext

--- MULTILINESTRING((1 5,5 7,9 5),(1 1,5 6,9 1)) (1 row)

6.5. 5.6.5 Segmentize

(12)

Segmentize function returns a modified variant of the geometry (the first parameter), which has no longer segments than a given distance (the second parameter).

gisdb=# SELECT AsText(Segmentize('LINESTRING(1 3,1 6)',3));

astext

--- LINESTRING(1 3,1 6,1 9) (1 row)

astext

--- LINESTRING(1 3,1 5,1 7,1 9) (1 row)

gisdb=# SELECT AsText(Segmentize(

'POLYGON((3 2,3 8,5 8,5 2,3 2))',3));

astext

--- POLYGON((3 2,3 5,3 8,5 8,5 5,5 2,3 2)) (1 row)

7. 5.7 Relation of two geometries

7.1. 5.7.1 Distance

Distance function returns the shortest distance between two geometries, which are the parameters of the function. The result is zero, if the geometries have a common point.

gisdb=# SELECT Distance('POINT(1 2)','POINT(4 6)');

distance --- 5 (1 row)

gisdb=# SELECT Distance('LINESTRING(2 2,8 2)','POINT(4 6)');

gisdb=# SELECT Distance('POLYGON((2 2,2 3,8 3,8 2,2 2))', 'POINT(4 6)');

Distance function can be used between geography objects too.

gisdb=# SELECT ST_Distance(

ST_GeogFromText('SRID=4326;POINT(18.41883 47.18878)'), ST_GeogFromText('SRID=4326;POINT(18.41281 47.18458)'));

st_distance --- 652.839475007109 (1 row)

ST_ShortestLine function returns the shortest line segment between two geometries as a LineString geometry.

The length of this LineString equals to the result of the Distance function.

gisdb=# SELECT AsText(ST_ShortestLine('POINT(1 2)','POINT(4 6)'));

astext

(13)

Basics of PostGIS

gisdb=# SELECT AsText(ST_ShortestLine('LINESTRING(2 2,8 2)', 'POINT(4 6)'));

astext

gisdb=# SELECT AsText(ST_ShortestLine(

'POLYGON((2 2,2 3,8 3,8 2,2 2))','POINT(4 6)'));

astext

7.2. 5.7.2 Azimuth

Azimuth function returns the azimuth in radians from the point specified in the first parameter to the point specified in the second parameter. If any parameter has no Point geometry, the function returns NULL.

gisdb=# SELECT Azimuth('POINT(1 1)','POINT(1 2)');

azimuth --- 0 (1 row)

gisdb=# SELECT Azimuth('POINT(1 1)','POINT(2 2)');

azimuth --- 0.785398163397448 (1 row)

7.3. 5.7.3 Equals

Equals function returns true value, if the point set of the geometries (specified by the parameters of the function) are spatially equal.

gisdb=# SELECT Equals('POINT(2 4)','POINT(3 5)');

equals --- f (1 row)

gisdb=# SELECT Equals('POINT(2 4)','POINT(2 4)');

equals --- t (1 row)

gisdb=# SELECT Equals('MULTIPOINT(2 4,5 8)','MULTIPOINT(5 8,2 4)');

gisdb=# SELECT Equals('LINESTRING(2 4,5 8)','LINESTRING(5 8,2 4)');

gisdb=# SELECT Equals('POLYGON((1 1,1 5,5 1,1 1))', 'POLYGON((1 5,1 1,5 1,1 5))');

(14)

gisdb=# SELECT Equals('POLYGON((2 2,2 4,4 4,4 2,2 2))', 'POLYGON((2 2,2 3,2 4,4 4,4 2,2 2))');

7.4. 5.7.4 Contains

Contains function returns true value, if the geometry specified by the first parameter contains all the points of the geometry specified by the second parameter.

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POINT(3 3)');

contains --- t

(1 row)

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POINT(8 4)');

contains --- f

(1 row)

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(2 2,3 4)');

contains --- t

(1 row)

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(2 4,6 4)');

contains --- f

(1 row)

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((2 2,2 3,3 3,3 2,2 2))');

contains --- t

(1 row)

gisdb=# SELECT Contains('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((4 4,4 7,7 7,7 4,4 4))');

contains --- f

(1 row)

gisdb=# SELECT Contains('LINESTRING(2 3,4 3)', 'POINT(3 3)');

contains --- t

(1 row)

gisdb=# SELECT Contains('LINESTRING(2 3,7 3)', 'LINESTRING(3 3,4 3)');

contains --- t

(1 row)

7.5. 5.7.5 Overlaps

(15)

Basics of PostGIS

Overlaps function returns true value, if the common set of the points of the two geometries has the same dimension as the objects. This function returns true value, if the two geometries have common points, but both of the geometries have points, which are not contained by the other object. This function does not work with GeometryCollection objects.

gisdb=# SELECT Overlaps('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((2 2,2 3,3 3,3 2,2 2))');

overlaps --- f

(1 row)

gisdb=# SELECT Overlaps('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((4 4,4 7,7 7,7 4,4 4))');

overlaps --- t

(1 row)

gisdb=# SELECT Overlaps('LINESTRING(2 3,7 3)', 'LINESTRING(3 3,4 3)');

overlaps --- f

(1 row)

gisdb=# SELECT Overlaps('LINESTRING(2 3,7 3)', 'LINESTRING(1 3,4 3)');

overlaps --- t

(1 row)

7.6. 5.7.6 Intersects

Intersects function returns true value, if the two geometries have at least one common point.

gisdb=# SELECT Intersects('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((4 4,4 7,7 7,7 4,4 4))');

intersects --- t

(1 row)

gisdb=# SELECT Intersects('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(2 4,6 4)'); intersects

--- t

(1 row)

gisdb=# SELECT Intersects('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(6 4,7 4)');

intersects --- f

(1 row)

7.7. 5.7.7 Touches

Touches function returns true value, if the two geometries have common points, but these points are located only on the boundaries of the objects.

gisdb=# SELECT Touches('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((5 2,5 4,7 4,7 2,5 2))');

touches --- t

(1 row)

(16)

gisdb=# SELECT Touches('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((5 2,4 4,7 4,7 2,5 2))');

touches --- f

(1 row)

7.8. 5.7.8 Relate

Relate function returns a text, which contains the element of the DE-9IM.

gisdb=# SELECT Relate('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((5 2,5 4,7 4,7 2,5 2))');

relate --- FF2F11212 (1 row)

If the Relate function has three parameters, the third parameter is a DE-9IM pattern. The function returns true, if the pattern matches the relation of the two geometries, given in the first and second parameters.

gisdb=# SELECT Relate('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((5 2,5 4,7 4,7 2,5 2))','****1****');

relate --- t (1 row)

8. 5.8 Calculate derived geometry

These functions return a modified geometry preserving the topology of the original object.

8.1. 5.8.1 Transform

PostGIS can transform geometries between spatial reference systems. The geometries contain the identification number of spatial reference system, further on SRID. The spatial_ref_sys table contains the descriptions of spatial reference systems identified by the SRID.

Transform function returns a transformed geometry from the original reference system (specified as the first parameter, SRID is involved in the geometry) to a new reference system, which SRID is specified by the second parameter.

gisdb=# SELECT AsText(Transform(

'SRID=4326;POINT(18.4183 47.1888)',23700));

astext

--- POINT(602233.50006042 205129.440451882) (1 row)

4326 is the SRID of WGS84, 23700 is the SRID of Hungarian Projection System (EOV).

The SRID of an object can be modified without transformation by the SetSRID function.

gisdb=# SELECT AsText(ST_SetSRID

('SRID=4326;POINT(18.4183 47.1888)',23700));

astext

--- POINT(18.4183 47.1888) (1 row)

8.2. 5.8.2 Affine

Affine function returns an affine transformed geometry from the input geometry specified as the first parameter. The other 6 or 12 parameters of the function contain the parameters of a 2D or 3D transformation.

(17)

Basics of PostGIS

8.3. 5.8.3 SnapToGrid

SnapToGrid function returns modified geometry snapping points to a grid. The resolution of the grid is specified by the second parameter. The coordinates of a geometry can be rounded up by this function.

gisdb=# SELECT AsText(SnapToGrid(

'POINT(602233.50006042 205129.440451882)',0.01));

astext

--- POINT(602233.5 205129.44) (1 row)

9. 5.9 Set functions

These functions return a geometry which represents a point set which is the result of a set operation between two geometries.

9.1. 5.9.1 ST_Union

ST_Union function returns a geometry that represents the union of the two geometries, which are specified as the parameters of the function.

gisdb=# SELECT AsText(ST_Union('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((3 3,3 7,7 7,7 3,3 3))'));

astext

--- POLYGON((1 1,1 5,3 5,3 7,7 7,7 3,5 3,5 1,1 1)) (1 row)

Figure 1. Union of two POLYGON geometries gisdb=# SELECT AsText(ST_Union('LINESTRING(2 3,5 3)',

'LINESTRING(4 3,7 3)')); astext --- MULTILINESTRING((2 3,4 3),(4 3,5 3),(5 3,7 3)) (1 row)

gisdb=# SELECT AsText(ST_Union('POINT(2 4)','POINT(7 3)'));

astext

--- MULTIPOINT(2 4,7 3) (1 row)

9.2. 5.9.2 Intersection

Intersection function returns a geometry that represents the common parts of the two geometries, which are specified as the parameters of the function.

gisdb=# SELECT AsText(Intersection('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((3 3,3 7,7 7,73,3 3))'));

astext

--- POLYGON((3 5,5 5,5 3,3 3,3 5))

(18)

(1 row)

Figure 2. Intersection of two POLYGON geometries gisdb=# SELECT AsText(Intersection('LINESTRING(2 3,5 3)',

'LINESTRING(4 3,7 3)')); astext ---

LINESTRING(4 3,5 3) (1 row)

gisdb=# SELECT AsText(Intersection('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(3 4,8 9)'));

astext

gisdb=# SELECT AsText(Intersection('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'MULTIPOINT(2 4,6 2)'));

astext --- POINT(2 4) (1 row)

9.3. 5.9.3 Difference

Difference function returns a geometry that represents that part of the first geometry, which is not in the second geometry.

gisdb=# SELECT AsText(Difference('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((3 3,3 7,7 7,7 3,3 3))'));

astext

--- POLYGON((1 1,1 5,3 5,3 3,5 3,5 1,1 1)) (1 row)

Figure 3. Difference of two POLYGON geometries gisdb=# SELECT AsText(Difference('LINESTRING(2 3,5 3)',

'LINESTRING(4 3,7 3)')); astext ---

LINESTRING(2 3,4 3) (1 row)

(19)

Basics of PostGIS

gisdb=# SELECT AsText(Difference('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'LINESTRING(3 4,8 9)')); astext

--- POLYGON((1 1,1 5,4 5,5 5,5 1,1 1)) (1 row)

gisdb=# SELECT AsText(Difference('POLYGON((1 1,1 5,5 5,5 1,1 1))', 'MULTIPOINT(2 4,6 2)')); astext

--- POLYGON((1 1,1 5,5 5,5 1,1 1)) (1 row)

9.4. 5.9.4 SymDifference

SymDifference function returns a geometry that represents that part of the two input geometry, which belongs to only one of them.

gisdb=# SELECT AsText(SymDifference(

'POLYGON((1 1,1 5,5 5,5 1,1 1))', 'POLYGON((3 3,3 7,7 7,7 3,3 3))'));

astext

---

MULTIPOLYGON(((1 1,1 5,3 5,3 3,5 3,5 1,1 1)), ((5 3,5 5,3 5,3 7,7 7,7 3,5 3)))

(1 row)

Figure 4. Symmetric difference of two POLYGON geometries gisdb=# SELECT AsText(SymDifference('LINESTRING(2 3,5 3)',

'LINESTRING(4 3,7 3)'));

astext

--- MULTILINESTRING((2 3,4 3),(5 3,7 3)) (1 row)

'POLYGON((1 1,1 5,5 5,5 1,1 1))','LINESTRING(3 4,8 9)'));

astext

--- GEOMETRYCOLLECTION(LINESTRING(4 5,8 9), POLYGON((1 1,1 5,4 5,5 5,5 1,1 1))) (1 row)

'POLYGON((1 1,1 5,5 5,5 1,1 1))','MULTIPOINT(2 4,6 2)'));

astext

--- GEOMETRYCOLLECTION(POINT(6 2),

POLYGON((1 1,1 5,5 5,5 1,1 1))) (1 row)

10. 5.10 Aggregate functions

(20)

PostGIS’ aggregate functions create geometry from more geometries. These are used, where the usual SQL aggregate functions, for example in queries containing GROUP BY statement.

10.1. 5.10.1 ST_Union

ST_Union aggregate returns a geometry that represents the union of all input geometries.

gisdb=# SELECT id, AsText(geom) FROM polygons; id | astext ----+---

1 | POLYGON((1 1,1 5,5 5,5 1,1 1)) 2 | POLYGON((3 3,3 7,7 7,7 3,3 3)) 3 | POLYGON((8 8,8 9,9 9,9 8,8 8)) (3 rows)

gisdb=# SELECT AsText(ST_Union(geom)) FROM polygons;

astext

--- MULTIPOLYGON(((8 8,8 9,9 9,9 8,8 8)),

((1 1,1 5,3 5,3 7,7 7,7 3,5 3,5 1,1 1))) (1 row)

10.2. 5.10.2 Collect

Collect aggregate returns a Multi* geometry or a GeometryCollection, which contains the elements of the original geometries. In contrast to the ST_Union, this aggregate does not do any spatial operation between the geometries.

gisdb=# SELECT AsText(Collect(geom)) FROM polygons;

astext

--- MULTIPOLYGON(((1 1,1 5,5 5,5 1,1 1)),((3 3,3 7,7 7,7 3,3 3)),

((8 8,8 9,9 9,9 8,8 8))) (1 row)

10.3. 5.10.3 Poligonize

Poligonize aggregate returns a GeometryCollection that contains the possible Polygons, which can be created from the input LineString and MultiLineString objects.

gisdb=# SELECT id, AsText(geom) from linework;

id | astext

gisdb=# SELECT AsText(Polygonize(geom)) from linework;

astext

--- GEOMETRYCOLLECTION(

POLYGON((1 1,1 2,2 2,2 1,1 1)), POLYGON((3 2,2 2,2 3,3 3,3 2))) (1 row)

11. 5.11 Operators

PostGIS has some operators that return true or false values depending on the spatial relationship of the bounding boxes of the operands. These operators use spatial indexes of any operands.

Table 1. Operators of PostGIS

(21)

Basics of PostGIS

A && B returns TRUE if A's bounding box overlaps B's

A &< B returns TRUE if A's bounding box overlaps or is to the left of B's A &<| B returns TRUE if A's bounding box overlaps or is below B's A &> B returns TRUE if A' bounding box overlaps or is to the right of B's A << B returns TRUE if A's bounding box is strictly to the left of B's A <<| B returns TRUE if A's bounding box is strictly below B's A = B returns TRUE if A's bounding box is the same as B's

A >> B returns TRUE if A's bounding box is strictly to the right of B's A @ B returns TRUE if A's bounding box is contained by B's A |&> B returns TRUE if A's bounding box overlaps or is above B's A |>> B returns TRUE if A's bounding box is strictly above B's A ~ B returns TRUE if A's bounding box contains B's A ~= B returns TRUE if A's bounding box is the same as B's These operators are very useful for fast pre-screening in spatial queries.

Bibliography

PostgreSQL Global Development Group: 1996-2010.

Refractions Research Inc.: PostGIS 1.5.2 manual, 2010.

Open Geospatial Consortium: OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 1: Common architecture, OGC 06-103r4, 2010.

Open Geospatial Consortium: OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option, OGC 06-104r4, 2010.

Spatial Databases by Open Standards and Software 5.

Spatial Databases by Open Standards and Software 5.

Basics of PostGIS

Gábor Nagy

Spatial Databases by Open Standards and Software 5.: Basics of PostGIS

Table of Contents

List of Tables

Chapter 5. Basics of PostGIS

1. 5.1 Introduction

2. 5.2 Installing PostGIS

2.1. 5.2.1 Installing from compiled binaries

2.2. 5.2.2 Installing from source

2.3. 5.2.3 Installing to a database

3. 5.3 Creating spatial tables

4. 5.4 Input and output functions

4.1. 5.4.1 GeomFromText

4.2. 5.4.2 GeomFromEWKT

4.3. 5.4.3 AsText

4.4. 5.4.4 Binary input and output

4.5. 5.4.5 GML and KML input and output

5. 5.5 Calculating numerical attributes of geometry

5.1. 5.5.1 Area

5.2. 5.5.2 Perimeter

5.3. 5.5.3 Length

6. 5.6 Calculating geometry values

6.1. 5.6.1 Buffer

6.2. 5.6.2 ConvexHull

6.3. 5.6.3 Centroid

6.4. 5.6.4 Simplify

6.5. 5.6.5 Segmentize

7. 5.7 Relation of two geometries

7.1. 5.7.1 Distance

7.2. 5.7.2 Azimuth

7.3. 5.7.3 Equals

7.4. 5.7.4 Contains

7.5. 5.7.5 Overlaps

7.6. 5.7.6 Intersects

7.7. 5.7.7 Touches

7.8. 5.7.8 Relate

8. 5.8 Calculate derived geometry

8.1. 5.8.1 Transform

8.2. 5.8.2 Affine

8.3. 5.8.3 SnapToGrid

9. 5.9 Set functions

9.1. 5.9.1 ST_Union

9.2. 5.9.2 Intersection

9.3. 5.9.3 Difference

9.4. 5.9.4 SymDifference

10. 5.10 Aggregate functions

10.1. 5.10.1 ST_Union

10.2. 5.10.2 Collect

10.3. 5.10.3 Poligonize

11. 5.11 Operators

Table 1. Operators of PostGIS

Bibliography