Difference between revisions of "PostGIS & ArcSDE part 2"

From phurvitz
Jump to: navigation, search
m (Protected "PostGIS & ArcSDE part 2" [edit=sysop:move=sysop] [cascading])
 
(3 intermediate revisions by the same user not shown)
Line 11: Line 11:
 
#: create schema authorization sde;
 
#: create schema authorization sde;
 
#: grant usage on schema sde to public;
 
#: 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;
 
#: grant insert,update,delete on public.geometry_columns to sde;
 
#: createuser ufladmin (superuser, etc.)
 
#: createuser ufladmin (superuser, etc.)
Line 17: Line 19:
 
#: update sde_dbtune set config_string = 'PG_GEOMETRY' where parameter_name = 'GEOMETRY_STORAGE' and keyword='DEFAULTS';
 
#: update sde_dbtune set config_string = 'PG_GEOMETRY' where parameter_name = 'GEOMETRY_STORAGE' and keyword='DEFAULTS';
 
#: </tt>
 
#: </tt>
−
# Use a connection with ufladmin to add data (avoids using sde and postgres users)
+
# 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.
 
# <tt>GRANT SELECT on [table_name] TO ufl</tt> to give UFL user access to data.
 
# <tt>GRANT SELECT on [table_name] TO ufl</tt> 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:
 +
<pre>
 +
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)
 +
);
 +
</pre>
 +
* At the command prompt, register the table as a layer:
 +
<pre>
 +
sdelayer -o register -l gtest,geom -e l  -g AUTOMATIC -u ufladmin -p ******** -D ufl -C id
 +
</pre>

Latest revision as of 16:52, 13 October 2009

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