OpenGeo

OpenGeo Stack Workshop

Previous topic

Creating a Spatial Database

Next topic

Adding a PostGIS Database to GeoServer

Loading Spatial Data

There are a variety of ways to load a PostGIS database with spatial data. This section covers the task of loading a database with spatial data via SQL scripts loaded with pgAdmin.

  1. In the Object Browser navigate to Databases ‣ bayarea, and click the SQL Query button in the toolbar.

    ../_images/postgis_load4.jpg

    Selecting a database and executing an SQL query

  2. In the Query window, open the File menu and select Open.

    ../_images/postgis_load5.jpg

    Opening an SQL script

  3. In the File Explorer navigate to the workshop data/bayarea directory and open the bay_area_roads.sql file.

    ../_images/postgis_load6.jpg

    Selecting the bay_area_roads SQL script

    ../_images/postgis_load7.jpg

    The bay_area_roads SQL script

    Note

    The bay_area_roads.sql file contains the SQL statements to create a table named bay_area_roads and populate it with data.

    Warning

    It may take a few moments for pgAdmin to load the bay_area_roads script. Be patient.

  4. Execute the SQL by clicking the Execute Query button in the toolbar.

    ../_images/postgis_load8.jpg

    Executing an SQL script

    Upon successful query execution the following status message should appear in the Messages panel:

    ../_images/postgis_load9.jpg

    Successful SQL completion

  5. Repeat steps 5 through 7 with the bay_area_transit.sql script

    Note

    The bay_area_transit.sql creates the tables:

    • bay_area_bus_stops
    • bay_area_transit_stations
    • bay_area_transit_routes
  6. Verify that all the tables are loaded via the pgAdmin Object Browser:

    ../_images/postgis_load10.jpg

    Browsing tables in the bayarea database

    Note

    It may be necessary to refresh the bayarea database in the object browser before the new tables are visible.

    ../_images/postgis_load11.jpg

    Refreshing the database

At this point the PostGIS database has been created and is loaded with spatial data. The next few sections will focus on connecting to PostGIS with GeoServer and serving the data sets created in this section.