Let's take a look at what is required to use PostGIS on your computer:

% pgsql/bin/psql -d <dbname>
   -f pgsql/share/contrib/postgis-1.5/spatial_ref_sys.sql

Once you have installed the various pieces of software, and have set up a spatially-enabled database, you can use the Psycopg database adapter in much the same way as you would use MySQLdb to access a MySQL database:

import psycopg2

connection = psycopg2.connect("dbname=... user=...")
cursor = connection.cursor()
cursor.execute("SELECT id,name FROM cities WHERE pop>100000")
for row in cursor:
  print row[0],row[1]

Because Psycopg conforms to Python's DB-API specification, using PostgreSQL from Python is relatively straightforward, especially if you have used databases from Python before.

Here is how you might create a new spatially-enabled table using PostGIS:

import psycopg2

connection = psycopg2.connect("dbname=... user=...")
cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS cities")
cursor.execute("CREATE TABLE cities (id INTEGER," +
               "name VARCHAR(255), PRIMARY KEY (id))")
cursor.execute("SELECT AddGeometryColumn('cities', 'geom', " +
               "-1, 'POLYGON', 2)")
cursor.execute("CREATE INDEX cityIndex ON cities " +
               "USING GIST (geom)")
connection.commit()

Let's take a look at each of these steps in more detail. We first get a cursor object to access the database, and then create the non-spatial parts of our table using standard SQL statements:

connection = psycopg2.connect("dbname=... user=...")
cursor = connection.cursor()

cursor.execute("DROP TABLE IF EXISTS cities")
cursor.execute("CREATE TABLE cities (id INTEGER," +
               "name VARCHAR(255), PRIMARY KEY (id))")

Once the table itself has been created, we have to use a separate PostGIS function called AddGeometryColumn() to define the spatial columns within our table:

cursor.execute("SELECT AddGeometryColumn('cities', 'geom', " +
               "-1, 'POLYGON', 2)")

Finally, we create a spatial index so that we can efficiently search using the new geometry column:

cursor.execute("CREATE INDEX cityIndex ON cities " +
               "USING GIST (geom)")

Once you have created your database, you can insert geometry features into it using the ST_GeomFromText() function, like this:

cursor.execute("INSERT INTO cities (name,geom) VALUES " +
               "(%s, ST_GeomFromText(%s)", (cityName, wkt))

Conversely, you can retrieve a geometry from the database in WKT format using the ST_AsText() function:

cursor.execute("select name,ST_AsText(geom) FROM cities")
for name,wkt in cursor:
    ...

Because PostGIS is an extension to PostgreSQL, and you use Psycopg to access it, there are three separate sets of documentation you will need to refer to:

Of these, the PostGIS manual is probably going to be the most useful, and you will also need to refer to the Psycopg documentation to find out the details of using PostGIS from Python. You will probably also need to refer to the PostgreSQL manual to learn the non-spatial aspects of using PostGIS, though be aware that this manual is huge and extremely complex, reflecting the complexity of PostgreSQL itself.

PostGIS supports the following features not available with MySQL:

PostGIS has a reputation for being a geo-spatial powerhouse. While it is not the only option for storing geo-spatial data (and is certainly the most complex database discussed in this book), it is worth considering if you are looking for a powerful spatially-enabled database to use from within your Python geo-spatial programs.

Before you can use SpatiaLite in your Python programs, you need to install SQLite, SpatiaLite, and pysqlite. How you do this depends on which operating system your computer is running:

After installing the libspatialite library and its dependencies, you now need to make sure you have a workable version of pysqlite, the Python database adapter for SQLite.

A version of pysqlite comes bundled with Python version 2.5 and later, in the form of a standard library module named sqlite3. This standard library module, however, may not work with SpatiaLite. Because SpatiaLite is an extension to SQLite, the pysqlite library must be able to load extensions—a feature that was only introduced in pysqlite version 2.5, and is often disabled by default. To see if your version of Python includes a usable version of sqlite3, type the following into the Python command line:

>>> import sqlite3
>>> conn = sqlite3.connect(":memory:")
>>> conn.enable_load_extension(True)

If you get an AttributeError, your built-in version of sqlite3 does not support loading extensions, and you will have to download and install a different version.

The main website for pysqlite is:

http://code.google.com/p/pysqlite

You can download binary versions for MS Windows, and source code packages that you can compile yourself for Linux.

Now that you have all the libraries installed, you are ready to start using pysqlite to access and work with SpatiaLite databases. There is, however, one final thing to be aware of: because pysqlite is a database adapter for SQLite rather than SpatiaLite, you will need to load the libspatialite extension before you can use any of the SpatiaLite functionality in your Python program.

