PostGIS

Here is an excellent guide to working with PostGIS. Some of my additional notes:

Load Shapefile to PostGIS

a) DB Connect:
psql -d gisdb -h localhost -U matt

b) Generate sql file from shapefile:

C:\Program files\PostgreSQL\8.2\bin>shp2pgsql.exe data\states > states.sql

c) Use pgAdmin to ru sql (tools -> query tool. In query tool open->go to the states.sql

Paul Ramsey wrote PostGIS. I recently shared some emails with him. Thought part of the discussion worth reprinting:

Q. What is the difference between PostGIS and ArcSDE?

A. At a very high level (or perhaps this is a very low level), PostGIS /
PostgreSQL is a database, and ArcSDE is not. ArcSDE is a "spatial
middleware", it runs as a separate process and talks to the real
database below it. PostGIS is a "dynamically loadable runtime" inside
the PostgreSQL process -- it runs inside the same address space as
PostgreSQL, from the point of view of the computer, it's the all the
same program.

PostGIS should be viewed in exactly the same way relative to
PostgreSQL as Oracle Spatial should vis-a-vis Oracle, SQL Server
Spatial vis-a-vis SQL Server, Informix Spatial Extender vis-a-vis
Informix, etc, etc, etc. It works exactly the same way as all those
other in-process spatial type extensions to relational databases. So,
call it an "extension", a "spatial type", a "data blade", etc.
Unfortunately, years of marketing by various vendors have muddied the
terminological water surrounding type extension mechanisms.

The most important functional differences with SDE have nothing to do
with spatial database functionality and everything to do with ESRI
product placement and marketing. You'll find that ESRI products talk
more directly to ArcSDE than they do to PostGIS (or Oracle Spatial or
SQL Server Spatial, for that matter) because ESRI is interested in
selling their whole stack (desktop + server + web apps) to their
clients, not just the ArcGIS component. It's a non-trivial benefit for a customer that (a) already has a lot of ESRI software and (b) can afford the maintenance overhead in dollars, time and expertise that ArcSDE extracts.