PostGIS

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS “spatially enables” the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI’s SDE or Oracle’s Spatial extension. PostGIS follows the OpenGIS “Simple Features Specification for SQL”.

1. Installation on Ubuntu

download and install the following package using Synaptic Package Manager:

1.1. postgis

geographic objects support for PostgreSQL — common files. This package contains the PostGIS userland binaries, common files and documentation.

1.2. postgressql-8.4-postgis

geographic objects support for PostgreSQL 8.4. This package supports PostgreSQL 8.4.

1.3. libpostgis-java

geographic objects support for PostgreSQL — JDBC support. This package contains JDBC support for PostGIS.

2. What Installed

The following packages were installed:

libgeos-3.2.0 (3.2.0-1)
libgeos-c1 (3.2.0-1)
libpostgis-java (1.5.1-5)
libproj0 (4.7.0-1)
postgis (1.5.1-5)
postgresql-8.4-postgis (1.5.1-5)
proj-data (4.7.0-1)

Where?

sudo find / -name 'postgis.sql' -print
/usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
~$ cd test
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
/usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
/usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
~/test$ ls /usr/share/postgresql/8.4/contrib/postgis_comments.sql
/usr/share/postgresql/8.4/contrib/postgis_comments.sql
sudo find / -name 'postgis.jar' -print
/usr/share/java/postgis.jar

3. Setup your database

3.1. create a simple PostgreSQL database

createdb mydb

3.2. enable the PL/pgSQL language in the database

createlang plpgsql mydb

3.3. load the PostGIS object and function definitions into the database

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql

3.4. populate a complete set of EPSG coordinate system definition identifiers into spatial_ref_sys table

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

3.5. add comments to the PostGIS functions

psql -d mydb -f /usr/share/postgresql/8.4/contrib/postgis_comments.sql

3.6. check

~/test$ psql mydb
psql (8.4.10)
Type "help" for help.
mydb=#SELECT PostGIS_Full_Version();
postgis_full_version
-------------------------------------------------------------------------------------------------------
POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS
(1 row)

4. Create a geographic table

4.1. create a table

CREATE TABLE global_points (

id SERIAL PRIMARY KEY,

name VARCHAR(64),

location GEOGRAPHY(POINT,4326)

);

mydb=# CREATE TABLE global_points (
mydb(# id SERIAL PRIMARY KEY,
mydb(# name VARCHAR(64),
mydb(# location GEOGRAPHY(POINT,4326)
mydb(# );
NOTICE: CREATE TABLE will create implicit sequence "global_points_id_seq" for serial column "global_points.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "global_points_pkey" for table "global_points"
CREATE TABLE

4.2. check  GEOGRAPHY_COLUMNS table

SELECT * FROM geography_columns;
f_table_catalog | f_table_schema | f_table_name | f_geography_column | coord_dimension | srid | type
-----------------+----------------+---------------+--------------------+-----------------+------+-------
mydb | public | global_points | location | 2 | 4326 | Point
(1 row)

4.3.  Add some data into the test table

mydb=# INSERT INTO global_points (name, location) VALUES ('Town', ST_GeographyFromText('SRID=4326;POINT(-110 30)') );
INSERT 0 1
mydb=# INSERT INTO global_points (name, location) VALUES ('Forest', ST_GeographyFromText('SRID=4326;POINT(-109 29)') );
INSERT 0 1
mydb=# INSERT INTO global_points (name, location) VALUES ('London', ST_GeographyFromText('SRID=4326;POINT(0 49)') );
INSERT 0 1

4.4. Create an index

mydb=# CREATE INDEX global_points_gix ON global_points USING GIST ( location );

4.5. Query Geo-Data

-- Show a distance query and note, London is outside the 1000km tolerance
SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);
-- Distance calculation using GEOGRAPHY (122.2km)
SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);
mydb=# SELECT name FROM global_points WHERE ST_DWithin(location, ST_GeographyFromText('SRID=4326;POINT(-110 29)'), 1000000);
name
--------
Town
Forest
(2 rows)
mydb=# SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geography, 'POINT(-21.96 64.15)':: geography);
st_distance
-----------------
122235.23814596
(1 row)
mydb=# SELECT ST_Distance('LINESTRING(-122.33 47.606, 0.0 51.5)'::geometry, 'POINT(-21.96 64.15)':: geometry);
st_distance
------------------
13.3422712214536
(1 row)

5. Explore More…

PostGIS 1.5.3. Manual is always the starting point.

GIS_for_Web_Developers is a must-read book..