Welcome to the Users PostGIS Wiki!

Everybody is welcome to contribute to this wiki.

• You must login to create new pages and modify existing ones. You can create your own ​OSGEO Userid or use your existing one or you can use the generic one (login:wiki;password:wiki).
• Please prefix every new page names in this section with "UsersWiki" in order to easily distinguish them from other Wiki Trac pages.
• For a quick overview of how to format page's content see this page.

What is PostGIS?

PostGIS is an extension to the ​PostgreSQL object-relational database system that allows ​GIS (Geographic Information System) objects to be stored in the database. PostGIS does for PostgreSQL what Oracle Spatial does for Oracle, ArcSDE does for Microsoft SQL Server/Oracle.

• ​(slides) FOSS4G NA 2012 What's New in PostGIS 2.0 Paul Ramsey
• ​(slides) FOSS4G 2011 State of PostGIS ​video Paul Ramsey
• ​FOSS4G 2011 PostGIS 2.0 the new stuff (slides)​video Regina Obe and Leo Hsu
• ​FOSS4G 2011 Store, manipulate and analyze raster data within the PostgreSQL/PostGIS spatial database video Pierre Racine
• ​NCGIS 2011 PostGIS 2.0 3D and Raster support enhancements Regina Obe and Leo Hsu
• ​Foss4G JP 2010 State of PostGIS video by Paul Ramsey
• ​PostGIS meets the 3rd Dimension by Olivier Courtin
• ​Foss4G 2009 State of PostGIS video by Paul Ramsey
• ​PostGIS 1.5 and Beyond PGDay EU 2010 talk by Mark Cave-Ayland and Olivier Coutin outlines the techical details of upcoming PostGIS 2.0 -- the new disc format, new geometry types etc.
• ​PostGIS 1.5 and Beyond PGDay EU 2010 talk (more of a user perspective) by Vincent Picavet and Olivier Courtin

Overview

• PostgreSQL/PostGIS version support matrix
• PostGIS Tutorials
• ​PostGIS Manuals
• Features of PostGIS
• Coverage Concepts and PostGIS
• PostgreSQL/PostGIS GEOS GDAL /PostGIS version support matrix
• ​PostGIS as a part of PostgreSQL (Article)]
• Tools that support PostGIS
• Gallery Collection of applications using PostGIS in the backend.
• PostgreSQL Case Studies
• PostGIS Hosters
• PostGIS Support and Consulting Services
• ​PostGIS Wikipedia entry
• PostGIS and ESRI ArcSDE/ArcGIS Articles
• ​PostgreSQL general use user contributed functions and snippets

Contrast / Compare / Port Guides

Don't know if PostGIS is right for you or if another spatial db is more suited or you are porting from another/going to another and not sure the gotchas

• ​NCGIS 2011: Cross Comparison of Spatially Enabled Databases: PostGIS, SQL Server and JASPA
• ​Compare SQL Server 2008 R2, Oracle 11G R2, PostgreSQL/PostGIS 1.5 Spatial Features
• ​SQL Server 2008, PostGIS, MySQL function/feature Spatial compare
• ​Simon Greener's FOSS4G 2009 talk on Oracle Spatial/Locator and gotchas for porting between PostGIS/Oracle - ​complete with slides and sound

Compilation and Installation Guides

• PostGIS Install Guide for different systems
• Directions for Compiling in Windows
• Issues with Installing on Windows Vista Windows Vista Gotchas.

PostGIS Books

