PostGIS & ArcSDE part 2

See instructions (windows/documentation_server/ARCSDE93POSTGRESQLINSTALLGUIDES/install_gd_postgresql.htm)

1. install PostgreSQL
2. install PostGIS
3. Install ArcSDE
   1. cp sg.dll, pe.dll, st_geopmetry.dll from D:\Program Files\ArcGIS\ArcSDE\pgexe\bin to D:\Program Files\PostgreSQL\8.3\lib
4. in Postgres:

   CREATE DATABASE UFL1 template=postgis_template;

   createuser sde (superuser, set passwd)

   \c ufl;

   create schema authorization sde;

   grant usage on schema sde to public;

   create user ufl; (no superuser)

   create schema authorization ufl;

   grant insert,update,delete on public.geometry_columns to sde;

   createuser ufladmin (superuser, etc.)

   create schema authorization ufladmin;

   grant usage on schema ufladmin to public;

   update sde_dbtune set config_string = 'PG_GEOMETRY' where parameter_name = 'GEOMETRY_STORAGE' and keyword='DEFAULTS';

5. Use a connection in ArcCatalog with ufladmin to add data (avoids using sde and postgres users).

   Use a connection as user ufl for read-only access.

6. GRANT SELECT on [table_name] TO ufl to give UFL user access to data.

To create layers in PostGIS and have them usable in Arc, e.g.:

• in psql create a table, add a geometry column, and insert some data:

CREATE TABLE gtest ( ID int4, NAME varchar(20) );
SELECT AddGeometryColumn(’’, ’gtest’,’geom’,-1,’LINESTRING’,2);
INSERT INTO gtest (ID, NAME, GEOM)
VALUES (
1,
’First Geometry’,
GeomFromText(’LINESTRING(2 3,4 5,6 5,7 8)’, -1)
);

• At the command prompt, register the table as a layer:

sdelayer -o register -l gtest,geom -e l  -g AUTOMATIC -u ufladmin -p ******** -D ufl -C id