PostGIS & ArcSDE

Background

http://geobabble.wordpress.com/2008/05/28/using-arcsde-93-with-postgresql-part-1/

http://geobabble.wordpress.com/2008/06/02/using-arcsde-93-with-postgresql-part-2/

http://geobabble.wordpress.com/2008/07/01/using-arcsde-93-with-postgresql-part-3/

http://geobabble.wordpress.com/2008/08/21/using-arcsde-93-with-postgresql-part-35/

Basic setup

user = postgres

passwd = 615cbe

port = 5432

spatial db = ufl

Install process

1. Install ArcSDE for PostgreSQL from the ESRI media.
   1. Process first installs PostgreSQL.
   2. Install ArcSDE.
2. Install PostGIS
   1. Initializes a PostGIS database (call it ufl).

      Should be able to verify in PgAdmin that the schema public has all the PostGIS functions

3. Run the ArcSDE post-installer to add SDE functions to the database

   Should be able to verify in PgAdmin that the schema sde has all the ArcSDE functions

Details

1. Licensing: first get a license from Luke Rogers

   sdesetup -o update_key -d POSTGRESQL -l "C:\Program Files\ESRI\License\sysgen\keycodes" -D ufl

2. Schema:

   in order for a user to add a layer it is necessary to add a schema for that user. e.g.,

   CREATE SCHEMA AUTHORIZATION postgres;

3. It may be necessary to change specify a different database than the default:

   sdeservice -o modify -r ADMIN_DATABASE -v ufl -d POSTGRESQL

4. And then start the service:

   sdemon -o start

5. Adding data:

   To create a data set using the PostGIS geometry, this example creates a layer from the KC hydro bigwater

   shp2sde -o create -l bigwater1,shape -f P:\gisdata\king_county\bigwater -a all -e a -u postgres -D ufl -p 615cbe -k PG_GEOMETRY

   Data can also be added through ArcCatalog; use the keyword PG_GEOMETRY to make sure the geometry data are stored in PostGIS format.

   To make the data accessible for specific users grant select privelege, e.g.,

   grant select on bigwater to ufl;

6. Metadata
   1. For data sets with HTML metadata, copy/paste the text from a web browser to an ASCII file (e.g., called layer.txt).
   2. Run cns layer.txt -o layer.met
   3. Import to layer in ArcCatalog
7. Access:

   We want a read-only access user. In that case add a user

   create user ufl nocreatedb nocreateuser password 'ufl';

   That user also needs access to the schemae:

   grant usage on schema sde to ufl; (contains the SDE stuff, I think)

   grant usage on schema postgres to ufl; (schema governing the data, I think)

Connecting

1. In ArcCatalog, select Database Connections > Add Spatial Database Connection

   Server: gis.caup.washington.edu

   Service: 5151

   {Database, Username, Password}: ufl