Home Articles Free, cheap, and powerful: PostGIS, the Open Source geodatabase

Free, cheap, and powerful: PostGIS, the Open Source geodatabase

Paolo Cavallini
Faunalia, Piazza Garibaldi 5 – Pontedera (PI), Italy
[email protected]

Emilia Venturato
Faunalia, Piazza Garibaldi 5 – Pontedera (PI), Italy
[email protected]

Maurizio Napolitano
ITC-irst, Divisione SRA,
Via Sommarive 18 – Povo (TN) Italy

The geographic database, or geodatabase for short, is an essential component of large and complex GIS environments, and especially for Spatial Data Infrastructure. Several proprietary applications exist, but are generally complex and very expensive. In the Open Source/Free Software arena, the solution by far more powerful and reliable is PostGIS ( https://www.postgis.org), a geographic extension of the rock-steady relational, object oriented, database management system PostgreSQL. PostGIS follows OGC standards, and assures the interoperability between GIS analyses, desktop applications, and web applications in an unified framework. It allows a wide variety of users to access large data sets (both geographic and alphanumeric), and the management of their relations, guaranteeing their integrity. It has full 3D support (i.e. volume, as opposed to 2.5D, i.e. draping). It is easy to install on a variety of platforms. It can be managed through many different interfaces, both graphical and character-based, both alphanumeric and geographic.

Fig. 1


GIS is by definition based on geographic data structures. Desktop GIS applications have habituated users to use only one tool for the visualization of the data as well as for their filing. In the case of vector data, the most widely used format is ESRI shapefile, in which three files are used for every vector assemblages: one for geometries, one for attributes, and one for indexing the relation between the two. This schema can be useful for single users, or small groups, but for larger structures, data replication and security issues require a more robust infrastructure, such as a RDMBS (Relational Data Base Management System). This system allow a multiuser access to large data sets, guaranteeing security and integrity. It is also an ideal component for web mapping applications, especially when data are dynamic, and should be updated, either by hand or through automatic systems. The standard language to interact with a DBMS is the Structured Query Language (SQL), a standard, easy to learn language that allows to select, insert, modify, calculate, apply functions etc. on a data base represented by a number of related tables. A wide literature is available to learn SQL (Google or other web crawlers are your friends here). The architecture of a geodatabase differs from a standard RDBMS not only because it can handle geographic (i.e., geometry+projections) data, but also for a larger set of commands that extend standard SQL language (e.g. distance calculations, buffers, overlay, conversion between coordinate systems, etc.). Open Geospatial Consortium https://www.opengeospatial.org; (OGC for short) has released a series of standards for geographic data formats https://www.opengeospatial.org/docs/99-049.pdf, which define:

  1. which tables must be present in a geodatabase (geometry columns table and spatial reference system table);
  2. data formats, called Simple Features (point, line, polygon, etc.), in one of two different formats (Fig. 1)
  3. Well Know Text (WKT): easier to read for humans, but disk-space wasting
  4. Well Know Binary (WKB): unreadable for humans, but faster and more compact for a computer;
  5. a set of SQL-like instructions for geographic analysis.

Fig. 2
The Well Known Text format is human-readable: you can see the Spatial Reference ID (SRID: a number which identifies unambiguously the projection of every single record), the type of geometry and the sequence of vertices as x,y coordinates. The Well Known Binary is more compact, but unreadable by humans.

Fig. 3
A sample from the 2671 standard projections (SRIDs) available in a PostGIS geodatabase. Any additional custom projection can be defined here.

Page 1 of 3