To load the libspatialite extension, add the following highlighted statements to your Python program:

from pysqlite2 import dbapi as sqlite

conn = sqlite.connect("...")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("libspatialite-2.dll")')
curs = conn.cursor()
...

For Linux users, make sure you use the correct name for the libspatialite extension. You may also need to change the name of the pysqlite2 module you're importing depending on which version you downloaded.

With all these different packages, it can be quite confusing knowing where to look for more information. First off, you can learn more about the SQL syntax supported by SQLite (and SpatiaLite) by looking at the SQL as Understood by SQLite page:

http://sqlite.org/lang.html

Then, to learn more about SpatiaLite itself, check out the SpatiaLite Manual, which can be found at:

http://gaia-gis.it/spatialite/docs.html

There are also tutorials available from the above link, but they aren't that useful if you are using SpatiaLite from Python.

Finally, to learn more about using pysqlite to access SQLite and SpatiaLite from Python, see:

http://pysqlite.googlecode.com/svn/doc/sqlite3.html

In many ways, SpatiaLite has been modeled after PostGIS. Before using SpatiaLite for your database, you need to load an "initialization file" into the database, and you also need to explicitly define your spatial columns by calling the AddGeometryColumn() function, just like you do in PostGIS. Let's see how all this works by creating a SpatiaLite database and creating an example database table.

As described above, the first step in using SpatiaLite is to connect to the database and load the SpatiaLite extension, like this:

from pysqlite2 import dbapi2 as sqlite

db = sqlite.connect("myDatabase.db")
db.enable_load_extension(True)
db.execute('SELECT load_extension("libspatialite.dll")')

You next need to load the SpatiaLite tables into your database. This can be done by downloading the initialization file from:

http://gaia-gis.it/spatialite/resources.html

For the current version of SpatiaLite at the time this book was written, the file you need is named init_spatialite-2.3.sql. The version number may have changed, so download the appropriate initialization file for your version of SpatiaLite.

This file contains a series of SQL commands that prepare your database to use with SpatiaLite. If you have the SQLite command-line client installed on your computer, you can load the initialization file directly into your database, like this:

% sqlite3 myDatabase.db < init_spatialite-2.3.sql

Alternatively, if you don't have the SQLite command-line client installed, you can use the following Python code to read the initialization file into memory and execute it using pysqlite:

from pysqlite2 import dbapi2 as sqlite

db = sqlite.connect("test.db")
db.enable_load_extension(True)
db.execute('SELECT load_extension("libspatialite.dll")')
cursor = db.cursor()

f = file("init_spatialite-2.3.sql", "r")
lines = []
for line in f.readlines():
    line = line.rstrip()
    if len(line) == 0: continue
    if line.startswith("--"): continue
    if line.startswith("BEGIN"): continue
    if line.startswith("COMMIT"): continue
    lines.append(line)
f.close()
cmds = ("".join(lines)).split(";")
for cmd in cmds:
    cursor.execute(cmd)
db.commit()

Note that this code removes comments (lines starting with --), and also strips out the BEGIN and COMMIT instructions as these can't be executed using pysqlite.

Running the initialization script will create the internal database tables needed by SpatiaLite, and will also load the master list of spatial references into the database so you can use SRID values to assign a spatial reference to your features.

After running the initialization script, you can create a new database table to hold your geo-spatial data. As with PostGIS, this is a two-step process; you first create the non-spatial parts of your table using standard SQL statements:

cursor.execute("DROP TABLE IF EXISTS cities")
cursor.execute("CREATE TABLE cities (" +
               "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
               "name CHAR(255))")

You then use the SpatiaLite function AddGeometryColumn() to define the spatial column(s) in your table:

cursor.execute("SELECT AddGeometryColumn('cities', 'geom', " +
               "4326, 'POLYGON', 2)")

You can then create a spatial index on your geometries using the CreateSpatialIndex() function, like this:

cursor.execute("SELECT CreateSpatialIndex('cities', 'geom')")

Now that you have set up your database table, you can insert geometry features into it using the GeomFromText() function:

cursor.execute("INSERT INTO cities (name, geom)" +
               " VALUES (?, GeomFromText(?, 4326))",
               (city, wkt))

And, you can retrieve geometries from the database in WKT format using the AsText() function:

cursor.execute("select name,AsText(geom) FROM cities")
for name,wkt in cursor:
    ...

Some highlights of SpatiaLite include:

While SpatiaLite is considered to be a lightweight database, it is indeed surprisingly capable. Depending on your application, SpatiaLite may well be an excellent choice for your Python geo-spatial programming needs.