Loading data into PostGIS with pgShapeloader¶

How it works¶

Like all graphical tools, pgShapeloader is designed for ease of use, while at the same time retaining most of the functionality of the more flexible command line tool. The pgShapeloader tool combines the two data loading stages, converting data into SQL commands and running those commands against the target database, into one operation.

Preparation¶

1. Select a shapefile to load—you will need all the files: .shp, .shx, and .dbf and so on.
2. Identify the SRID (“projection”) of your data. If available, this information is easily accessed via the layer metadata in GeoServer. If the projection is unknown, use a service like prj2epsg.org to upload and convert the shapefile’s .prj file to a SRID code.
3. Either identify the target database where you would like to load the data, or create a new database.

Launching pgShapeloader¶

Depending on how OpenGeo Suite was installed pgShapeloader may or may not have been installed. See the Installation section for installation instructions. Once installed follow the instructions below for your platform.

Mac¶

On Mac the pgShapeloader application is accessible from the installer image under PostGIS Utilities.

Launching pgShapeloader on Mac

Loading data¶

1. Make sure OpenGeo Suite is running and launch pgShapeloader.

2. Click View connection details and enter the connection information in the PostGIS Connection section. This information will depend on how the OpenGeo Suite was installed, and whether pgShapeloader is running locally or remotely. For a default local installation, the following connection info may be used:

   Username	postgres
   Password	[blank/any]
   Server Host	localhost
   Port	5432

   

   PostGIS connection options

3. Click OK to return to the main application. The shapefile loader uses the supplied connection details to connect to the target database; the connection status is reported in the Log Window. If you see any errors, check your details and try again.

   

   A successful PostGIS connection

4. To select your source files, click Add File to open the Select a Shape File dialog box. Navigate to the location of your shapefile, click the shapefile you wish to load, and click Open. Multiple files can also be added in the same way.

   

   Selecting a shapefile to import

5. Edit the configuration information for each item in the Import List to provide the correct SRID for each shapefile.

   Warning

   Do not omit this step, otherwise your data will not load properly.

   

   Changing the SRID

6. Other import options are available to configure. Click Options to open the Import Options dialog box.

   

   Additional options

   The import options are:

   Option	Description
   DBF file character encoding	Specifies the character encoding of the shapefile’s attribute columns. Default is UTF-8.
   Preserve case of column names	If this option is not selected, all column names will be lower case. Select this option to preserve mixed case.
   Do not create ‘bigint’ columns	Columns with type ‘bigint’ will not be created.
   Create spatial index automatically after load	Creates a spatial index automatically. Indexing is recommended for improved performance reasons, but if you wish to create the index manually or if you’re sure you don’t want one, clear the check box.
   Load only attribute (dbf) data	Strips the geometry column from the loading process, leaving just the attribute columns. Default is unselected.
   Load data using COPY rather than INSERT	This can sometimes improve the performance of the load process.
   Load into GEOGRAPHY column	Will load the geospatial data as type GEOGRAPHY instead of the default of GEOMETRY. Requires lat/lon data (SRID 4326).
   Generate simple geometries instead of MULTI geometries	Override the default behavior of importing multipolygons

   Note

   If you are unsure about the implications of making further changes, leave the default values as they are and click OK to return to the main dialog box.

7. Once all import options have been configured, click OK and click Import to start the conversion.

   

   Import in progress

8. On successful completion, the shapefile has been imported as a table in your PostGIS database.

   

   A successful import

9. You can verify this in pgAdmin by viewing the list of tables in the Object browser—your new table should be listed.

   

   Confirming import in pgAdmin

   You can also verify a successful import operation at the command line by typing:

   psql -U <USERNAME> -d <DATABASE> -c "\d"
   

   Note

   The specific command parameters will depend on your local configuration.

    Schema |         Name         |   Type   |  Owner
   --------+----------------------+----------+----------
    public | geography_columns    | view     | postgres
    public | geometry_columns     | view     | postgres
    public | raster_columns       | view     | postgres
    public | raster_overviews     | view     | postgres
    public | spatial_ref_sys      | table    | postgres
    public | us_cities            | table    | postgres
    public | us_cities_gid_seq    | sequence | postgres