Documentation
Tutorials/Workshops
- http://workshops.opengeo.org/postgis-intro/
- http://workshops.opengeo.org/postgis-spatialdbtips/
- http://linfiniti.com/downloads/pgtraining.pdf
- http://revenant.ca/www/postgis/workshop/
- http://trac.osgeo.org/postgis/wiki/UsersWikiTutorials
- http://download.osgeo.org/osgeo/foss4g/2009/SPREP/0Tue/Parkside%20GO1/0900/
- http://postgisonline.org/tutorials/
- http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01
Connecting via ODBC
Download, install driver from:
http://www.postgresql.org/ftp/odbc/versions/msi/
Control Panel > Administrative Tools > Data Sources (ODBC) > System DSN > Add > PostgreSQL Unicode… (see screenshot below)
Manage a PostGIS database server
The standard tool to manage a PostGIS instance is pgAdmin.
It can be downloaded from here (Windows platform) or here (other operating systems).
After following the installation procedure, proceed with the following steps:
1) connect to a database server
pgAdmin can manage multiple database servers. The first step is to enter the connection details of a PostGIS database server by pressing the plug icon:
The connection details to enter are:
- name: the name you give to this server connection (ex: “local instance – admin”, or “Jupiter Assets”)
- host: the network name (or IP address) of the host
- port: default port is 54321 for a Windows OpenGeo Suite installation, 5432 for an Ubuntu
- SSL: leave as is
- maintenance DB: leave as is
- username and password: enter the credentials provided by your administrator. These credentials will allow or deny access to individual database objects.
Lease the other options as is.
Note: if the connection is refused, check the connection details then ask your database administrator if remote TCP connection is enabled from your machine (it is disabled by default for security reasons).
2) explore the database tree
Once the connection is successfully established, the databases and their objects are accessible via a tree:
Right-clicking on a database object gives access to the attributes and actions of this object. Each object type (table, sequence, schema, …) has specific attributes and actions:
The most useful action in the data exploration context is: View Data > View Top 100 rows. Another handy action is “Refresh” – it allows the pgAdmin client to reload any changes to the object.
3) run a SQL query
Once connected to a database server (step 1) and having selected a database (step 2) to explore, press the SQL icon (a sheet with a pen):
In the SQL querying window, arbitrary queries can be written:
The green arrow sends the query to the database server and the results are returned in the bottom part of the screen:
