Post date: Nov 26, 2014 11:13:40 PM
While my previous post outlined creating a spatially-enabled database using SDE, this post will document how to use postgis to track geometries and spatial references. In my SDE post, I mentioned using SDE was the simplest way to create a spatially-enabled database. Postgis is in fact more complicated and harder to setup initially, but I believe is more powerful and easier to use in the long run. Postgis database can also accept SDE connections from ArcGIS, so using postgis allows operability with many open source GIS tools and frameworks, while also supporting ArcGIS users.
To get started, first one must understand postgresql. While pgAdmin is useful for some admin tasks on a windows system, the command line is really where postgresql is meant to be administered. Due to the nature of the configuration steps for a postgis database, I have chosen to document all configuration steps as done at the command line. Some of these steps could be done through pgAdmin, but not all. Additionally, these steps are applicable cross-platform with minimal differences. The steps are valid for postgres versions previous to 9.1, or 9.1+ versions that have not had postgis installed as an extension (but postgis is installed for the postgres version in use), and are taken directly from chaper two of the postgis user manual.
To begin, open a cmd window as an administrator. If the postgresql bin folder is not in the system path, cd the bin folder (on Atlas: C:\Program File\PostgreSQL\9.0\bin) to make the commands available. If you are unsure of the necessity of this set, type psql and press enter. If the command is not found, then the postgres bin folder is not on the system path.
With the postgres commands available, here are the steps (1 through 4 are required):
The first step in creating a PostGIS database is to create a simple PostgreSQL database.
createdb [yourdatabase]
Note: likely all of the commands here will need "-U postgres" added as the first option so they use the database admin user.
Many of the PostGIS functions are written in the PL/pgSQL procedural language. As such, the next step to create a PostGIS database is to enable the PL/pgSQL language in your new database. This is accomplish by the command below command. For PostgreSQL 8.4+, this is generally already installed
createlang plpgsql [yourdatabase]
This command may result in the message that the language is already loaded or created. If that is the case, this step was not necessary, but was not harmful.
Now load the PostGIS object and function definitions into your database by loading the postgis.sql definitions file (located in [prefix]/share/contrib as specified during the configuration step).
psql -d [yourdatabase] -f postgis.sql
For a complete set of EPSG coordinate system definition identifiers, you can also load the spatial_ref_sys.sql definitions file and populate the spatial_ref_sys table. This will permit you to perform ST_Transform() operations on geometries.
psql -d [yourdatabase] -f spatial_ref_sys.sql
OPTIONAL: If you wish to add comments to the PostGIS functions, the final step is to load the postgis_comments.sql into your spatial database. The comments can be viewed by simply typing \dd [function_name] from a psql terminal window.
psql -d [yourdatabase] -f postgis_comments.sql
OPTIONAL: Install raster support
psql -d [yourdatabase] -f rtpostgis.sql
OPTIONAL: Install raster support comments. This will provide quick help info for each raster function using psql or PgAdmin or any other PostgreSQL tool that can show function comments
psql -d [yourdatabase] -f raster_comments.sql
OPTIONAL: Install topology support
psql -d [yourdatabase] -f topology/topology.sql
OPTIONAL: Install topology support comments. This will provide quick help info for each topology function / type using psql or PgAdmin or any other PostgreSQL tool that can show function comments
psql -d [yourdatabase] -f topology/topology_comments.sql
OPTIONAL: If you plan to restore an old backup from prior versions in this new db, run:
psql -d [yourdatabase] -f legacy.sql
Note: There is an alternative legacy_minimal.sql you can run instead which will install barebones needed to recover tables and work with apps like MapServer and GeoServer. If you have views that use things like distance / length etc, you'll need the full blown legacy.sql
You can later run uninstall_legacy.sql to get rid of the deprecated functions after you are done with restoring and cleanup.
With this done, you can add users to the database. If you plan to use SDE to connect to the database, follow the instructions I have written up in my previous post about postgresql users and SDE database. A later post will outline connecting to a postgis database from ArcGIS and any caveats about user permissions or other details.