4.4 Loading GIS Data

PostGIS 1.5.
1 Manual
32 / 315
SELECT a.lake_id, b.wharf_id

FROM lakes a, wharfs b
WHERE a.geom && b.geom
AND ST_Relate(a.geom, b.geom, 102101FF2);
For more information or reading, see:

OpenGIS Simple Features Implementation Specification for SQL (version 1.1, section 2.1.13.2)
Dimensionally Extended Nine-Intersection Model (DE-9IM) by Christian Strobl
Encyclopedia of GIS By Hui Xiong
4.4 Loading GIS Data

Once you have created a spatial table, you are ready to upload GIS data to the database. Currently, there are two ways to get data
into a PostGIS/PostgreSQL database: using formatted SQL statements or using the Shape file loader/dumper.
4.4.1 Using SQL

If you can convert your data to a text representation, then using formatted SQL might be the easiest way to get your data into
PostGIS. As with Oracle and other SQL databases, data can be bulk loaded by piping a large text file full of SQL "INSERT"
statements into the SQL terminal monitor.
A data upload file (roads.sql for example) might look like this:
BEGIN;
INSERT INTO roads (road_id, roads_geom, road_name)
VALUES (1,ST_GeomFromText(LINESTRING(191232 243118,191108
COMMIT;
243242),-1),Jeff Rd);
244817),-1),Geordie Rd);
229814),-1),Paul St);
259122),-1),Graeme Ave);
228134),-1),Phil Tce);
268322),-1),Dave Cres);
The data file can be piped into PostgreSQL very easily using the "psql" SQL terminal monitor:
psql -d [database] -f roads.sql
4.4.2 Using the Loader

The shp2pgsql data loader converts ESRI Shape files into SQL suitable for insertion into a PostGIS/PostgreSQL database
either in geometry or geography format. The loader has several operating modes distinguished by command line flags:
In addition to the shp2pgsql command-line loader, there is an shp2pgsql-gui graphical interface with most of the options as
the command-line loader, but may be easier to use for one-off non-scripted loading or if you are new to PostGIS. It can also be
configured as a plugin to PgAdminIII.
(c|a|d|p) These are mutually exclusive options:
-c Creates a new table and populates it from the shapefile. This is the default mode.
PostGIS 1.5.1 Manual

33 / 315
-a Appends data from the Shape file into the database table. Note that to use this option to load multiple files, the files
must have the same attributes and same data types.
-d Drops the database table before creating a new table with the data in the Shape file.
-p Only produces the table creation SQL code, without adding any actual data. This can be used if you need to completely
separate the table creation and data loading steps.
-? Display help screen.
-D Use the PostgreSQL "dump" format for the output data. This can be combined with -a, -c and -d. It is much faster to load
than the default "insert" SQL format. Use this for very large data sets.
-s <SRID> Creates and populates the geometry tables with the specified SRID.
-k Keep identifiers case (column, schema and attributes). Note that attributes in Shapefile are all UPPERCASE.
-i Coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant
it.
-I Create a GiST index on the geometry column.
-w Output WKT format, for use with older (0.x) versions of PostGIS. Note that this will introduce coordinate drifts and will
drop M values from shapefiles.
-W <encoding> Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the
specified encoding to UTF8. The resulting SQL output will contain a SET CLIENT_ENCODING to UTF8 command,
so that the backend will be able to reconvert from UTF8 to whatever encoding the database is configured to use internally.
-N <policy> NULL geometries handling policy (insert*,skip,abort)
-n -n Only import DBF file. If your data has no corresponding shapefile, it will automatically switch to this mode and load just
the dbf. So setting this flag is only needed if you have a full shapefile set, and you only want the attribute data and no
geometry.
-G Use geography type instead of geometry (requires lon/lat data) in WGS84 long lat (SRID=4326)
An example session using the loader to create an input file and uploading it might look like this:
# shp2pgsql -c -D -s 4269 -i -I shaperoads.shp myschema.roadstable > roads.sql
# psql -d roadsdb -f roads.sql
A conversion and upload can be done all in one step using UNIX pipes:
# shp2pgsql shaperoads.shp myschema.roadstable | psql -d roadsdb
4.5 Retrieving GIS Data

Data can be extracted from the database using either SQL or the Shape file loader/dumper. In the section on SQL we will discuss
some of the operators available to do comparisons and queries on spatial tables.
4.5.1 Using SQL

The most straightforward means of pulling data out of the database is to use a SQL select query and dump the resulting columns
into a parsable text file:

4.4 Loading GIS Data

Uploaded by

Copyright:

Available Formats

4.4 Loading GIS Data

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

4.4 Loading GIS Data

Uploaded by

Copyright:

Available Formats

PostGIS 1.5.

SELECT a.lake_id, b.wharf_id

For more information or reading, see:

4.4 Loading GIS Data

4.4.1 Using SQL

4.4.2 Using the Loader

PostGIS 1.5.1 Manual

4.5 Retrieving GIS Data

4.5.1 Using SQL

You might also like