Web Mapping Illustrated

Load Data into the Database

There are many ways to put data into a database. One method is to manually type SQL commands with psql to insert data into a table. You can also use programs that convert data into an SQL script that can then be loaded, or you can use a tool that exports data directly to the database.

Using shp2pgsql

PostGIS comes with command-line tools called pgsql2shp and shp2pgsql. These can be used to convert from a PostGIS table to a shapefile and back.

The tool shp2pgsql converts the shapes to a text stream of SQL commands. Therefore, you need to pipe it to a text file for later loading or to the psql command to use it immediately. For example, use one of these methods:

> shp2pgsql countyp020.shp countyp020
               > 
               mycounties.sql
> psql -d project1 -f mycounties.sql

or use this one to load the data immediately to the database:

> shp2pgsql mycounties.shp mycounties | psql -d project1

By default, the shp2pgsql command puts the geometry data into a field called the_geom, whereas the default for the ogr2ogr command (shown next) puts the geometry data into a field called wkb_geometry. These defaults can be overridden and changed to something more meaningful. To acquire the countyp020 data, see the next section.

Using ogr2ogr

ogr2ogr is an excellent program for putting spatial data into a database. It's part of the GDAL/OGR toolkit included in FWTools and introduced in Chapters 3 and 7. This command-line tool takes any OGR supported data layer and exports it into the database.