Difference between revisions of "PostGIS & ArcSDE part 2"
From phurvitz
Phil Hurvitz (talk | contribs) |
Phil Hurvitz (talk | contribs) m (Protected "PostGIS & ArcSDE part 2" [edit=sysop:move=sysop] [cascading]) |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
# Install ArcSDE | # Install ArcSDE | ||
## cp sg.dll, pe.dll, st_geopmetry.dll from D:\Program Files\ArcGIS\ArcSDE\pgexe\bin to D:\Program Files\PostgreSQL\8.3\lib | ## cp sg.dll, pe.dll, st_geopmetry.dll from D:\Program Files\ArcGIS\ArcSDE\pgexe\bin to D:\Program Files\PostgreSQL\8.3\lib | ||
| − | # in Postgres: | + | # in Postgres:<tt> |
#: CREATE DATABASE UFL1 template=postgis_template; | #: CREATE DATABASE UFL1 template=postgis_template; | ||
| − | #: createuser sde (superuser, | + | #: createuser sde (superuser, set passwd) |
| − | #: \c | + | #: \c ufl; |
#: 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; | ||
#: createuser ufladmin (superuser, etc.) | #: createuser ufladmin (superuser, etc.) | ||
#: create schema authorization ufladmin; | #: create schema authorization ufladmin; | ||
#: grant usage on schema ufladmin to public; | #: grant usage on schema ufladmin to public; | ||
| + | #: update sde_dbtune set config_string = 'PG_GEOMETRY' where parameter_name = 'GEOMETRY_STORAGE' and keyword='DEFAULTS'; | ||
| + | #: </tt> | ||
| + | # 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. | ||
| + | |||
| + | |||
| + | 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)
- install PostgreSQL
- install PostGIS
- Install ArcSDE
- cp sg.dll, pe.dll, st_geopmetry.dll from D:\Program Files\ArcGIS\ArcSDE\pgexe\bin to D:\Program Files\PostgreSQL\8.3\lib
- 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';
- 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.
- 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
