jduck.net

I am making the plunge to get more informed with how to use PostGIS. It is pretty impressive so far. Here is a quick tutorial which shows you how to get started and loading in a shapefile to play around with some fun SQL querying. I assume a basic understanding of the linux command line and some basic SQL skills.

To get started in ubuntu 7.04 (should work in 7.10 and debian too):

$ sudo apt-get install postgis postgresql-8.1-postgis

Next we’ll start setting up the PostGIS environment.

Then we need to set up a new database, I called mine geodb:

$ createdb geodb

We then need to bind it to the PostGIS libs with the following:

$ createlang plpgsql geodb

And finally add the PostGIS Tables:

$ psql -d mytestdb -f /usr/share/postgresql-8.1-postgis/lwpostgis.sql
$ psql -d mytestdb -f /usr/share/postgresql-8.1-postgis/spatial_ref_sys.sql

Phew….that was a bit of work.

Now we’re ready to go. We have a database called geodb and we’ve told PostgreSQL that it is a PostGIS database.

So now we can use the shp2pgsql command to import a shapefile into the db:

$ shp2pgsql -W LATIN1 -D -I myshapefile.shp mytablename | psql geodb

-W is for setting the encoding type, I had problems with many shapefiles as my locale is UTF8 and PostgreSQL seems to default to the locale. For me LATIN1 has been working better.

-D sets the SQL output to be in dump format for insertion into the database.

-I flags it to compute the spatial indexing upon import. For large complex vector features this can take awhile, so use with discretion.

All of this is piped to

This gives us a new table called “tablename”, which we can see by doing the following:

$ psql geodb
Welcome to psql 8.1.8, the PostgreSQL interactive terminal.
 
Type:  \\copyright for distribution terms
       \\h for help with SQL commands
       \\? for help with psql commands
       \\g or terminate with semicolon to execute query
       \\q to quit
 
geodb=#

At the PostgreSQL prompt you can enter various SQL commands including queries.

geodb=# \\d mytablename

shows the columns and data types of the data in the table.

Notice the column called: the_geom

This is where the GIS Objects are stored.

The sample query there would show all the data in the shapefile’s associated .dbf as well as a column called

Next time we’ll launch into some querying fun.

Onward to Part II…