Trying out PostGIS
This is my experience with loading PostGIS into a 7.4 database.
PostGIS is a collection of tools--data types, functions, operators and
indexes that enable you to store geographic information in the database.
This collection is different from the geometry types in that it is
geared more toward the geographic requirements. But it is not that different
in that the geometry types also provide functions, operators and indexes
for spatial data types. If you are drawing maps, probably you want PostGIS.
If you are simply describing shapes and lines and points geometrically
you probably want the geometric types. Simple maps, points and paths can be
stored with the geometric types, but you have to handle all of the
coordinate system conversions yourself.
Paul Ramsey provides an excellent set of slides from his OSCON talk
Build your own MapQuest now available on
The PostGIS Website is at
You must build PostGIS for each installation that you plan to use it with
and then load the definitions into each database that you want
PostGIS types to be used. PostGIS itself is fairly version independent
and will work well with PostgreSQL 7.1 up to the current beta 8.0.
I run multiple versions of PostgreSQL
and so build PostgreSQL from source. That is
what I will describe for PostGIS here. But there are RPMs and
windows precompiled libraries (for cygwin) are available as well.
1. Unload the downloaded tar file into the contrib directory of the
Here I ran into some problems. It was looking for header files
that did not exist on my system. Eventually the problem was traced
to the fact that it expected that I had previously installed PROJ4
and GEOS on my system. This leads to the obvious question, what
are these and why would I want them. The answer was provided
by Mark Cave-Ayland.
PROJ 4 is a reprojection library which allows you to convert from one
coordinate system to another. Because the earth is a sphere, it's almost
like an orange: you can peel the skin and flatten it in many different
ways. Depending on how you peel the skin will affect the accuracy of
For example, most countries have their own sets of coordinate systems
which are typically devised to either minimize the error when measuring
area, or reduce the distortion at the very limits of a country. Since
data can be supplied in one of many coordinate systems, PROJ 4 enables
you to translate data 'on the fly' from one coordinate system to
As an example, TIGER data is collected in [lat/lon] coordinate systems such as
NAD83 and NAD27. But what if you want to reference the data on a world
map which is in [a different] latitude and longitude? [For example, polar
or Cartesian coordinate systems.] Using the PostGIS transform()
function, you can convert the data into latitude and longitude on the
fly, instead of being limited to referencing your data against maps that
are also based on NAD83 and NAD87 [lat/lon coordinate systems].
In GIS, there are a number of complex spatial analysis routines based on
a set of rigid mathematical definitions. These analysis routines were
first written as part of JUMP called the Java Topological Suite, or JTS
for short. GEOS is a C++ port of JTS.
GEOS allows you to do things like calculate the mathematical 'union' and
'difference' of overlapping shapes which is difficult to describe in
words! A good place to look would be the following page on JTS:
The other important things GEOS allows you to do is do exact tests of
spatial relationships, like Touches(), Contains(), Intersects(),
Overlaps(), etc. These are all boolean tests of how spatial features
If you do not plan to use GEOS or PROJ4, you must UNDEF USE_GEOS and USE_PROJ.
Don't simply set them from 1 to 0 because they are used as IFDEFs in the C code.
Those IFDEFs check to see whether the variable is defined, ignoring the actual value.
The Makefile is also where you will need to set the path to
your PostgreSQL source tree by setting the environment variable
Now we are ready to go back to PostGIS:
3. sudo make install
So far so good. Then I created a database pggis and loaded the
definitions of the postgis types, functions, indexes and operators
4. psql pggis < $PGSQL_SRC/contrib/postgis-0.8.2/postgis.sql
If you get an error regarding plpgsql, be sure that the
database has the plpgsql language installed (createlang 'plpgsql' dbname).
At this point we have the capacity to create tables that use the
Having successfully created PostGIS without Proj4 and GEOS, we wanted
to ensure that all went smoothly if we did want to use these features.
Building with GEOS and Proj4 (Again)
Proj4 is available for download from
GEOS is available for download from
The source was downloaded for both projects and
the ./configure scripts run with the
prefix for my installation of PostgreSQL 7.4.
1. ./configure --prefix=/local/pgsql74
Both builds and installs went very smoothly.
3. sudo make INSTALL
After installing Proj4 and GEOS you must change
the PostGIS Makefile to enable these projects
by setting the variables:
Ignoring all of the hints regarding LD_LIBRARY_PATH,
I reran make clean; make; make install in the PostGIS
source tree and had no errors.
I recreated my target database and reloaded it
with the PostGIS definitions:
psql pggis < postgis.sql
psql pggis < spatial_ref_sys.sql
and after I remembered to add 'plpgsql' as a language
it also ran with no errors.
And now we are ready to actually begin to use the PostGIS
capabilities. This article will be continued in the next issue
elein at varlena.com,
Mark Cave-Ayland m.cave-ayland at webbased.co.uk,
Paul Ramsey pramsey at refractions.net
PostgreSQL 8.0 Beta Release
Major Features and Incompatibilities
latest release notes are always available in the
Developer's Version of the Docs under Appendix E Release Notes.
Release Notes are available, but note they are a work in progress.
However, it is a very well done work in progress and so I'm including
the major features and incompatibilities here, with minor edits,
for your information and planning. Do not forego planning for 8.0!
Note also that this list is not complete. See the detailed release
notes for information about Your Favorite New Feature if you do not
see it listed here.
- Win32 Native Server
This is the first PostgreSQL release to natively run on Microsoft Windows as a server.
It can run as a Windows service. This release supports NT-based Windows releases like Win2000,
XP, Win2003. Older releases like Windows 95, 98, and ME are not supported because
these operating systems do not have the infrastructure to support PostgreSQL.
A separate windows installer project,
has been created to ease installation on Windows. This is not part of the core release
and must be accessed separately.
Previous releases required the Unix emulation toolkit Cygwin for Win32 server support. PostgreSQL has always supported clients on Win32.
- Savepoints Improve Transaction Control
Savepoints allow specific parts of a transaction to be aborted without affecting the remainder
of a given transaction. Prior releases had no such capability; there was no way to recover
from a statement failure within a transaction except by aborting the whole transaction.
This feature is valuable for application writers who require error recovery within a complex
transaction or data loads.
- Point-In-Time Recovery Increases Reliability
Though PostgreSQL is very reliable, in previous releases there was no way to recover
from disk drive failure except to restore from a previous backup or use a standby replication
server. Point-in-time recovery allows continuous backup of the server. You can recover either to
the point of failure or to some transaction in the past.
- Tablespaces Simplify Disk Layout
Tablespaces allow administrators to select the file systems used for storage of databases,
schemas, tables, or indexes. This improves performance and control over disk space usage.
Prior releases used initlocation and manual symlink management for such tasks.
- Improved Buffer Management, CHECKPOINT, VACUUM
This release has a more intelligent buffer replacement strategy, which will make better
use of available shared buffers and improve performance. The performance impact of vacuum
and checkpoints is also lessened.
- Change Column Types
A column's data type can now be changed with ALTER TABLE.
Previously, in order to change a column type, you must create a new column
in the table with ALTER TABLE, copy the old column values to the new column
and then ALTER TABLE again to drop the old column and again to rename the
new column to the old name. This simplifies that procedure.
- New Perl Server-Side Language
A new version of the plperl server-side language now supports a persistent shared
storage area, triggers, returning records and arrays of records, and
SPI calls to access the database.
- COPY Handles Comma-Separated-Value Files
COPY can now read and write comma-separated-value (CSV) files.
It has the flexibility to interpret non-standard quoting and separation characters too.
COPY has always been able to produce and read comma separated values with
the DELIMITER option. Now, however, it is easier with the new flag CSV option
instead of the DELIMITER ',' option. The added capabilities of quoting fields also
simplifies some of the problems one might encounter with commas within text fields.
A dump/restore using pg_dump is required for those wishing to migrate data from any previous release.
Many of these changes involve postgresql.conf. Plan on reviewing your existing postgresql.conf
when you convert to 8.0.
- postgresql.conf: Server configuration parameters virtual_host
and tcpip_socket have been replaced with a more general parameter listen_addresses.
Also, the server now listens on localhost by default, which eliminates the need for
the -i postmaster switch in many scenarios.
- postgresql.conf: Server configuration parameters SortMem and VacuumMem
have been renamed to work_mem and maintenance_work_mem
to better reflect their use. The original names are still supported in SET and SHOW.
- postgresql.conf: Server configuration parameters
log_pid, log_timestamp, and log_source_port have been removed now that
a more flexible log_line_prefix has been added.
- postgresql.conf: Server configuration parameter syslog
has been removed and replaced with a more logical log_destination
variable to control the log output destination.
- postgresql.conf: Server configuration parameter log_statement
has been changed so it can selectively log just database modification or data definition statements.
- postgresql.conf: Server configuration parameter max_expr_depth
parameter has been replaced with max_stack_depth which measures the physical
stack size rather than the expression nesting depth. This helps prevent session
termination due to stack overflow caused by recursive functions. If you have previously
set this value, it should change to reflect the new measurement of stack size rather than
- CHAR(n) and length(): The length() function no longer counts trailing
spaces in CHAR(n) values. If you use char(n) columns
you are probably aware of changes regarding trailing spaces. Examine your SQL
to see if you are relying on the length() function to count spaces. This is no longer valid.
- CAST BIT(n): Casting an integer to bit(n)
selects the rightmost N bits of the integer, not the leftmost N bits as before.
If you use the bit(n) types, examine any cases where you previously cast the value
for discrepancies that may be caused by this change.
- Arrays: UPDATE-ing an element or slice of a NULL array value now produces a
non-NULL array result, namely an array containing just the assigned-to positions.
- Arrays: Syntax checking of array input processing has been tightened up considerably. Junk that was previously allowed in odd places with odd results now causes an ERROR. Also changed behavior with respect to whitespace surrounding array elements; trailing whitespace is now ignored as well as leading whitespace (which has always been ignored).
- Strings, Floats: The server now warns of empty strings passed to
oid/float4/float8 data types. In the next major release, doing this will generate an error.
- Date Extraction: The extract() function (also called date_part) now returns the
proper year for BC dates. It previously returned one less than the current year.
The function now also returns the proper values for millennium and century.
- pgsql \copy: psql's \copy command now reads or writes the query stdin/stdout, rather than psql's stdin/stdout. The previous behavior can be accessed via new pstdin/pstdout parameters.
- JDBC, Tcl The JDBC client interface has been removed from the core distribution,
and is now hosted at http://jdbc.postgresql.org.
The TCL client interface has also been removed. There are several TCL interfaces now hosted
at GBORG: http://gborg.postgresql.org.
- Timezones The server now uses its own time zone database, rather than the one
supplied by the operating system. This will provide consistent behavior across all platforms.
In most cases, there should be little noticeable difference in time zone behavior, except
that the time zone names used by SET/SHOW TimeZone may be different from what your
platform provides. If you relied on your operating system names of your timezones,
check in the postgresql.conf to be sure you are using the correct one.
- EXECUTE: EXECUTE now returns a completion tag that matches the executed statement.
- configure: configure's threading option no longer requires users to run tests
or edit configuration files; threading options are now detected automatically.
Now that tablespaces have been implemented, initlocation has been removed.
- toc_char(interval): The 8.1 release will remove the function to_char(interval)
Bruce Momjian pgman at candle.pha.pa.us