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.