[DebianGIS-dev] r896 - packages/postgis/trunk/doc/html
frankie at alioth.debian.org
frankie at alioth.debian.org
Sat Jun 16 10:59:52 UTC 2007
Author: frankie
Date: 2007-06-16 10:59:52 +0000 (Sat, 16 Jun 2007)
New Revision: 896
Modified:
packages/postgis/trunk/doc/html/postgis.html
Log:
Uhm, changed doc file
Modified: packages/postgis/trunk/doc/html/postgis.html
===================================================================
--- packages/postgis/trunk/doc/html/postgis.html 2007-06-15 23:02:47 UTC (rev 895)
+++ packages/postgis/trunk/doc/html/postgis.html 2007-06-16 10:59:52 UTC (rev 896)
@@ -1,20 +1,16 @@
-<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>PostGIS Manual</title><link rel="stylesheet" href="style.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.65.1"><meta name="description" content="PostGIS is an extension to the PostgreSQL object-relational
+<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>PostGIS Manual</title><link rel="stylesheet" href="style.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.72.0"><meta name="description" content="PostGIS is an extension to the PostgreSQL object-relational database system which allows GIS (Geographic Information Systems) objects to be stored in the database. PostGIS includes support for GiST-based R-Tree spatial indexes, and functions for analysis and processing of GIS objects. This is the manual for version 1.2.1"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="book" lang="en"><div class="titlepage"><div><div><h1 class="title"><a name="id2486894"></a>PostGIS Manual</h1></div><div><div class="abstract"><p class="title"><b>Abstract</b></p><p>PostGIS is an extension to the PostgreSQL object-relational
database system which allows GIS (Geographic Information Systems)
objects to be stored in the database. PostGIS includes support for
GiST-based R-Tree spatial indexes, and functions for analysis and
- processing of GIS objects. This is the manual for version 1.2.1"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="book" lang="en"><div class="titlepage"><div><div><h1 class="title"><a name="id2695011"></a>PostGIS Manual</h1></div><div><div class="abstract"><p class="title"><b>Abstract</b></p><p>PostGIS is an extension to the PostgreSQL object-relational
- database system which allows GIS (Geographic Information Systems)
- objects to be stored in the database. PostGIS includes support for
- GiST-based R-Tree spatial indexes, and functions for analysis and
- processing of GIS objects.</p><p>This is the manual for version 1.2.1</p></div></div></div><div></div><hr></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="chapter"><a href="#id2695940">1. Introduction</a></span></dt><dd><dl><dt><span class="sect1"><a href="#credits">1.1. Credits</a></span></dt><dt><span class="sect1"><a href="#id2695778">1.2. More Information</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2723071">2. Installation</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2723077">2.1. Requirements</a></span></dt><dt><span class="sect1"><a href="#PGInstall">2.2. PostGIS</a></span></dt><dd><dl><dt><span class="sect2"><a href="#templatepostgis">2.2.1. Creating PostGIS spatially-enabled databases from an in-built
- template</a></span></dt><dt><span class="sect2"><a href="#upgrading">2.2.2. Upgrading</a></span></dt><dt><span class="sect2"><a href="#id2763269">2.2.3. Common Problems</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2763415">2.3. JDBC</a></span></dt><dt><span class="sect1"><a href="#id2763491">2.4. Loader/Dumper</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2763531">3. Frequently Asked Questions</a></span></dt><dt><span class="chapter"><a href="#id2764010">4. Using PostGIS</a></span></dt><dd><dl><dt><span class="sect1"><a href="#RefObject">4.1. GIS Objects</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2764042">4.1.1. OpenGIS WKB and WKT</a></span></dt><dt><span class="sect2"><a href="#id2764151">4.1.2. PostGIS EWKB, EWKT and Canonical Forms</a></span></dt><dt><span class="sect2"><a href="#id2764316">4.1.3. SQL-MM Part 3</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2764400">4.2. Using OpenGIS Standards</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2764441">4.2.1. The SPATIAL_REF_SYS Table</a></span></dt><dt><span class="sect2"><a href="#id2764652">4.2.2. The GEOMETRY_COLUMNS Table</a></span></dt><dt><span class="sect2"><a href="#id2764797">4.2.3. Creating a Spatial Table</a></span></dt><dt><span class="sect2"><a href="#id2764911">4.2.4. Ensuring OpenGIS compliancy of geometries</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765010">4.3. Loading GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765024">4.3.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2765088">4.3.2. Using the Loader</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765328">4.4. Retrieving GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765341">4.4.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2765514">4.4.2. Using the Dumper</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765691">4.5. Building Indexes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765757">4.5.1. GiST Indexes</a></span></dt><dt><span class="sect2"><a href="#id2765837">4.5.2. Using Indexes</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765955">4.6. Complex Queries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765973">4.6.1. Taking Advantage of Indexes</a></span></dt><dt><span class="sect2"><a href="#id2766061">4.6.2. Examples of Spatial SQL</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2766374">4.7. Using Mapserver</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2766416">4.7.1. Basic Usage</a></span></dt><dt><span class="sect2"><a href="#id2766674">4.7.2. Frequently Asked Questions</a></span></dt><dt><span class="sect2"><a href="#id2766856">4.7.3. Advanced Usage</a></span></dt><dt><span class="sect2"><a href="#id2767038">4.7.4. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2767148">4.8. Java Clients (JDBC)</a></span></dt><dt><span class="sect1"><a href="#id2767233">4.9. C Clients (libpq)</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2767242">4.9.1. Text Cursors</a></span></dt><dt><span class="sect2"><a href="#id2767252">4.9.2. Binary Cursors</a></span></dt></dl></dd></dl></dd><dt><span class="chapter"><a href="#id2767264">5. Performance tips</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2767270">5.1. Small tables of large geometries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2767276">5.1.1. Problem description</a></span></dt><dt><span class="sect2"><a href="#id2767350">5.1.2. Workarounds</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2767434">5.2. CLUSTERing on geometry indices</a></span></dt><dt><span class="sect1"><a href="#id2767496">5.3. Avoiding dimension conversion</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2767560">6. PostGIS Reference</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2767572">6.1. OpenGIS Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2767578">6.1.1. Management Functions</a></span></dt><dt><span class="sect2"><a href="#id2767667">6.1.2. Geometry Relationship Functions</a></span></dt><dt><span class="sect2"><a href="#id2768066">6.1.3. Geometry Processing Functions</a></span></dt><dt><span class="sect2"><a href="#id2768393">6.1.4. Geometry Accessors</a></span></dt><dt><span class="sect2"><a href="#id2768904">6.1.5. Geometry Constructors</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2769510">6.2. Postgis Extensions</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2769516">6.2.1. Management Functions</a></span></dt><dt><span class="sect2"><a href="#id2769855">6.2.2. Operators</a></span></dt><dt><span class="sect2"><a href="#id2770079">6.2.3. Measurement Functions</a></span></dt><dt><span class="sect2"><a href="#id2770368">6.2.4. Geometry Outputs</a></span></dt><dt><span class="sect2"><a href="#id2770508">6.2.5. Geometry Constructors</a></span></dt><dt><span class="sect2"><a href="#id2770859">6.2.6. Geometry Editors</a></span></dt><dt><span class="sect2"><a href="#id2771471">6.2.7. Linear Referencing</a></span></dt><dt><span class="sect2"><a href="#id2771698">6.2.8. Misc</a></span></dt><dt><span class="sect2"><a href="#id2772712">6.2.9. Long Transactions support</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2772297">6.3. SQL-MM Functions</a></span></dt><dt><span class="sect1"><a href="#id2773715">6.4. ArcSDE Functions</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2773833">7. Reporting Bugs</a></span></dt><dt><span class="appendix"><a href="#release_notes">A. Appendix</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2773913">A.1. Release Notes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2773918">A.1.1. Release 1.2.1</a></span></dt><dt><span class="sect2"><a href="#id2773964">A.1.2. Release 1.2.0</a></span></dt><dt><span class="sect2"><a href="#id2773998">A.1.3. Release 1.1.6</a></span></dt><dt><span class="sect2"><a href="#id2774117">A.1.4. Release 1.1.5</a></span></dt><dt><span class="sect2"><a href="#id2774231">A.1.5. Release 1.1.4</a></span></dt><dt><span class="sect2"><a href="#id2774358">A.1.6. Release 1.1.3</a></span></dt><dt><span class="sect2"><a href="#id2774525">A.1.7. Release 1.1.2</a></span></dt><dt><span class="sect2"><a href="#id2774658">A.1.8. Release 1.1.1</a></span></dt><dt><span class="sect2"><a href="#id2774789">A.1.9. Release 1.1.0</a></span></dt><dt><span class="sect2"><a href="#id2775120">A.1.10. Release 1.0.6</a></span></dt><dt><span class="sect2"><a href="#id2775228">A.1.11. Release 1.0.5</a></span></dt><dt><span class="sect2"><a href="#id2775363">A.1.12. Release 1.0.4</a></span></dt><dt><span class="sect2"><a href="#rel_1.0.3_upgrading">A.1.13. Release 1.0.3</a></span></dt><dt><span class="sect2"><a href="#id2775629">A.1.14. Release 1.0.2</a></span></dt><dt><span class="sect2"><a href="#id2775707">A.1.15. Release 1.0.1</a></span></dt><dt><span class="sect2"><a href="#id2775809">A.1.16. Release 1.0.0</a></span></dt><dt><span class="sect2"><a href="#id2775909">A.1.17. Release 1.0.0RC6</a></span></dt><dt><span class="sect2"><a href="#id2775982">A.1.18. Release 1.0.0RC5</a></span></dt><dt><span class="sect2"><a href="#id2776055">A.1.19. Release 1.0.0RC4</a></span></dt><dt><span class="sect2"><a href="#id2776181">A.1.20. Release 1.0.0RC3</a></span></dt><dt><span class="sect2"><a href="#id2776354">A.1.21. Release 1.0.0RC2</a></span></dt><dt><span class="sect2"><a href="#id2776453">A.1.22. Release 1.0.0RC1</a></span></dt></dl></dd></dl></dd></dl></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2695940"></a>Chapter 1. Introduction</h2></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#credits">1.1. Credits</a></span></dt><dt><span class="sect1"><a href="#id2695778">1.2. More Information</a></span></dt></dl></div><p>PostGIS is developed by Refractions Research Inc, as a spatial
+ processing of GIS objects.</p><p>This is the manual for version 1.2.1</p></div></div></div><hr></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="chapter"><a href="#id2510480">1. Introduction</a></span></dt><dd><dl><dt><span class="sect1"><a href="#credits">1.1. Credits</a></span></dt><dt><span class="sect1"><a href="#id2486632">1.2. More Information</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2487178">2. Installation</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2487183">2.1. Requirements</a></span></dt><dt><span class="sect1"><a href="#PGInstall">2.2. PostGIS</a></span></dt><dd><dl><dt><span class="sect2"><a href="#templatepostgis">2.2.1. Creating PostGIS spatially-enabled databases from an in-built
+ template</a></span></dt><dt><span class="sect2"><a href="#upgrading">2.2.2. Upgrading</a></span></dt><dt><span class="sect2"><a href="#id2539675">2.2.3. Common Problems</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2539820">2.3. JDBC</a></span></dt><dt><span class="sect1"><a href="#id2539897">2.4. Loader/Dumper</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2539937">3. Frequently Asked Questions</a></span></dt><dt><span class="chapter"><a href="#id2540415">4. Using PostGIS</a></span></dt><dd><dl><dt><span class="sect1"><a href="#RefObject">4.1. GIS Objects</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2540447">4.1.1. OpenGIS WKB and WKT</a></span></dt><dt><span class="sect2"><a href="#id2540555">4.1.2. PostGIS EWKB, EWKT and Canonical Forms</a></span></dt><dt><span class="sect2"><a href="#id2540720">4.1.3. SQL-MM Part 3</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2540805">4.2. Using OpenGIS Standards</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2540846">4.2.1. The SPATIAL_REF_SYS Table</a></span></dt><dt><span class="sect2"><a href="#id2541056">4.2.2. The GEOMETRY_COLUMNS Table</a></span></dt><dt><span class="sect2"><a href="#id2541202">4.2.3. Creating a Spatial Table</a></span></dt><dt><span class="sect2"><a href="#id2541316">4.2.4. Ensuring OpenGIS compliancy of geometries</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2541415">4.3. Loading GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2541428">4.3.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2541493">4.3.2. Using the Loader</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2541732">4.4. Retrieving GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2541745">4.4.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2541918">4.4.2. Using the Dumper</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542096">4.5. Building Indexes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542162">4.5.1. GiST Indexes</a></span></dt><dt><span class="sect2"><a href="#id2542241">4.5.2. Using Indexes</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542359">4.6. Complex Queries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542378">4.6.1. Taking Advantage of Indexes</a></span></dt><dt><span class="sect2"><a href="#id2542465">4.6.2. Examples of Spatial SQL</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542778">4.7. Using Mapserver</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542821">4.7.1. Basic Usage</a></span></dt><dt><span class="sect2"><a href="#id2543078">4.7.2. Frequently Asked Questions</a></span></dt><dt><span class="sect2"><a href="#id2543261">4.7.3. Advanced Usage</a></span></dt><dt><span class="sect2"><a href="#id2543442">4.7.4. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2543552">4.8. Java Clients (JDBC)</a></span></dt><dt><span class="sect1"><a href="#id2543637">4.9. C Clients (libpq)</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2543646">4.9.1. Text Cursors</a></span></dt><dt><span class="sect2"><a href="#id2543656">4.9.2. Binary Cursors</a></span></dt></dl></dd></dl></dd><dt><span class="chapter"><a href="#id2543668">5. Performance tips</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2543674">5.1. Small tables of large geometries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2543680">5.1.1. Problem description</a></span></dt><dt><span class="sect2"><a href="#id2543754">5.1.2. Workarounds</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2543838">5.2. CLUSTERing on geometry indices</a></span></dt><dt><span class="sect1"><a href="#id2543901">5.3. Avoiding dimension conversion</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2543964">6. PostGIS Reference</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2543977">6.1. OpenGIS Functions</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2543982">6.1.1. Management Functions</a></span></dt><dt><span class="sect2"><a href="#id2544071">6.1.2. Geometry Relationship Functions</a></span></dt><dt><span class="sect2"><a href="#id2544470">6.1.3. Geometry Processing Functions</a></span></dt><dt><span class="sect2"><a href="#id2544797">6.1.4. Geometry Accessors</a></span></dt><dt><span class="sect2"><a href="#id2545307">6.1.5. Geometry Constructors</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2545912">6.2. Postgis Extensions</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2545918">6.2.1. Management Functions</a></span></dt><dt><span class="sect2"><a href="#id2546255">6.2.2. Operators</a></span></dt><dt><span class="sect2"><a href="#id2546480">6.2.3. Measurement Functions</a></span></dt><dt><span class="sect2"><a href="#id2546767">6.2.4. Geometry Outputs</a></span></dt><dt><span class="sect2"><a href="#id2546907">6.2.5. Geometry Constructors</a></span></dt><dt><span class="sect2"><a href="#id2547257">6.2.6. Geometry Editors</a></span></dt><dt><span class="sect2"><a href="#id2547868">6.2.7. Linear Referencing</a></span></dt><dt><span class="sect2"><a href="#id2548094">6.2.8. Misc</a></span></dt><dt><span class="sect2"><a href="#id2549109">6.2.9. Long Transactions support</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2548552">6.3. SQL-MM Functions</a></span></dt><dt><span class="sect1"><a href="#id2550109">6.4. ArcSDE Functions</a></span></dt></dl></dd><dt><span class="chapter"><a href="#id2550227">7. Reporting Bugs</a></span></dt><dt><span class="appendix"><a href="#release_notes">A. Appendix</a></span></dt><dd><dl><dt><span class="sect1"><a href="#id2550307">A.1. Release Notes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2550312">A.1.1. Release 1.2.1</a></span></dt><dt><span class="sect2"><a href="#id2550358">A.1.2. Release 1.2.0</a></span></dt><dt><span class="sect2"><a href="#id2550392">A.1.3. Release 1.1.6</a></span></dt><dt><span class="sect2"><a href="#id2550510">A.1.4. Release 1.1.5</a></span></dt><dt><span class="sect2"><a href="#id2550624">A.1.5. Release 1.1.4</a></span></dt><dt><span class="sect2"><a href="#id2550751">A.1.6. Release 1.1.3</a></span></dt><dt><span class="sect2"><a href="#id2550918">A.1.7. Release 1.1.2</a></span></dt><dt><span class="sect2"><a href="#id2551051">A.1.8. Release 1.1.1</a></span></dt><dt><span class="sect2"><a href="#id2551182">A.1.9. Release 1.1.0</a></span></dt><dt><span class="sect2"><a href="#id2551513">A.1.10. Release 1.0.6</a></span></dt><dt><span class="sect2"><a href="#id2551621">A.1.11. Release 1.0.5</a></span></dt><dt><span class="sect2"><a href="#id2551756">A.1.12. Release 1.0.4</a></span></dt><dt><span class="sect2"><a href="#rel_1.0.3_upgrading">A.1.13. Release 1.0.3</a></span></dt><dt><span class="sect2"><a href="#id2552021">A.1.14. Release 1.0.2</a></span></dt><dt><span class="sect2"><a href="#id2552100">A.1.15. Release 1.0.1</a></span></dt><dt><span class="sect2"><a href="#id2552201">A.1.16. Release 1.0.0</a></span></dt><dt><span class="sect2"><a href="#id2552301">A.1.17. Release 1.0.0RC6</a></span></dt><dt><span class="sect2"><a href="#id2552375">A.1.18. Release 1.0.0RC5</a></span></dt><dt><span class="sect2"><a href="#id2552447">A.1.19. Release 1.0.0RC4</a></span></dt><dt><span class="sect2"><a href="#id2552573">A.1.20. Release 1.0.0RC3</a></span></dt><dt><span class="sect2"><a href="#id2552747">A.1.21. Release 1.0.0RC2</a></span></dt><dt><span class="sect2"><a href="#id2552846">A.1.22. Release 1.0.0RC1</a></span></dt></dl></dd></dl></dd></dl></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2510480"></a>Chapter 1. Introduction</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#credits">1.1. Credits</a></span></dt><dt><span class="sect1"><a href="#id2486632">1.2. More Information</a></span></dt></dl></div><p>PostGIS is developed by Refractions Research Inc, as a spatial
database technology research project. Refractions is a GIS and database
consulting company in Victoria, British Columbia, Canada, specializing in
data integration and custom software development. We plan on supporting
and developing PostGIS to support a range of important GIS functionality,
including full OpenGIS support, advanced topological constructs
(coverages, surfaces, networks), desktop user interface tools for viewing
- and editing GIS data, and web-based access tools.</p><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="credits"></a>1.1. Credits</h2></div></div><div></div></div><div class="variablelist"><dl><dt><span class="term">Sandro Santilli <strk at refractions.net></span></dt><dd><p>Coordinates all bug fixing and maintenance effort,
+ and editing GIS data, and web-based access tools.</p><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="credits"></a>1.1. Credits</h2></div></div></div><div class="variablelist"><dl><dt><span class="term">Sandro Santilli <strk at refractions.net></span></dt><dd><p>Coordinates all bug fixing and maintenance effort,
integration of new GEOS functionality, and new function
enhancements.</p></dd><dt><span class="term">Chris Hodgson <chodgson at refractions.net></span></dt><dd><p>Maintains new functions and the 7.2 index bindings.</p></dd><dt><span class="term">Paul Ramsey <pramsey at refractions.net></span></dt><dd><p>Keeps track of the documentation and packaging.</p></dd><dt><span class="term">Jeff Lounsbury <jeffloun at refractions.net></span></dt><dd><p>Original development of the Shape file loader/dumper.</p></dd><dt><span class="term">Dave Blasby <dblasby at gmail.com></span></dt><dd><p>The original developer of PostGIS. Dave wrote the server
side objects, index bindings, and many of the server side
@@ -28,7 +24,7 @@
Davis <mbdavis at vividsolutions.com> of Vivid Solutions in
making it all work.</p><p>The <a href="http://proj4.maptools.org" target="_top">Proj4</a>
cartographic projection library, and the work of Gerald Evenden
- and Frank Warmerdam in creating and maintaining it.</p></dd></dl></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2695778"></a>1.2. More Information</h2></div></div><div></div></div><div class="itemizedlist"><ul type="disc"><li><p>The latest software, documentation and news items are
+ and Frank Warmerdam in creating and maintaining it.</p></dd></dl></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2486632"></a>1.2. More Information</h2></div></div></div><div class="itemizedlist"><ul type="disc"><li><p>The latest software, documentation and news items are
available at the PostGIS web site, <a href="http://postgis.refractions.net" target="_top">http://postgis.refractions.net</a>.</p></li><li><p>More information about the GEOS geometry operations library is
available at<a href="http://geos.refractions.net" target="_top">
http://geos.refractions.net</a>.</p></li><li><p>More information about the Proj4 reprojection library is
@@ -37,21 +33,21 @@
PostgreSQL GiST development site, <a href="http://www.sai.msu.su/~megera/postgres/gist" target="_top">http://www.sai.msu.su/~megera/postgres/gist</a>.</p></li><li><p>More information about Mapserver internet map server is
available at <a href="http://mapserver.gis.umn.edu/" target="_top">http://mapserver.gis.umn.edu</a>.</p></li><li><p>The "<a href="http://www.opengis.org/docs/99-049.pdf" target="_top">Simple
Features for Specification for SQL</a>" is available at the
- OpenGIS Consortium web site: <a href="http://www.opengis.org" target="_top">http://www.opengis.org</a>.</p></li></ul></div></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2723071"></a>Chapter 2. Installation</h2></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#id2723077">2.1. Requirements</a></span></dt><dt><span class="sect1"><a href="#PGInstall">2.2. PostGIS</a></span></dt><dd><dl><dt><span class="sect2"><a href="#templatepostgis">2.2.1. Creating PostGIS spatially-enabled databases from an in-built
- template</a></span></dt><dt><span class="sect2"><a href="#upgrading">2.2.2. Upgrading</a></span></dt><dt><span class="sect2"><a href="#id2763269">2.2.3. Common Problems</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2763415">2.3. JDBC</a></span></dt><dt><span class="sect1"><a href="#id2763491">2.4. Loader/Dumper</a></span></dt></dl></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2723077"></a>2.1. Requirements</h2></div></div><div></div></div><p>PostGIS has the following requirements for building and usage:</p><div class="itemizedlist"><ul type="disc"><li><p>A complete installation of PostgreSQL (including server
+ OpenGIS Consortium web site: <a href="http://www.opengis.org" target="_top">http://www.opengis.org</a>.</p></li></ul></div></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2487178"></a>Chapter 2. Installation</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#id2487183">2.1. Requirements</a></span></dt><dt><span class="sect1"><a href="#PGInstall">2.2. PostGIS</a></span></dt><dd><dl><dt><span class="sect2"><a href="#templatepostgis">2.2.1. Creating PostGIS spatially-enabled databases from an in-built
+ template</a></span></dt><dt><span class="sect2"><a href="#upgrading">2.2.2. Upgrading</a></span></dt><dt><span class="sect2"><a href="#id2539675">2.2.3. Common Problems</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2539820">2.3. JDBC</a></span></dt><dt><span class="sect1"><a href="#id2539897">2.4. Loader/Dumper</a></span></dt></dl></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2487183"></a>2.1. Requirements</h2></div></div></div><p>PostGIS has the following requirements for building and usage:</p><div class="itemizedlist"><ul type="disc"><li><p>A complete installation of PostgreSQL (including server
headers). PostgreSQL is available from <a href="http://www.postgresql.org" target="_top">http://www.postgresql.org</a>.
- Version 7.2 or higher is required.</p></li><li><p>GNU C compiler (<tt class="filename">gcc</tt>). Some other ANSI C
+ Version 7.2 or higher is required.</p></li><li><p>GNU C compiler (<code class="filename">gcc</code>). Some other ANSI C
compilers can be used to compile PostGIS, but we find far fewer
- problems when compiling with <tt class="filename">gcc</tt>.</p></li><li><p>GNU Make (<tt class="filename">gmake</tt> or <tt class="filename">make</tt>).
- For many systems, GNU <tt class="filename">make</tt> is the default
- version of make. Check the version by invoking <tt class="filename">make -v</tt>.
- Other versions of <tt class="filename">make</tt> may not process the
- PostGIS <tt class="filename">Makefile</tt> properly.</p></li><li><p>(Recommended) Proj4 reprojection library. The Proj4 library is
+ problems when compiling with <code class="filename">gcc</code>.</p></li><li><p>GNU Make (<code class="filename">gmake</code> or <code class="filename">make</code>).
+ For many systems, GNU <code class="filename">make</code> is the default
+ version of make. Check the version by invoking <code class="filename">make -v</code>.
+ Other versions of <code class="filename">make</code> may not process the
+ PostGIS <code class="filename">Makefile</code> properly.</p></li><li><p>(Recommended) Proj4 reprojection library. The Proj4 library is
used to provide coordinate reprojection support within PostGIS.
Proj4 is available for download from <a href="http://www.remotesensing.org/proj" target="_top">http://www.remotesensing.org/proj</a>.</p></li><li><p>(Recommended) GEOS geometry library. The GEOS library is used
to provide geometry tests (Touches(), Contains(), Intersects()) and
operations (Buffer(), GeomUnion(), Difference()) within PostGIS.
- GEOS is available for download from <a href="http://geos.refractions.net" target="_top">http://geos.refractions.net</a>.</p></li></ul></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="PGInstall"></a>2.2. PostGIS</h2></div></div><div></div></div><p>The PostGIS module is a extension to the PostgreSQL backend
+ GEOS is available for download from <a href="http://geos.refractions.net" target="_top">http://geos.refractions.net</a>.</p></li></ul></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="PGInstall"></a>2.2. PostGIS</h2></div></div></div><p>The PostGIS module is a extension to the PostgreSQL backend
server. As such, PostGIS 1.2.1 <span class="emphasis"><em>requires</em></span>
full PostgreSQL server headers access in order to compile. The
PostgreSQL source code is available at <a href="http://www.postgresql.org" target="_top">http://www.postgresql.org</a>.</p><p>PostGIS 1.2.1 can be built against PostgreSQL
@@ -67,44 +63,44 @@
| tar xvf -</pre></li><li><p>Enter the postgis-1.2.1 directory, and run:
</p><pre class="programlisting"># ./configure</pre><div class="itemizedlist"><ul type="disc"><li><p>If you want support for coordinate reprojection, you must
have the Proj4 library installed. If ./configure didn't find
- it, try using <tt class="code">--with-proj=PATH</tt> switch specify a
+ it, try using <code class="code">--with-proj=PATH</code> switch specify a
specific Proj4 installation directory.</p></li><li><p>If you want to use GEOS functionality, you must have the
GEOS library installed. If ./configure didn't find it, try
- using <tt class="code">--with-geos=PATH</tt> to specify the full path to
+ using <code class="code">--with-geos=PATH</code> to specify the full path to
the geos-config program full path.</p></li></ul></div></li><li><p>Run the compile and install commands.</p><pre class="programlisting"># make # make install</pre><p>All files are installed using information provided by
- <tt class="filename">pg_config</tt></p><div class="itemizedlist"><ul type="disc"><li><p>Libraries are installed <tt class="filename">[pkglibdir]/lib/contrib</tt>.</p></li><li><p>Important support files such as <tt class="filename">lwpostgis.sql</tt>
- are installed in <tt class="filename">[prefix]/share/contrib</tt>.</p></li><li><p>Loader and dumper binaries are installed in
- <tt class="filename">[bindir]/</tt>.</p></li></ul></div></li><li><p>PostGIS requires the PL/pgSQL procedural language extension.
- Before loading the <tt class="filename">lwpostgis.sql</tt> file, you must
- first enable PL/pgSQL. You should use the <tt class="filename">createlang</tt>
+ <code class="filename">pg_config</code></p><div class="itemizedlist"><ul type="disc"><li><p>Libraries are installed <code class="filename">[pkglibdir]/lib/contrib</code>.</p></li><li><p>Important support files such as <code class="filename">lwpostgis.sql</code>
+ are installed in <code class="filename">[prefix]/share/contrib</code>.</p></li><li><p>Loader and dumper binaries are installed in
+ <code class="filename">[bindir]/</code>.</p></li></ul></div></li><li><p>PostGIS requires the PL/pgSQL procedural language extension.
+ Before loading the <code class="filename">lwpostgis.sql</code> file, you must
+ first enable PL/pgSQL. You should use the <code class="filename">createlang</code>
command. The PostgreSQL Programmer's Guide has the details if
you want to this manually for some reason.</p><pre class="programlisting"># createlang plpgsql [yourdatabase]</pre></li><li><p>Now load the PostGIS object and function definitions into your
- database by loading the <tt class="filename">lwpostgis.sql</tt>
+ database by loading the <code class="filename">lwpostgis.sql</code>
definitions file.</p><pre class="programlisting"># psql -d [yourdatabase] -f lwpostgis.sql</pre><p>The PostGIS server extensions are now loaded and ready to use.</p></li><li><p>For a complete set of EPSG coordinate system definition
- identifiers, you can also load the <tt class="filename">spatial_ref_sys.sql</tt>
- definitions file and populate the <tt class="varname">SPATIAL_REF_SYS</tt>
+ identifiers, you can also load the <code class="filename">spatial_ref_sys.sql</code>
+ definitions file and populate the <code class="varname">SPATIAL_REF_SYS</code>
table.</p><pre class="programlisting"># psql -d [yourdatabase] -f spatial_ref_sys.sql</pre></li></ol></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="templatepostgis"></a>2.2.1. Creating PostGIS spatially-enabled databases from an in-built
- template</h3></div></div><div></div></div><p>Some packaged distributions of PostGIS (in particular the Win32
+ template</h3></div></div></div><p>Some packaged distributions of PostGIS (in particular the Win32
installers for PostGIS >= 1.1.5) load the PostGIS functions into a
- template database called <tt class="varname">template_postgis</tt>. If the
- <tt class="varname">template_postgis</tt> database exists in your PostgreSQL
+ template database called <code class="varname">template_postgis</code>. If the
+ <code class="varname">template_postgis</code> database exists in your PostgreSQL
installation then it is possible for users and/or applications to
create spatially-enabled databases using a single command. Note that
in both cases, the database user must have been granted the privilege
to create new databases.</p><p>From the shell:</p><pre class="programlisting"># createdb -T template_postgis my_spatial_db</pre><p>From SQL:</p><pre class="programlisting">postgres=# CREATE DATABASE my_spatial_db
- TEMPLATE=template_postgis</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="upgrading"></a>2.2.2. Upgrading</h3></div></div><div></div></div><p>Upgrading existing spatial databases can be tricky as it
+ TEMPLATE=template_postgis</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="upgrading"></a>2.2.2. Upgrading</h3></div></div></div><p>Upgrading existing spatial databases can be tricky as it
requires replacement or introduction of new PostGIS object
definitions.</p><p>Unfortunately not all definitions can be easily replaced in a
live database, so sometimes your best bet is a dump/reload process.</p><p>PostGIS provides a SOFT UPGRADE procedure for minor or bugfix
releases, and an HARD UPGRADE procedure for major releases.</p><p>Before attempting to upgrade postgis, it is always worth to
backup your data. If you use the -Fc flag to pg_dump you will always
- be able to restore the dump with an HARD UPGRADE.</p><div class="sect3" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="soft_upgrade"></a>2.2.2.1. Soft upgrade</h4></div></div><div></div></div><p>Soft upgrade consists of sourcing the lwpostgis_upgrade.sql
+ be able to restore the dump with an HARD UPGRADE.</p><div class="sect3" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="soft_upgrade"></a>2.2.2.1. Soft upgrade</h4></div></div></div><p>Soft upgrade consists of sourcing the lwpostgis_upgrade.sql
script in your spatial database:</p><pre class="programlisting">$ psql -f lwpostgis_upgrade.sql -d your_spatial_database</pre><p>If a soft upgrade is not possible the script will abort and
you will be warned about HARD UPGRADE being required, so do not
- hesitate to try a soft upgrade first.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>If you can't find the <tt class="filename">lwpostgis_upgrade.sql</tt>
+ hesitate to try a soft upgrade first.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>If you can't find the <code class="filename">lwpostgis_upgrade.sql</code>
file you are probably using a version prior to 1.1 and must
generate that file by yourself. This is done with the following
- command:</p><pre class="programlisting">$ utils/postgis_proc_upgrade.pl lwpostgis.sql > lwpostgis_upgrade.sql</pre></div></div><div class="sect3" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="hard_upgrade"></a>2.2.2.2. Hard upgrade</h4></div></div><div></div></div><p>By HARD UPGRADE we intend full dump/reload of postgis-enabled
+ command:</p><pre class="programlisting">$ utils/postgis_proc_upgrade.pl lwpostgis.sql > lwpostgis_upgrade.sql</pre></div></div><div class="sect3" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="hard_upgrade"></a>2.2.2.2. Hard upgrade</h4></div></div></div><p>By HARD UPGRADE we intend full dump/reload of postgis-enabled
databases. You need an HARD UPGRADE when postgis objects'
internal storage changes or when SOFT UPGRADE is not possible. The
<a href="#release_notes" title="Appendix A. Appendix">Release Notes</a> appendix reports
@@ -150,13 +146,13 @@
newdb=> delete from spatial_ref_sys;
DROP
newdb=> \i spatial_ref_sys.sql
- </pre></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2763269"></a>2.2.3. Common Problems</h3></div></div><div></div></div><p>There are several things to check when your installation or
+ </pre></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2539675"></a>2.2.3. Common Problems</h3></div></div></div><p>There are several things to check when your installation or
upgrade doesn't go as you expected.</p><div class="orderedlist"><ol type="1"><li><p>It is easiest if you untar the PostGIS distribution into the
contrib directory under the PostgreSQL source tree. However, if
this is not possible for some reason, you can set the
- <tt class="varname">PGSQL_SRC</tt> environment variable to the path to
+ <code class="varname">PGSQL_SRC</code> environment variable to the path to
the PostgreSQL source directory. This will allow you to compile
- PostGIS, but the <span><b class="command">make install</b></span> may not work, so
+ PostGIS, but the <span><strong class="command">make install</strong></span> may not work, so
be prepared to copy the PostGIS library and executable files to
the appropriate locations yourself.</p></li><li><p>Check that you you have installed PostgreSQL 7.2 or newer,
and that you are compiling against the same version of the
@@ -169,36 +165,36 @@
PostgreSQL which is running, connect to the database using psql
and run this query:</p><pre class="programlisting">SELECT version();</pre><p>If you are running an RPM based distribution, you can check
for the existence of pre-installed packages using the
- <span><b class="command">rpm</b></span> command as follows: <span><b class="command">rpm -qa | grep
- postgresql</b></span></p></li></ol></div><p>Also check that you have made any necessary changes to the top
+ <span><strong class="command">rpm</strong></span> command as follows: <span><strong class="command">rpm -qa | grep
+ postgresql</strong></span></p></li></ol></div><p>Also check that you have made any necessary changes to the top
of the Makefile.config. This includes:</p><div class="orderedlist"><ol type="1"><li><p>If you want to be able to do coordinate reprojections, you
must install the Proj4 library on your system, set the
- <tt class="varname">USE_PROJ</tt> variable to 1 and the
- <tt class="varname">PROJ_DIR</tt> to your installation prefix in the
+ <code class="varname">USE_PROJ</code> variable to 1 and the
+ <code class="varname">PROJ_DIR</code> to your installation prefix in the
Makefile.config.</p></li><li><p>If you want to be able to use GEOS functions you must
install the GEOS library on your system, and set the
- <tt class="varname">USE_GEOS</tt> to 1 and the <tt class="varname">GEOS_DIR</tt>
- to your installation prefix in the Makefile.config</p></li></ol></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2763415"></a>2.3. JDBC</h2></div></div><div></div></div><p>The JDBC extensions provide Java objects corresponding to the
+ <code class="varname">USE_GEOS</code> to 1 and the <code class="varname">GEOS_DIR</code>
+ to your installation prefix in the Makefile.config</p></li></ol></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2539820"></a>2.3. JDBC</h2></div></div></div><p>The JDBC extensions provide Java objects corresponding to the
internal PostGIS types. These objects can be used to write Java clients
which query the PostGIS database and draw or do calculations on the GIS
- data in PostGIS.</p><div class="orderedlist"><ol type="1"><li><p>Enter the <tt class="filename">jdbc</tt> sub-directory of the
- PostGIS distribution.</p></li><li><p>Edit the <tt class="filename">Makefile</tt> to provide the correct
- paths of your java compiler (<tt class="varname">JAVAC</tt>) and
- interpreter (<tt class="varname">JAVA</tt>).</p></li><li><p>Run the <tt class="filename">make</tt> command. Copy the
- <tt class="filename">postgis.jar</tt> file to wherever you keep your java
- libraries.</p></li></ol></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2763491"></a>2.4. Loader/Dumper</h2></div></div><div></div></div><p>The data loader and dumper are built and installed automatically
+ data in PostGIS.</p><div class="orderedlist"><ol type="1"><li><p>Enter the <code class="filename">jdbc</code> sub-directory of the
+ PostGIS distribution.</p></li><li><p>Edit the <code class="filename">Makefile</code> to provide the correct
+ paths of your java compiler (<code class="varname">JAVAC</code>) and
+ interpreter (<code class="varname">JAVA</code>).</p></li><li><p>Run the <code class="filename">make</code> command. Copy the
+ <code class="filename">postgis.jar</code> file to wherever you keep your java
+ libraries.</p></li></ol></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2539897"></a>2.4. Loader/Dumper</h2></div></div></div><p>The data loader and dumper are built and installed automatically
as part of the PostGIS build. To build and install them manually:</p><pre class="programlisting"># cd postgis-1.2.1/loader # make #
- make install</pre><p>The loader is called <tt class="filename">shp2pgsql</tt> and converts
+ make install</pre><p>The loader is called <code class="filename">shp2pgsql</code> and converts
ESRI Shape files into SQL suitable for loading in PostGIS/PostgreSQL.
- The dumper is called <tt class="filename">pgsql2shp</tt> and converts PostGIS
+ The dumper is called <code class="filename">pgsql2shp</code> and converts PostGIS
tables (or queries) into ESRI Shape files. For more verbose
- documentation, see the online help, and the manual pages.</p></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2763531"></a>Chapter 3. Frequently Asked Questions</h2></div></div><div></div></div><div class="qandaset"><dl><dt>3.1. <a href="#id2763539">What kind of geometric objects can I store?</a></dt><dt>3.2. <a href="#id2763560">How do I insert a GIS object into the database?</a></dt><dt>3.3. <a href="#id2763666">How do I construct a spatial query?</a></dt><dt>3.4. <a href="#id2763736">How do I speed up spatial queries on large tables?</a></dt><dt>3.5. <a href="#id2763824">Why aren't PostgreSQL R-Tree indexes supported?</a></dt><dt>3.6. <a href="#id2763883">Why should I use the AddGeometryColumn()
- function and all the other OpenGIS stuff?</a></dt><dt>3.7. <a href="#id2763930">What is the best way to find all objects within a radius of
- another object?</a></dt><dt>3.8. <a href="#id2763979">How do I perform a coordinate reprojection as part of a query?</a></dt></dl><table border="0" summary="Q and A Set"><col align="left" width="1%"><tbody><tr class="question"><td align="left" valign="top"><a name="id2763539"></a><a name="id2763542"></a><b>3.1.</b></td><td align="left" valign="top"><p>What kind of geometric objects can I store?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>You can store point, line, polygon, multipoint, multiline,
+ documentation, see the online help, and the manual pages.</p></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2539937"></a>Chapter 3. Frequently Asked Questions</h2></div></div></div><div class="qandaset"><dl><dt>3.1. <a href="#id2539945">What kind of geometric objects can I store?</a></dt><dt>3.2. <a href="#id2539965">How do I insert a GIS object into the database?</a></dt><dt>3.3. <a href="#id2540071">How do I construct a spatial query?</a></dt><dt>3.4. <a href="#id2540141">How do I speed up spatial queries on large tables?</a></dt><dt>3.5. <a href="#id2540229">Why aren't PostgreSQL R-Tree indexes supported?</a></dt><dt>3.6. <a href="#id2540288">Why should I use the AddGeometryColumn()
+ function and all the other OpenGIS stuff?</a></dt><dt>3.7. <a href="#id2540335">What is the best way to find all objects within a radius of
+ another object?</a></dt><dt>3.8. <a href="#id2540384">How do I perform a coordinate reprojection as part of a query?</a></dt></dl><table border="0" summary="Q and A Set"><col align="left" width="1%"><tbody><tr class="question"><td align="left" valign="top"><a name="id2539945"></a><a name="id2539947"></a><b>3.1.</b></td><td align="left" valign="top"><p>What kind of geometric objects can I store?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>You can store point, line, polygon, multipoint, multiline,
multipolygon, and geometrycollections. These are specified in the
- Open GIS Well Known Text Format (with XYZ,XYM,XYZM extentions).</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763560"></a><a name="id2763562"></a><b>3.2.</b></td><td align="left" valign="top"><p>How do I insert a GIS object into the database?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>First, you need to create a table with a column of type
+ Open GIS Well Known Text Format (with XYZ,XYM,XYZM extentions).</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2539965"></a><a name="id2539967"></a><b>3.2.</b></td><td align="left" valign="top"><p>How do I insert a GIS object into the database?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>First, you need to create a table with a column of type
"geometry" to hold your GIS data. Connect to your database
- with <tt class="filename">psql</tt> and try the following SQL:</p><pre class="programlisting">CREATE TABLE gtest ( ID int4, NAME varchar(20) );
+ with <code class="filename">psql</code> and try the following SQL:</p><pre class="programlisting">CREATE TABLE gtest ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn('',
'gtest','geom',-1,'LINESTRING',2);</pre><p>If the geometry column addition fails, you probably have not
loaded the PostGIS functions and objects into this database. See the
@@ -208,7 +204,7 @@
'First Geometry', GeomFromText('LINESTRING(2 3,4 5,6 5,7
8)', -1));</pre><p>For more information about other GIS objects, see the <a href="#RefObject" title="4.1. GIS Objects">object reference</a>.</p><p>To view your GIS data in the table:</p><pre class="programlisting">SELECT id, name, AsText(geom) AS geom FROM gtest;</pre><p>The return value should look something like this:</p><pre class="programlisting">id | name | geom
----+----------------+----------------------------- 1 | First
- Geometry | LINESTRING(2 3,4 5,6 5,7 8) (1 row)</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763666"></a><a name="id2763669"></a><b>3.3.</b></td><td align="left" valign="top"><p>How do I construct a spatial query?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>The same way you construct any other database query, as an SQL
+ Geometry | LINESTRING(2 3,4 5,6 5,7 8) (1 row)</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540071"></a><a name="id2540074"></a><b>3.3.</b></td><td align="left" valign="top"><p>How do I construct a spatial query?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>The same way you construct any other database query, as an SQL
combination of return values, functions, and boolean tests.</p><p>For spatial queries, there are two issues that are important
to keep in mind while constructing your query: is there a spatial
index you can make use of; and, are you doing expensive calculations
@@ -225,9 +221,9 @@
<span class="emphasis"><em>might</em></span> meet the condition of interest. The
spatial functions are then use to test the condition exactly.</p><pre class="programlisting">SELECT id, the_geom FROM thetable WHERE the_geom
&& 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))' AND
- Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))';</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763736"></a><a name="id2763738"></a><b>3.4.</b></td><td align="left" valign="top"><p>How do I speed up spatial queries on large tables?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>Fast queries on large tables is the <span class="emphasis"><em>raison
+ Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))';</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540141"></a><a name="id2540143"></a><b>3.4.</b></td><td align="left" valign="top"><p>How do I speed up spatial queries on large tables?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>Fast queries on large tables is the <span class="emphasis"><em>raison
d'etre</em></span> of spatial databases (along with transaction
- support) so having a good index is important.</p><p>To build a spatial index on a table with a <tt class="varname">geometry</tt>
+ support) so having a good index is important.</p><p>To build a spatial index on a table with a <code class="varname">geometry</code>
column, use the "CREATE INDEX" function as follows:</p><pre class="programlisting">CREATE INDEX [indexname] ON [tablename] USING GIST (
[geometrycolumn] );</pre><p>The "USING GIST" option tells the server to use a GiST
(Generalized Search Tree) index.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>GiST indexes are assumed to be lossy. Lossy indexes uses a
@@ -236,8 +232,8 @@
enough information about your index to make rational decisions about
when to use it. To do this, you have to "gather statistics"
on your geometry tables.</p><p>For PostgreSQL 8.0.x and greater, just run the
- <span><b class="command">VACUUM ANALYZE</b></span> command.</p><p>For PostgreSQL 7.4.x and below, run the <span><b class="command">SELECT
- UPDATE_GEOMETRY_STATS()</b></span> command.</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763824"></a><a name="id2763826"></a><b>3.5.</b></td><td align="left" valign="top"><p>Why aren't PostgreSQL R-Tree indexes supported?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>Early versions of PostGIS used the PostgreSQL R-Tree indexes.
+ <span><strong class="command">VACUUM ANALYZE</strong></span> command.</p><p>For PostgreSQL 7.4.x and below, run the <span><strong class="command">SELECT
+ UPDATE_GEOMETRY_STATS()</strong></span> command.</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540229"></a><a name="id2540231"></a><b>3.5.</b></td><td align="left" valign="top"><p>Why aren't PostgreSQL R-Tree indexes supported?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>Early versions of PostGIS used the PostgreSQL R-Tree indexes.
However, PostgreSQL R-Trees have been completely discarded since
version 0.6, and spatial indexing is provided with an
R-Tree-over-GiST scheme.</p><p>Our tests have shown search speed for native R-Tree and GiST
@@ -249,22 +245,22 @@
"lossy" trick of substituting the bounding box for the
feature itself.</p></li><li><p>R-Tree indexes in PostgreSQL are not "null safe",
so building an index on a geometry column which contains null
- geometries will fail.</p></li></ul></div></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763883"></a><a name="id2763885"></a><b>3.6.</b></td><td align="left" valign="top"><p>Why should I use the <tt class="varname">AddGeometryColumn()</tt>
- function and all the other OpenGIS stuff?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>If you do not want to use the OpenGIS support functions, you
+ geometries will fail.</p></li></ul></div></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540288"></a><a name="id2540290"></a><b>3.6.</b></td><td align="left" valign="top"><p>Why should I use the <code class="varname">AddGeometryColumn()</code>
+ function and all the other OpenGIS stuff?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>If you do not want to use the OpenGIS support functions, you
do not have to. Simply create tables as in older versions, defining
your geometry columns in the CREATE statement. All your geometries
will have SRIDs of -1, and the OpenGIS meta-data tables will
<span class="emphasis"><em>not</em></span> be filled in properly. However, this will
cause most applications based on PostGIS to fail, and it is
- generally suggested that you do use <tt class="varname">AddGeometryColumn()</tt>
+ generally suggested that you do use <code class="varname">AddGeometryColumn()</code>
to create geometry tables.</p><p>Mapserver is one application which makes use of the
- <tt class="varname">geometry_columns</tt> meta-data. Specifically,
+ <code class="varname">geometry_columns</code> meta-data. Specifically,
Mapserver can use the SRID of the geometry column to do on-the-fly
- reprojection of features into the correct map projection.</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763930"></a><a name="id2763933"></a><b>3.7.</b></td><td align="left" valign="top"><p>What is the best way to find all objects within a radius of
- another object?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>To use the database most efficiently, it is best to do radius
+ reprojection of features into the correct map projection.</p></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540335"></a><a name="id2540338"></a><b>3.7.</b></td><td align="left" valign="top"><p>What is the best way to find all objects within a radius of
+ another object?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>To use the database most efficiently, it is best to do radius
queries which combine the radius test with a bounding box test: the
bounding box test uses the spatial index, giving fast access to a
- subset of data which the radius test is then applied to.</p><p>The <tt class="varname">Expand()</tt> function is a handy way of
+ subset of data which the radius test is then applied to.</p><p>The <code class="varname">Expand()</code> function is a handy way of
enlarging a bounding box to allow an index search of a region of
interest. The combination of a fast access index clause and a slower
accurate distance test provides the best combination of speed and
@@ -272,15 +268,15 @@
1000) the following query would work well:</p><pre class="programlisting">SELECT * FROM GEOTABLE WHERE GEOCOLUMN &&
Expand(GeomFromText('POINT(1000 1000)',-1),100) AND
Distance(GeomFromText('POINT(1000 1000)',-1),GEOCOLUMN)
- < 100;</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2763979"></a><a name="id2763981"></a><b>3.8.</b></td><td align="left" valign="top"><p>How do I perform a coordinate reprojection as part of a query?</p></td></tr><tr class="answer"><td align="left" valign="top"><b></b></td><td align="left" valign="top"><p>To perform a reprojection, both the source and destination
+ < 100;</pre></td></tr><tr class="question"><td align="left" valign="top"><a name="id2540384"></a><a name="id2540386"></a><b>3.8.</b></td><td align="left" valign="top"><p>How do I perform a coordinate reprojection as part of a query?</p></td></tr><tr class="answer"><td align="left" valign="top"></td><td align="left" valign="top"><p>To perform a reprojection, both the source and destination
coordinate systems must be defined in the SPATIAL_REF_SYS table, and
the geometries being reprojected must already have an SRID set on
them. Once that is done, a reprojection is as simple as referring to
- the desired destination SRID.</p><pre class="programlisting">SELECT Transform(GEOM,4269) FROM GEOTABLE;</pre></td></tr></tbody></table></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2764010"></a>Chapter 4. Using PostGIS</h2></div></div><div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#RefObject">4.1. GIS Objects</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2764042">4.1.1. OpenGIS WKB and WKT</a></span></dt><dt><span class="sect2"><a href="#id2764151">4.1.2. PostGIS EWKB, EWKT and Canonical Forms</a></span></dt><dt><span class="sect2"><a href="#id2764316">4.1.3. SQL-MM Part 3</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2764400">4.2. Using OpenGIS Standards</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2764441">4.2.1. The SPATIAL_REF_SYS Table</a></span></dt><dt><span class="sect2"><a href="#id2764652">4.2.2. The GEOMETRY_COLUMNS Table</a></span></dt><dt><span class="sect2"><a href="#id2764797">4.2.3. Creating a Spatial Table</a></span></dt><dt><span class="sect2"><a href="#id2764911">4.2.4. Ensuring OpenGIS compliancy of geometries</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765010">4.3. Loading GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765024">4.3.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2765088">4.3.2. Using the Loader</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765328">4.4. Retrieving GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765341">4.4.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2765514">4.4.2. Using the Dumper</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765691">4.5. Building Indexes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765757">4.5.1. GiST Indexes</a></span></dt><dt><span class="sect2"><a href="#id2765837">4.5.2. Using Indexes</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2765955">4.6. Complex Queries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2765973">4.6.1. Taking Advantage of Indexes</a></span></dt><dt><span class="sect2"><a href="#id2766061">4.6.2. Examples of Spatial SQL</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2766374">4.7. Using Mapserver</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2766416">4.7.1. Basic Usage</a></span></dt><dt><span class="sect2"><a href="#id2766674">4.7.2. Frequently Asked Questions</a></span></dt><dt><span class="sect2"><a href="#id2766856">4.7.3. Advanced Usage</a></span></dt><dt><span class="sect2"><a href="#id2767038">4.7.4. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2767148">4.8. Java Clients (JDBC)</a></span></dt><dt><span class="sect1"><a href="#id2767233">4.9. C Clients (libpq)</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2767242">4.9.1. Text Cursors</a></span></dt><dt><span class="sect2"><a href="#id2767252">4.9.2. Binary Cursors</a></span></dt></dl></dd></dl></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="RefObject"></a>4.1. GIS Objects</h2></div></div><div></div></div><p>The GIS objects supported by PostGIS are a superset of the
+ the desired destination SRID.</p><pre class="programlisting">SELECT Transform(GEOM,4269) FROM GEOTABLE;</pre></td></tr></tbody></table></div></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="id2540415"></a>Chapter 4. Using PostGIS</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="sect1"><a href="#RefObject">4.1. GIS Objects</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2540447">4.1.1. OpenGIS WKB and WKT</a></span></dt><dt><span class="sect2"><a href="#id2540555">4.1.2. PostGIS EWKB, EWKT and Canonical Forms</a></span></dt><dt><span class="sect2"><a href="#id2540720">4.1.3. SQL-MM Part 3</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2540805">4.2. Using OpenGIS Standards</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2540846">4.2.1. The SPATIAL_REF_SYS Table</a></span></dt><dt><span class="sect2"><a href="#id2541056">4.2.2. The GEOMETRY_COLUMNS Table</a></span></dt><dt><span class="sect2"><a href="#id2541202">4.2.3. Creating a Spatial Table</a></span></dt><dt><span class="sect2"><a href="#id2541316">4.2.4. Ensuring OpenGIS compliancy of geometries</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2541415">4.3. Loading GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2541428">4.3.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2541493">4.3.2. Using the Loader</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2541732">4.4. Retrieving GIS Data</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2541745">4.4.1. Using SQL</a></span></dt><dt><span class="sect2"><a href="#id2541918">4.4.2. Using the Dumper</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542096">4.5. Building Indexes</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542162">4.5.1. GiST Indexes</a></span></dt><dt><span class="sect2"><a href="#id2542241">4.5.2. Using Indexes</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542359">4.6. Complex Queries</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542378">4.6.1. Taking Advantage of Indexes</a></span></dt><dt><span class="sect2"><a href="#id2542465">4.6.2. Examples of Spatial SQL</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2542778">4.7. Using Mapserver</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2542821">4.7.1. Basic Usage</a></span></dt><dt><span class="sect2"><a href="#id2543078">4.7.2. Frequently Asked Questions</a></span></dt><dt><span class="sect2"><a href="#id2543261">4.7.3. Advanced Usage</a></span></dt><dt><span class="sect2"><a href="#id2543442">4.7.4. Examples</a></span></dt></dl></dd><dt><span class="sect1"><a href="#id2543552">4.8. Java Clients (JDBC)</a></span></dt><dt><span class="sect1"><a href="#id2543637">4.9. C Clients (libpq)</a></span></dt><dd><dl><dt><span class="sect2"><a href="#id2543646">4.9.1. Text Cursors</a></span></dt><dt><span class="sect2"><a href="#id2543656">4.9.2. Binary Cursors</a></span></dt></dl></dd></dl></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="RefObject"></a>4.1. GIS Objects</h2></div></div></div><p>The GIS objects supported by PostGIS are a superset of the
"Simple Features" defined by the OpenGIS Consortium (OGC). As of
version 0.9, PostGIS supports all the objects and functions specified in
the OGC "Simple Features for SQL" specification.</p><p>PostGIS extends the standard with support for 3DZ,3DM and 4D
- coordinates.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764042"></a>4.1.1. OpenGIS WKB and WKT</h3></div></div><div></div></div><p>The OpenGIS specification defines two standard ways of
+ coordinates.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2540447"></a>4.1.1. OpenGIS WKB and WKT</h3></div></div></div><p>The OpenGIS specification defines two standard ways of
expressing spatial objects: the Well-Known Text (WKT) form and the
Well-Known Binary (WKB) form. Both WKT and WKB include information
about the type of the object and the coordinates which form the
@@ -295,7 +291,7 @@
GeometryFromText(text WKT, SRID);</pre><p>For example, a valid insert statement to create and insert an
OGC spatial object would be:</p><pre class="programlisting">INSERT INTO SPATIALTABLE ( THE_GEOM, THE_NAME ) VALUES
( GeomFromText('POINT(-126.4 45.32)', 312), 'A Place'
- )</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764151"></a>4.1.2. PostGIS EWKB, EWKT and Canonical Forms</h3></div></div><div></div></div><p>OGC formats only support 2d geometries, and the associated SRID
+ )</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2540555"></a>4.1.2. PostGIS EWKB, EWKT and Canonical Forms</h3></div></div></div><p>OGC formats only support 2d geometries, and the associated SRID
is *never* embedded in the input/output representations.</p><p>Postgis extended formats are currently superset of OGC one
(every valid WKB/WKT is a valid EWKB/EWKT) but this might vary in the
future, specifically if OGC comes out with a new format conflicting
@@ -318,7 +314,7 @@
hex form) - Input - binary: EWKB ascii: HEXEWKB|EWKT </pre><p>For example this statement reads EWKT and returns HEXEWKB in the
process of canonical ascii input/output:</p><pre class="programlisting">=# SELECT 'SRID=4;POINT(0 0)'::geometry;
geometry ----------------------------------------------------
- 01010000200400000000000000000000000000000000000000 (1 row)</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764316"></a>4.1.3. SQL-MM Part 3</h3></div></div><div></div></div><p>The SQL Multimedia Applications Spatial specification extends
+ 01010000200400000000000000000000000000000000000000 (1 row)</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2540720"></a>4.1.3. SQL-MM Part 3</h3></div></div></div><p>The SQL Multimedia Applications Spatial specification extends
the simple features for SQL spec by defining a number of circularly
interpolated curves.</p><p>The SQL-MM definitions include 3dm, 3dz and 4d coordinates, but
do not allow the embedding of SRID information.</p><p>The well-known text extensions are not yet fully supported.
@@ -328,21 +324,21 @@
11.5 11, 11 11.5, 11 11)))</p></li></ul></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>Currently, PostGIS cannot support the use of Compound Curves
in a Curve Polygon.</p></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>All floating point comparisons within the SQL-MM
implementation are performed to a specified tolerance, currently
- 1E-8.</p></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2764400"></a>4.2. Using OpenGIS Standards</h2></div></div><div></div></div><p>The OpenGIS "Simple Features Specification for SQL"
+ 1E-8.</p></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2540805"></a>4.2. Using OpenGIS Standards</h2></div></div></div><p>The OpenGIS "Simple Features Specification for SQL"
defines standard GIS object types, the functions required to manipulate
them, and a set of meta-data tables. In order to ensure that meta-data
remain consistent, operations such as creating and removing a spatial
- column are carried out through special procedures defined by OpenGIS.</p><p>There are two OpenGIS meta-data tables: <tt class="varname">SPATIAL_REF_SYS</tt>
- and <tt class="varname">GEOMETRY_COLUMNS</tt>. The <tt class="varname">SPATIAL_REF_SYS</tt>
+ column are carried out through special procedures defined by OpenGIS.</p><p>There are two OpenGIS meta-data tables: <code class="varname">SPATIAL_REF_SYS</code>
+ and <code class="varname">GEOMETRY_COLUMNS</code>. The <code class="varname">SPATIAL_REF_SYS</code>
table holds the numeric IDs and textual descriptions of coordinate
- systems used in the spatial database.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764441"></a>4.2.1. The SPATIAL_REF_SYS Table</h3></div></div><div></div></div><p>The <tt class="varname">SPATIAL_REF_SYS</tt> table definition is as
+ systems used in the spatial database.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2540846"></a>4.2.1. The SPATIAL_REF_SYS Table</h3></div></div></div><p>The <code class="varname">SPATIAL_REF_SYS</code> table definition is as
follows:</p><pre class="programlisting">CREATE TABLE SPATIAL_REF_SYS ( SRID INTEGER NOT NULL
PRIMARY KEY, AUTH_NAME VARCHAR(256), AUTH_SRID INTEGER, SRTEXT
- VARCHAR(2048), PROJ4TEXT VARCHAR(2048) )</pre><p>The <tt class="varname">SPATIAL_REF_SYS</tt> columns are as follows:</p><div class="variablelist"><dl><dt><span class="term">SRID</span></dt><dd><p>An integer value that uniquely identifies the Spatial
+ VARCHAR(2048), PROJ4TEXT VARCHAR(2048) )</pre><p>The <code class="varname">SPATIAL_REF_SYS</code> columns are as follows:</p><div class="variablelist"><dl><dt><span class="term">SRID</span></dt><dd><p>An integer value that uniquely identifies the Spatial
Referencing System (SRS) within the database.</p></dd><dt><span class="term">AUTH_NAME</span></dt><dd><p>The name of the standard or standards body that is being
cited for this reference system. For example, "EPSG"
- would be a valid <tt class="varname">AUTH_NAME</tt>.</p></dd><dt><span class="term">AUTH_SRID</span></dt><dd><p>The ID of the Spatial Reference System as defined by the
- Authority cited in the <tt class="varname">AUTH_NAME</tt>. In the case
+ would be a valid <code class="varname">AUTH_NAME</code>.</p></dd><dt><span class="term">AUTH_SRID</span></dt><dd><p>The ID of the Spatial Reference System as defined by the
+ Authority cited in the <code class="varname">AUTH_NAME</code>. In the case
of EPSG, this is where the EPSG projection code would go.</p></dd><dt><span class="term">SRTEXT</span></dt><dd><p>The Well-Known Text representation of the Spatial
Reference System. An example of a WKT SRS representation is:</p><pre class="programlisting">PROJCS["NAD83 / UTM Zone 10N",
GEOGCS["NAD83",
@@ -362,14 +358,14 @@
Specification" at <a href="http://www.opengis.org/techno/specs.htm" target="_top">http://www.opengis.org/techno/specs.htm</a>.
For information on the European Petroleum Survey Group (EPSG)
and their database of spatial reference systems, see <a href="http://epsg.org" target="_top">http://epsg.org</a>.</p></dd><dt><span class="term">PROJ4TEXT</span></dt><dd><p>PostGIS uses the Proj4 library to provide coordinate
- transformation capabilities. The <tt class="varname">PROJ4TEXT</tt>
+ transformation capabilities. The <code class="varname">PROJ4TEXT</code>
column contains the Proj4 coordinate definition string for a
particular SRID. For example:</p><pre class="programlisting">+proj=utm +zone=10 +ellps=clrk66 +datum=NAD27
+units=m</pre><p>For more information about, see the Proj4 web site at
<a href="http://www.remotesensing.org/proj" target="_top">http://www.remotesensing.org/proj</a>.
- The <tt class="filename">spatial_ref_sys.sql</tt> file contains both
- <tt class="varname">SRTEXT</tt> and <tt class="varname">PROJ4TEXT</tt>
- definitions for all EPSG projections.</p></dd></dl></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764652"></a>4.2.2. The GEOMETRY_COLUMNS Table</h3></div></div><div></div></div><p>The <tt class="varname">GEOMETRY_COLUMNS</tt> table definition is as
+ The <code class="filename">spatial_ref_sys.sql</code> file contains both
+ <code class="varname">SRTEXT</code> and <code class="varname">PROJ4TEXT</code>
+ definitions for all EPSG projections.</p></dd></dl></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2541056"></a>4.2.2. The GEOMETRY_COLUMNS Table</h3></div></div></div><p>The <code class="varname">GEOMETRY_COLUMNS</code> table definition is as
follows:</p><pre class="programlisting">CREATE TABLE GEOMETRY_COLUMNS ( F_TABLE_CATALOG
VARCHAR(256) NOT NULL, F_TABLE_SCHEMA VARCHAR(256) NOT NULL,
F_TABLE_NAME VARCHAR(256) NOT NULL, F_GEOMETRY_COLUMN VARCHAR(256) NOT
@@ -378,36 +374,36 @@
the geometry column. Note that the terms "catalog" and
"schema" are Oracle-ish. There is not PostgreSQL
analogue of "catalog" so that column is left blank --
- for "schema" the PostgreSQL schema name is used (<tt class="varname">public</tt>
+ for "schema" the PostgreSQL schema name is used (<code class="varname">public</code>
is the default).</p></dd><dt><span class="term">F_GEOMETRY_COLUMN</span></dt><dd><p>The name of the geometry column in the feature table.</p></dd><dt><span class="term">COORD_DIMENSION</span></dt><dd><p>The spatial dimension (2, 3 or 4 dimensional) of the
column.</p></dd><dt><span class="term">SRID</span></dt><dd><p>The ID of the spatial reference system used for the
coordinate geometry in this table. It is a foreign key reference
- to the <tt class="varname">SPATIAL_REF_SYS</tt>.</p></dd><dt><span class="term">TYPE</span></dt><dd><p>The type of the spatial object. To restrict the spatial
+ to the <code class="varname">SPATIAL_REF_SYS</code>.</p></dd><dt><span class="term">TYPE</span></dt><dd><p>The type of the spatial object. To restrict the spatial
column to a single type, use one of: POINT, LINESTRING, POLYGON,
MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION or
corresponding XYM versions POINTM, LINESTRINGM, POLYGONM,
MULTIPOINTM, MULTILINESTRINGM, MULTIPOLYGONM,
GEOMETRYCOLLECTIONM. For heterogeneous (mixed-type) collections,
you can use "GEOMETRY" as the type.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>This attribute is (probably) not part of the OpenGIS
- specification, but is required for ensuring type homogeneity.</p></div></dd></dl></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764797"></a>4.2.3. Creating a Spatial Table</h3></div></div><div></div></div><p>Creating a table with spatial data is done in two stages:</p><div class="itemizedlist"><ul type="disc"><li><p>Create a normal non-spatial table.</p><p>For example: <span><b class="command">CREATE TABLE ROADS_GEOM ( ID int4,
- NAME varchar(25) )</b></span></p></li><li><p>Add a spatial column to the table using the OpenGIS
+ specification, but is required for ensuring type homogeneity.</p></div></dd></dl></div></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2541202"></a>4.2.3. Creating a Spatial Table</h3></div></div></div><p>Creating a table with spatial data is done in two stages:</p><div class="itemizedlist"><ul type="disc"><li><p>Create a normal non-spatial table.</p><p>For example: <span><strong class="command">CREATE TABLE ROADS_GEOM ( ID int4,
+ NAME varchar(25) )</strong></span></p></li><li><p>Add a spatial column to the table using the OpenGIS
"AddGeometryColumn" function.</p><p>The syntax is: </p><pre class="programlisting">AddGeometryColumn(<schema_name>,
<table_name>, <column_name>, <srid>,
<type>, <dimension>)</pre><p> Or, using
current schema: </p><pre class="programlisting">AddGeometryColumn(<table_name>,
<column_name>, <srid>, <type>,
- <dimension>)</pre><p>Example1: <span><b class="command">SELECT
+ <dimension>)</pre><p>Example1: <span><strong class="command">SELECT
AddGeometryColumn('public', 'roads_geom',
- 'geom', 423, 'LINESTRING', 2)</b></span></p><p>Example2: <span><b class="command">SELECT AddGeometryColumn(
+ 'geom', 423, 'LINESTRING', 2)</strong></span></p><p>Example2: <span><strong class="command">SELECT AddGeometryColumn(
'roads_geom', 'geom', 423, 'LINESTRING',
- 2)</b></span></p></li></ul></div><p>Here is an example of SQL used to create a table and add a
+ 2)</strong></span></p></li></ul></div><p>Here is an example of SQL used to create a table and add a
spatial column (assuming that an SRID of 128 exists already):</p><pre class="programlisting">CREATE TABLE parks ( PARK_ID int4, PARK_NAME
varchar(128), PARK_DATE date, PARK_TYPE varchar(2) ); SELECT
AddGeometryColumn('parks', 'park_geom', 128,
'MULTIPOLYGON', 2 );</pre><p>Here is another example, using the generic "geometry"
type and the undefined SRID value of -1:</p><pre class="programlisting">CREATE TABLE roads ( ROAD_ID int4, ROAD_NAME
varchar(128) ); SELECT AddGeometryColumn( 'roads',
- 'roads_geom', -1, 'GEOMETRY', 3 );</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2764911"></a>4.2.4. Ensuring OpenGIS compliancy of geometries</h3></div></div><div></div></div><p>Most of the functions implemented by the GEOS library rely on
+ 'roads_geom', -1, 'GEOMETRY', 3 );</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2541316"></a>4.2.4. Ensuring OpenGIS compliancy of geometries</h3></div></div></div><p>Most of the functions implemented by the GEOS library rely on
the assumption that your geometries are valid as specified by the
OpenGIS Simple Feature Specification. To check validity of geometries
you can use the <a href="#IsValid">IsValid()</a> function:</p><pre class="programlisting">gisdb=# select isvalid('LINESTRING(0 0, 1
@@ -427,14 +423,14 @@
The <a href="#IsValid">IsValid()</a> function won't
consider higher dimensioned geometries invalid! Invocations of <a href="#AddGeometryColumn">AddGeometryColumn()</a> will add a
constraint checking geometry dimensions, so it is enough to specify
- 2 there.</p></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2765010"></a>4.3. Loading GIS Data</h2></div></div><div></div></div><p>Once you have created a spatial table, you are ready to upload GIS
+ 2 there.</p></div></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2541415"></a>4.3. Loading GIS Data</h2></div></div></div><p>Once you have created a spatial table, you are ready to upload GIS
data to the database. Currently, there are two ways to get data into a
PostGIS/PostgreSQL database: using formatted SQL statements or using the
- Shape file loader/dumper.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2765024"></a>4.3.1. Using SQL</h3></div></div><div></div></div><p>If you can convert your data to a text representation, then
+ Shape file loader/dumper.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2541428"></a>4.3.1. Using SQL</h3></div></div></div><p>If you can convert your data to a text representation, then
using formatted SQL might be the easiest way to get your data into
PostGIS. As with Oracle and other SQL databases, data can be bulk
loaded by piping a large text file full of SQL "INSERT"
- statements into the SQL terminal monitor.</p><p>A data upload file (<tt class="filename">roads.sql</tt> for example)
+ statements into the SQL terminal monitor.</p><p>A data upload file (<code class="filename">roads.sql</code> for example)
might look like this:</p><pre class="programlisting">BEGIN; INSERT INTO ROADS_GEOM (ID,GEOM,NAME ) VALUES
(1,GeomFromText('LINESTRING(191232 243118,191108
243242)',-1),'Jeff Rd'); INSERT INTO ROADS_GEOM
@@ -450,7 +446,7 @@
228134)',-1),'Phil Tce'); INSERT INTO ROADS_GEOM
(ID,GEOM,NAME ) VALUES (6,GeomFromText('LINESTRING(198231
263418,198213 268322)',-1),'Dave Cres'); COMMIT;</pre><p>The data file can be piped into PostgreSQL very easily using the
- "psql" SQL terminal monitor:</p><pre class="programlisting">psql -d [database] -f roads.sql</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2765088"></a>4.3.2. Using the Loader</h3></div></div><div></div></div><p>The <tt class="filename">shp2pgsql</tt> data loader converts ESRI
+ "psql" SQL terminal monitor:</p><pre class="programlisting">psql -d [database] -f roads.sql</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="id2541493"></a>4.3.2. Using the Loader</h3></div></div></div><p>The <code class="filename">shp2pgsql</code> data loader converts ESRI
Shape files into SQL suitable for insertion into a PostGIS/PostgreSQL
database. The loader has several operating modes distinguished by
command line flags:</p><div class="variablelist"><dl><dt><span class="term">-d</span></dt><dd><p>Drops the database table before creating a new table with
@@ -472,15 +468,15 @@
will drop M values from shapefiles.</p></dd><dt><span class="term">-W <encoding></span></dt><dd><p>Specify encoding of the input data (dbf file). When used,
all attributes of the dbf are converted from the specified
encoding to UTF8. The resulting SQL output will contain a
- <tt class="code">SET CLIENT_ENCODING to UTF8</tt> command, so that the
+ <code class="code">SET CLIENT_ENCODING to UTF8</code> command, so that the
backend will be able to reconvert from UTF8 to whatever encoding
the database is configured to use internally.</p></dd></dl></div><p>Note that -a, -c, -d and -p are mutually exclusive.</p><p>An example session using the loader to create an input file and
uploading it might look like this:</p><pre class="programlisting"># shp2pgsql shaperoads myschema.roadstable >
roads.sql # psql -d roadsdb -f roads.sql</pre><p>A conversion and upload can be done all in one step using UNIX
pipes:</p><pre class="programlisting"># shp2pgsql shaperoads myschema.roadstable | psql -d
- roadsdb</pre></div></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="id2765328"></a>4.4. Retrieving GIS Data</h2></div></div><div></div></div><p>Data can be extracted from the database using either SQL or the
+ roadsdb</pre></div></div><div class="sect1" lang="e