OpenGeo

OpenGeo Stack Workshop

Previous topic

Adding a PostGIS Table to GeoServer

Next topic

OpenLayers Basics

Converting Shapefile data to PostGISΒΆ

In the previous section SQL scripts were used to load a PostGIS database. Often it more useful to take existing data in file formats such as Shapefile and import them into a PostGIS database. This section covers using the command:shp2pgsql utility to convert a Shapefile into a PostGIS table.

  1. Navigate to the workshop data/bayarea directory.

  2. Copy the following files onto the desktop:

    bay_area_parks.shp
    bay_area_parks.shx
    bay_area_parks.dbf
    bay_area_parks.prj
    
    ../_images/shp_convert1.jpg

    Copying the bay_area_parks shapefile

  3. From the Start menu, open a Command Line Prompt.

    ../_images/shp_convert2.jpg

    Note

    An alternate way of opening a command prompt is to open the Start Menu and select Run.... In the resulting dialog enter the text “cmd” and clock OK.

  4. Change directory to the desktop by executing the command line:

    cd Desktop
  5. Convert the bay_area_parks.shp file to SQL with the shp2pgsql utility by executing the command line:

    "C:\Program Files\PostgreSQL\8.2\bin\shp2pgsql.exe" -s 4326 bay_area_parks.shp bay_area_parks > bay_area_parks.sql
    ../_images/shp_convert3.jpg

    Converting a shapefile to sql with shp2pgsql

    Warning

    The quotes around the path to shp2pgsql are necessary because of the space in the file path.

    Note

    shp2pgsql is a utility used to convert a shapefile into a database table. It comes with every PostGIS installation.

    Note

    Tab completion is supported by the windows command prompt and can help when specifying the path to shp2pgsql

  6. Execute the bay_area_parks.sql script against the database by executing the command line:

    "C:\Program Files\PostgreSQL\8.2\bin\psql.exe" -U postgres bayarea -f bay_area_parks.sql
    ../_images/shp_convert4.jpg

    Loading the result of shp2pgsql into the database

    Note

    psql is a program used to interact with a PostgreSQL database from the command line. The commmand above takes the sql file bay_are_parks.sql created by shp2pgsql, and executes it against the database, creating the bay_area_parks table.

    Note

    If prompted for a password remember that the password is “postgres”

  7. Using skills learned in the previous section, create a feature type for the bay_area_parks table created in the previous step.

    ../_images/shp_convert5.jpg

    Creating the bay_area_parks feature type

    ../_images/shp_convert6.jpg

    Previewing bay_area_parks