pgAdmin

About

pgAdmin is a feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world, which includes, among others, the Postgis spatial extention.

pgAdmin is developed by a community of PostgreSQL experts around the world and is available in more than a dozen languages. It is Free Software released under the PostgreSQL License.

pgAdmin is aimed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes its administration easy. The application includes, among other things: syntax highlighting SQL editor, a server-side code editor, an SQL/batch/shell job scheduling agent, and much more. See more about it in the official website.

Quick start guide

Note

To execute this Quick Start Guide you will need a running instance of PostgreSQL database with the spatial extension PostGIS installed, please ask your system administrator for the connection parameters. For further information about PostgreSQL or PostGIS see Online resources.

The following quick start guide will introduce you to pgAdmin basics. It will show how to access to a spatially enabled database and run some simple queries on its tables.

  1. Download the data for this tutorial here and unzip it anywhere you think it’s convenient.

  2. Import the geodata from the shapefile into the database, if you have shp2pgsql command line utility at hand you can import the data with:

    shp2pgsql events.shp | psql -U postgres pg_test

    If you don’t have this command available you can still use QGIS db manager to import you data into the database.

  3. Open pgAdmin using any of the available shortcuts in your computer.

    By default, pgAdmin will show the following window on opening:

    ../_images/pgadmin_first_open.png

  4. Click on the Add a connection to a server (the “plug” icon) to create a new database connection.

  5. Enter the connection parameters in the dialog, a database connection is usually identified by:

    • host
    • port
    • username
    • password
    ../_images/pgadmin_register_new_server.png

  6. Click on the newly added server to open its features tree and select the events table or any other table you want to query

    ../_images/pgadmin_schema_tree.png

  7. Perform operations on selected tables with Right-Click

    ../_images/pgadmin_right_click_table_operations.png

  8. Preview data with View Data ‣ View Top 100 Rows

    ../_images/pgadmin_right_click_table_preview.png
    ../_images/pgadmin_right_click_table_preview_result.png

  9. Click on the SQL icon

    ../_images/pgadmin_execute_sql.png

  10. Execute a spatial query:

    SELECT name, St_AsText( geom ) FROM events;
    ../_images/pgadmin_execute_sql_results.png