Using Command-Line Tools - Desktop GIS: Mapping the Planet with Open Source Tools - page 195

psql using the -d switch is all we need to send the SQL to the database
and create the table.

As the import proceeds, the results are printed to the screen. I trun-
cated the INSERT statements in the example, since there are 606 of
them—one for each city. Notice that before the import began we get
some feedback about what's going on, including the creation of the
geometry column.

If we use psql to examine the table after it's loaded, we find that our
spatial index was created as part of the import. Partial output from the
\d command in psql shows the GIST index cities_pg_the_geom_gist was
created on the geometry column.

Indexes:
"cities_pg_pkey" PRIMARY KEY, btree (gid)
"cities_pg_the_geom_gist" gist (the_geom)

Our table is ready to use in our GIS applications; however, you should
run the VACUUM ANALYZE command to have PostgreSQL collect statistics
to improve performance.

Exporting to a Shapefile
To export data from your PostGIS database to a shapefile, use the
pgsql2shp command. This command has fewer options than its coun-
terpart:

$ pgsql2shp
RCSID: $Id: pgsql2shp.c 2513 2006-10-14 14:22:10Z mschaber $ RELEASE: 1.2.1
USAGE: ./pgsql2shp [<options>] <database> [<schema>.]<table>
./pgsql2shp [<options>] <database> <query>

OPTIONS:
-f <filename>

Use this option to specify the name of the file

to create.
-h <host> Allows you to specify connection to a database on a
machine other than the default.
-p <port> Allows you to specify a database port other than the default.
-P <password> Connect to the database with the specified password.
-u <user> Connect to the database as the specified user.
-g <geometry_column> Specify the geometry column to be exported.
-b Use a binary cursor.
-r Raw mode. Do not assume table has been created by
the loader. This would not unescape attribute names
and will not skip the 'gid' attribute.
-k Keep postgresql identifiers case.
-? Display this help screen.