I was assigned to discover how ArcSDE and Postgre can connect. Here is my finding.
Before you read this article, you must be equipped with some knowledge about Postgre so you will have a better understanding. You can read “PostgreSQL 8 for WINDOWS” by Richard Blum. Just read the first 4 chapters will do !
This article is about connecting your Postgre to ArcSDE. There are a few information that you will find in this article :
1. The installation step by step.
2. Creating a Postgre database
3. Connecting and registering your database to ArcSDE
4. Scenario : Create table manually, fill in some data and register it with ArcSDE
5. User privilege
6. A little about ArcSDE
First of all, you have to remember that :
1. ArcSDE fpr Postgre only available at 9.3
2. NO OLDER client can access ArcSDE for Postgre (ie: Desktop 9.2 can’t access ArcSDE 9.3)
3. Remember that ArcSDE only support : Postgre 8.3.0 and Postgis 1.3.2
4. This guide is tested in Windows XP operating system (this might work on Windows Server 2000 and 2003)
5. ArcSDE runs on : Window Server 2000 and 2003, Linux SUSE 10 and RedHat
The installation steps
This is the order for the installations :
3. ArcSDE installation and
4. ArcSDE postinstallation (optional)
The installation of Postgre is prety much straight forward. Just follow the wizard and you are done with it. Here are the screen shots for the Postgre installation
1.jpg and 2.jpg: accept the default
3.jpg : You can enable the Application Stack Builder if you don’t have Postgis 1.3.2 installation file. If you have Postgis then you can disable it
4.jpg : it’s the Postgre’s service name that will run on your Windows. Just accept the default values
5.jpg : it’s the superuser account. The default for “Superuser name” is usually “postgres”. Leave it as it is. Give password that is easy to remember.
6.jpg, 7.jpg and 8.jpg : Accept the default until finish
After finish installing Postgre, you can install Postgis. You don’t have to install Postgis if you don’t intend to use GEOMETRY spatial data type in your database.
Before you start Postgis installation, if you want to set the tablespace to the location that you prefer, you must set the “default_tablespace” in PGAdmin (you can find it in PGAdmin in menu Tools –> Server Configuration –> postgresql.conf). Restart the computer to get the changes saved into your computer.
5.jpg : Specify the database name. You will need this name later in ArcSDE post-installation iff you want to use GEOMETRY to store your spatial data
The installation of Postgis will create a template Postgis database called “template_postgis” and a database of your own (by default, the name is usually “postgis”). For the last database mentioned, postgis, you can change the database name if you want to.
Next is installing ArcSDE :
The most important part is the post installation.
5.jpg : specify the “Superuser name” of your Postgre database
6.jpg : specify the Postgis database that you have created in the previous step.
If you accidentally type in a wrong database name you can fix it by executing command in postgre “psql –d yourdatabase -f lwpostgis.sql”. The script is provided with ArcSDE installation dvd.
10.jpg : name the service to your desire. It has to be meaningfull and easy to remember
The post installation will create a Login Role called “sde”. See “after installing sde.jpg”. Compare it with “before installing sde.jpg”.
If you follow the installation steps described here, than you are DONE. You can use your database with ArcSDE. HOWEVER… the owner of your data is “sde”. If you want the data to be owned by a certain user than you can create your own Login Role.
Important Note : to be a data owner, the “SCHEMA NAME” and “LOGIN ROLE” must be THE SAME ! Otherwise you will get error “Database user name and current user schema do not match”
Creating a Postgre database (provided that you already have Postgis installed)
You can create a database manually by using script SQL via PGAdmin or “psql to Postgres” :
1. You must CREATE a folder in “postgres installation directory\8.3\data\foldername” manually from Windows Explorer. Replace “foldername” with name that represents your tablespace. This name will be used for variable “TABLESPACE” when you create new database. If you don’t want to, the default TABLESPACE will be pg_default and you don’t need to include the “TABLESPACE” word when you create new database.
2. Execute script below :
CREATE DATABASE [dbname] //dbname = database name
WITH OWNER=[yourloginrole] //yourloginrole = specify the owner name/login role
TEMPLATE=template_postgis //this is the template database. By default it is “template_postgis”
TABLESPACE=[foldername] //[Optional]. foldername = the folder that you created in the first step. If you don’t specify (ie: not writing the ..
//..TABLESPACE word), the default value is pg_default”
postgres=# CREATE DATABASE dbname template=template_postgis tablespace=myspatialdb;
From the script above, you can see that I use the Postgis template to create my database. The database created will have ability to store data in Postgis format (GEOMETRY).
3. Execute the script below to create role and schema.
create role myself login password ‘myself’ noinherit createdb;
create schema myself authorization myself
grant USAGE on schema myself to public;
GRANT select, insert, update, delete on public.geometry_columns to myself; //execute this line if your data is stored in GEOMETRY because the data..
//..is stored in Public schema
4. Run ArcCatalog. Double click “Add Spatial Database Connection”. Fill in the textbox correctly then click Connect. See “connecting via catalog.jpg”. You can start working now by creating Feature Class (FC) or Feature Dataset (FD). Right click your newly created FD/FC then click “Register with Geodatabase” and “Register as Version” (optional).
5. Edit your data in ArcMap. Dont forget to turn off the Version editing if you don’t want to have versioned editing by UN-CHECKING the “Edit a version of a database…”. See “edit option.jpg”. By default it is CHECKED. If you edit your FC straight away you will get error message like this “no sufficient privilege or feature class not registered as version”. If you want to have version edit, then leave the default setting (CHECKED) but you must do “Register as Version” in ArcCatalog first.
6. Finish. You are ready to work now !
Connecting and registering your database to ArcSDE
You can connect your EXISTING table to ArcSDE manually by registering it using command supplied with ArcSDE. Once it is registered with ArcSDE then it will have full geodatabase functionality. You can execute this command through command prompt. The command is :
sdelayer -o register -l tester,shape -e a -C objectid,SDE -R 2 -t ST_GEOMETRY -i 5158 -D final2 -u final2 -p final2
-o register : the operation type is “register”
-l tester,shape : the table name is “tester” and the spatial column in this table is “shape”. Spatial column has data type of GEOMETRY or ST_GEOMETRY.
-e a : “a” indicates that the data represents Area. For point type “p” and for line type “l”.
-C objectid,SDE : “objectid” is column that serves as id (index) and this is maintained by user called “SDE”. It is recommended that the index is maintained by user “SDE”
-R 2 : “2” is the spatial reference number of your data. If you do not know, you can find out by creating a dummy feature class (and don’t forget to specify the spatial reference) in ArcCatalog then find it out in sde.sde_geometry_columns in column “srid”
-t : “ST_GEOMETRY” is the spatial type. You can choose Postgis spatial type by typing “GEOMETRY”
-i : “5158” is the service number of your ArcSDE service
-D : “final2” is the name of your database
-u : “final2” is the username
-p : “final2” is the password
(for the sake of testing, I put in the same name for database name, username and password). The “sdelayer -o register..” above will create 2 indexes in your table. There will be codes added to your table like the following :
CREATE INDEX a1_ix1
CREATE UNIQUE INDEX r39_sde_rowid_uk
To test the connection, open ArcCatalog then double click “Add Spatial Database Connection” and connect using your Login Role that you created in Postgre.
Scenario : Create table manually, fill in some data and register it with ArcSDE
1. Creating table
create table sde.blocksss_st(objectid integer not null, block varchar(24), shape st_geometry);
ALTER TABLE sde.blocksss_st OWNER TO sde;
GRANT ALL ON TABLE sde.blocks_st TO sde;
2. Fill in some data
insert into sde.blocksss_st values (1, ‘block’, st_geometry(‘linestring(487998.66386880 3766968.84310695, 490186.81623251 3766968.84310695)’,1));
To view the data that is just inserted run :
select * from sde.blocks_st;
Remark : Number “1” in the end of the command above is the SRID number. Please refer to Connecting and registering your database to ArcSDE above.
3. Register it with ArcSDE
sdelayer -o register -l sde.blocks_st,shape -C objectid,SDE -e l -t ST_GEOMETRY -i 5154 -D postgis -u sde -p sde
To check the data yo can run :
select st_astext(shape) from sde.blocks_st;
There are two types of user, EDITOR and VIEWER. You can grant privileges to both types using ArcCatalog or PGAdmin. I recommend that you use ArcCatalog because it is easier as you don’t have to find the corresponding A and D tables if the FC is involved in Version editing.
1. EDITOR : has privileges Select, Insert, Delete and Update
Create new Login Role using PGAdmin tool. Open ArcCatalog, right click the FC that you want it to be granted to. Check all the available privileges. See “grant1.jpg” Select the privileges. See “grant2.jpg”. You can check the table in PGAdmin.
This grant process will add the following code to your FC (table) in your table :
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE schema.table TO loginrole;
2. VIEWER : has Select privilege
Create new Login Role using PGAdmin tool. Open ArcCatalog, right click the FC that you want it to be granted to. Check the SELECT privilege
This grant process will add the following code to your FC (table) in your table :
GRANT SELECT ON TABLE schema.table TO loginrole;
You can also group your user. This is how to do it :
1. Right click GROUP ROLES Add New. Give the group a name. No password is required
2. Double click the table name. This will open the table properties. Go to “Privileges” tab. See “grant group 1.jpg”
3. Double click SCHEMA where the FC exist. Grant USAGE for the group role. See “grant group 2.jpg”
4. Create a new Login Role. Check the option “inherits rights from from parent roles”. See “grant group user 1.jpg”. In tab “Role membership”, add the Group Role to the textbox on the right by clicking the “>>” button. See “grant group user 2.jpg”
5. Grant SELECT for group roles to the desired table. See “grant select group.jpg”
Any Login Role that is a member of Group Role will automatically inherit the GROUP ROLE privilege.
Remark : you CAN’T GRANT SELECT to Group Roles via ArcCatalog but you CAN GRANT SELECT to Login Roles via ArcCatalog. See point #2 about VIEWER above.
A little about ArcSDE
ArcSDE stands for Arc-Spatial Database Engine. It has ability to make your database spatially enabled. ArcSDE adds the following capabilities to geodatabases:
• It defines how spatial data is stored and managed in a DBMS
• ArcSDE enables support for version management of geodatabases. This enables use by multiple, simultaneous users and editors, supports distributed geodatabases, and enables historical archiving of all updates.
• ArcSDE supports spatial types for SQL in all DBMS’s using the ISO and OGC SQL Multi-media standard for SQL.
Note: i havnt uploaded the screen captures yet.