PostGIS & ArcSDE

From phurvitz
Revision as of 16:52, 13 October 2009 by Phil Hurvitz (talk | contribs) (Protected "PostGIS & ArcSDE" [edit=sysop:move=sysop] [cascading])
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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