PostGIS & ArcSDE

Background

user = postgres

passwd = 615cbe

port = 5432

spatial db = ufl

Install ArcSDE for PostgreSQL from the ESRI media.
1. Process first installs PostgreSQL.
2. Install ArcSDE.
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
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

Licensing: first get a license from Luke Rogers
sdesetup -o update_key -d POSTGRESQL -l "C:\Program Files\ESRI\License\sysgen\keycodes" -D ufl
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;
It may be necessary to change specify a different database than the default:
sdeservice -o modify -r ADMIN_DATABASE -v ufl -d POSTGRESQL
And then start the service:
sdemon -o start
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;
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
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)