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 Tidbits

Download

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.

Build

1. Unload the downloaded tar file into the contrib directory of the PostgreSQL source.
2. make

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 your data.

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 another.

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: http://www.jump-project.org/project.php?PID=JTS&SID=OVER.

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 interact.

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 PGSQL_SRC=/local/src/postgresql-74.

Build Again

2. make
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 using:

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 PostGIS datatypes. 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)

1. ./configure --prefix=/local/pgsql74
2. make
3. sudo make INSTALL

After installing Proj4 and GEOS you must change the PostGIS Makefile to enable these projects by setting the variables:

USE_GEOS=1;
GEOS_DIR=/local/pgsql74
USE_PROJ4=1;
PROJ4_DIR=/local/pgsql74

	psql pggis < postgis.sql
	psql pggis < spatial_ref_sys.sql

And now we are ready to actually begin to use the PostGIS capabilities. This article will be continued in the next issue of GeneralBits.

Contributors: 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	22-Aug-2004

The latest release notes are always available in the Developer's Version of the Docs under Appendix E Release Notes. The 8.0 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.

Major Features

• 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, pginstaller, 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.

Incompatibilities

• 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 expression depth.

• 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.

• initlocation Now that tablespaces have been implemented, initlocation has been removed.

• toc_char(interval): The 8.1 release will remove the function to_char(interval)

Contributors: Bruce Momjian pgman at candle.pha.pa.us