• ​PostGIS official manuals for all versions and for PostGIS JDBC driver.
• ​PostGIS in Action (2nd Edition) by Regina Obe and Leo Hsu - Covers PostGIS 2.1. Feature complete and available from Manning in draft form. With early purchase you get draft updates and final (e-Book/Hardcover) when complete. E-book is generally 1/2 price of full. All code and data downloads available for free on the book site even if you don't buy the book. ​Chapter 1 (a primer on PostGIS ) are free downloads.
• ​PostGIS cookbook Covers PostGIS 2.0 and 2.1. Common real-world recipes for getting things done in PostGIS.
• ​PostGIS 2 by Jose Carlos Martinez spanish book covering new features of PostGIS 2. ​Table of Contents
• ​PostGIS in Action (1st Edition) by Regina Obe and Leo Hsu - It's out and available via various distributors including Manning Publications and Amazon. If you want just the E-Book, purchase directly from Manning. The E-book is about 50% off the hard-copy price. With the purchase of Hard-copy, there is a free-coupon to get the E-Book version. Book includes PostGIS 1.3-1.5, PostgreSQL 8.3-9.0 (with upcoming highlights of PostgreSQL 9.1 and PostGIS 2.0). All code and data downloads available for free on the book site even if you don't buy the book. ​Chapter 1 (a primer on PostGIS ) and ​Appendix C (a primer on SQL) are free downloads.

Examples of Spatial SQL

Finding Features

• Find Nearby Features
• Find Nearby Features in Lat/Lon (EPSG:4326)
• Find Features far from nearest neighbor - Anti-Near Neighbor
• Find Features Inside a Particular Polygon
• Find points on interior rings within a certain distance to exterior ring

Overlay

• Join Spatial and Non-Spatial Tables
• Overlay Geometries in one table with Geometries in another

Cleaning Geometry

• Clean Invalid Polygon Topology
• Determine spikes in polygons
• Search for spikes and removes them
• Function Find overlapping polygons in a table and remove slivers/overlaps
• Test all tables from GeometryColumns for invalid geometries

Splitting Geometry

• Split a Polygon with a Linestring
• Split a table of Linestrings by a table of Points onto or close to the lines

Simplifying

• Simplify a multipolygon layer, keeping existing connections between polygons (Not using topology extension)
• Simplify a multipolygon layer, using PostGIS Topology
• Dilation/erosion technique for simplifying coastlines

Creating Polygons

• Polygonize / building areas from non-noded linework

Generating Shapes

• Create a fishnet grid (rectangular cells)
• Generating a hexagonal grid
• MultiPoint centroids from Multi geometries
• Functions for creating random points inside a polygon
• Function used for creating dot density maps

Direction

• Determine cardinal direction from azimuth direction

Linear Referencing

• Geocode a point along a line with a left/right offset

Topology

• Shell script showing basic construction of polygons from linestrings using topology
• Schema explaining postgis topology data model
• Create a network topology from a set of noded linestrings

Tools

• Examples OGR Examples
• Batch Load Shapefiles with shp2pgsql.exe on windows
• Set of functions for spatial overlay of 2 polygon tables

Extending PostGIS

• PostGIS EJB3 Integration
• Postgis Topology - to be moved somewhere else ;)
• Raster support
  • WKT Raster - The new raster implementation in PostGIS
  • ​PGCHIP - The GDAL Postgis driver for raster data - a corrected version can be found in current GDAL CVS
• WishList of features for the future.
• Code Sprint 2009 Notes
• SpatiaLite export from PostGIS
• pgRouting routing with PostGIS

Newbie Common Problems and Questions

• function addgeometrycolumn("unknown", "unknown", integer, "unknown", integer) does not exist
• What is the measurement returned by ST_Distance?
• What unit of measurement do PostGIS functions use?
• How to get distance measurement in meters?

Extras

• Netbeans SQL Editor with (PostGIS) functions autocompletion and documentation
• Using QGis to display PostGis Layers

Notes

• Spatial Algorithm Web Sites

Useful Additional PL/PGSQL Functions

• PL/PGSQL Functions
• Functions that work specifically for the geography type
• WKT Raster Helper Functions
• Neighbor Search Nearest Neighbor Search
• PL/PGSQL Pseudo Cascade Union Aggregate Function (use for pre-PostGIS 1.4)

What is GEOS

​GEOS is a C++ port of the ​JTS Topology Suite. It is used by PostGIS to implement Topological functions.