• Home
• Our Products
• Our Services
• Company
• Contact

Home > Products > PostGIS > PostGIS Technical Overview

PostGIS Technical Overview

Theory of Operation

PostGIS adds support for spatial types to the PostgreSQL open source database. But what does it mean to "support" spatial? PostgreSQL "supports" non-spatial types like strings, by providing a string type, indexes that work on strings, and functions to manipulate and test string data. PostGIS adds a spatial "geometry" type to PostgreSQL, an R-Tree index suitable for spatial indexing, and functions that manipulate and test geometry objects.

Once spatial support is added to a database, the same powerful features that are standard for non-spatial types because available for spatial data as well:

• Transactional integrity and conflict avoidance
• Table joins using spatial predicates instead of non-spatial keys
• Hot backups, replication, and clustering
• High speed random access to large data sets
• Query formulation using SQL

Architectural Implications

Once data is managed in a spatial database, access becomes much more uniform: only a SQL query is required to extract information. Combining SQL access with simple web services allows data managers to expose spatial queries through extremely simple scripts using the power of the SQL engine in the database.

For example, here is a PHP script that does a radius query of parcels, given a UTM coordinate as input (http://example.com/parcels.php?easting=100000&northing=5000000&radius=500). Note the very small amount of code necessary to complete the operation:

<?php
#
# Retrieve the URL parameters
#
$northing = $_GET["northing"];
$easting = $_GET["easting"];
$radius = $_GET["radius"];
#
# Construct the SQL query
#
$db_sql = 
 "SELECT parcel_id FROM parcels
  WHERE ST_DWithin(
          parcel_geom, 
          ST_MakePoint($easting,$northing,26910), 
          $radius
        )";
#
# Execute the SQL query
#
$db_connection = pg_connect("db=spatialdb");
$db_result = pg_query($db_connection, $db_sql);
#
# Print out the list of parcel IDs as a text file
#
header("Content-type: text/plain");
while( $db_row = pg_fetch_row( $db_result ) ) {
  print $db_row[0] . "\n";
}
# All done
?>

No GIS Required

The functionality provided by the SQL operations included in PostGIS and the basic SQL engine in PostgreSQL is sufficient to replace 90% of the operations that are customarily associated with "GIS" in operational environments.

What parcels are within the 'COMMERCIAL' zoning category?

SELECT parcels.* FROM parcels, zones WHERE ST_Contains(zones.geom, parcels.geom)

How many roads are within 10km of the river?

SELECT Sum(ST_Length(roads.geom)) FROM roads, rivers WHERE ST_DWithin(roads.geom, rivers.geom, 10000)

Who are Bob Smartly's neighbors?

SELECT p2.owner FROM parcels p1, parcels p2 WHERE ST_DWithin(p1.geom,g2.geom,10) AND p1.owner = 'Bob Smartly'