Difference between revisions of "PostGIS & ArcSDE"

From phurvitz
Jump to: navigation, search
(New page: __FORCETOC__ ==Basic setup== user = postgres passwd = 615caup port = 5432 spatial db = ufl ==dbcluster== su = postgres passwd = 615cbe port = 5431)
 
m (Protected "PostGIS & ArcSDE" [edit=sysop:move=sysop] [cascading])
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
__FORCETOC__
 
__FORCETOC__
 +
==Background==
 +
[http://geobabble.wordpress.com/2008/05/28/using-arcsde-93-with-postgresql-part-1/ 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/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/07/01/using-arcsde-93-with-postgresql-part-3/]
 +
 +
[http://geobabble.wordpress.com/2008/08/21/using-arcsde-93-with-postgresql-part-35/ http://geobabble.wordpress.com/2008/08/21/using-arcsde-93-with-postgresql-part-35/]
 +
 
==Basic setup==
 
==Basic setup==
−
user = postgres
+
:user = postgres
−
passwd = 615caup
+
:passwd = 615cbe
−
port = 5432
+
:port = 5432
−
spatial db = ufl
+
:spatial db = ufl
 +
 
 +
==Install process==
 +
# Install ArcSDE for PostgreSQL from the ESRI media.
 +
## Process first installs PostgreSQL.
 +
## Install ArcSDE.
 +
# Install PostGIS
 +
## 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
 +
 
 +
==Details==
 +
# Licensing: first get a license from Luke Rogers
 +
#: <tt>sdesetup  -o update_key -d POSTGRESQL -l "C:\Program Files\ESRI\License\sysgen\keycodes" -D ufl</tt>
 +
# Schema:
 +
#: in order for a user to add a layer it is necessary to add a schema for that user. e.g.,
 +
#:: <tt>CREATE SCHEMA AUTHORIZATION postgres;</tt>
 +
# It may be necessary to change specify a different database than the default:
 +
#:: <tt>sdeservice -o modify -r ADMIN_DATABASE -v ufl -d POSTGRESQL</tt>
 +
# And then start the service:
 +
#:: <tt>sdemon -o start</tt>
 +
# Adding data:
 +
#: To create a data set using the PostGIS geometry, this example creates a layer from the KC hydro bigwater
 +
#:: <tt>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</tt>
 +
#: 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.,
 +
#:: <tt>grant select on bigwater to ufl;</tt>
 +
# Metadata
 +
## For data sets with HTML metadata, copy/paste the text from a web browser to an ASCII file (e.g., called <tt>layer.txt</tt>).
 +
## Run <tt>cns layer.txt -o layer.met</tt>
 +
## Import to layer in ArcCatalog
 +
# Access:
 +
#: We want a read-only access user. In that case add a user
 +
#:: <tt>create user ufl nocreatedb nocreateuser password 'ufl';</tt>
 +
#: That user also needs access to the schemae:
 +
#:: <tt>grant usage on schema sde to ufl;</tt> (contains the SDE stuff, I think)
 +
#:: <tt>grant usage on schema postgres to ufl;</tt> (schema governing the data, I think)
  
−
==dbcluster==
+
==Connecting==
−
su = postgres
+
# In ArcCatalog, select '''Database Connections > Add Spatial Database Connection'''
−
passwd = 615cbe
+
#: Server: '''gis.caup.washington.edu'''
−
port = 5431
+
#: Service: '''5151'''
 +
#: {Database, Username, Password}: '''ufl'''

Latest revision as of 16:52, 13 October 2009

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
Retrieved from "http://faculty.washington.edu/phurvitz/mediawiki/index.php?title=PostGIS_%26_ArcSDE&oldid=1998"