View source for PostGIS & ArcSDE (linux)

__FORCETOC__
=Installing SDE & PostGIS on a linux machine=
==OS Config==
* RedHat (Linux gist 2.6.18-128.1.1.el5xen #1 SMP Mon Jan 26 14:34:58 EST 2009 i686 i686 i386 GNU/Linux)
*: Red Hat Enterprise Linux Server release 5.3 (Tikanga)
* Running on a VMWare machine on top of WinXP 


===OS users===
* postgres
*: .bashrc contents:
<pre>
PGDATA=/usr/local/pgsql/data
export PGDATA
PATH=$PATH:/usr/bin:/usr/local/bin
export PATH
LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/usr/local/pgsql/lib
export LD_LIBRARY_PATH
</pre>

* sde
*: .bashrc contents:
<pre>
SDEHOME=/usr/local/sde/sdeexe93
export SDEHOME
PATH=$PATH:$SDEHOME/bin
export PATH
LD_LIBRARY_PATH=/usr/local/sde/sdeexe93/lib
export LD_LIBRARY_PATH
</pre>

===PostgreSQL data dir===
<pre>
mkdir /usr/local/pgsql/data
chown -R /usr/local/pgsql/data
</pre>


==Software==
* PostgreSQL 8.3.7
*: compiled with <tt>configure --with-perl --with-python</tt>
* Geos 3.1.0
*: compiled with <tt>configure --enable-python --enable-ruby</tt> (not that I'll actually use ruby!)
* Proj.4 4.6.1
*: standard build
* PostGIS 1.3.5
*: compiled with <tt>configure --with-geos --with-pgsql --with-proj --prefix=/usr/local/pgsql</tt>
* ArcSDE 9.3


==PostgreSQL stuff==
===pg_hba.conf contents===
<pre>
# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
host    all         all         128.95.140.165/32          password
host    all         all         128.95.140.149/32          password
host    all         all         128.95.140.144/32          password
host    all         all         140.142.198.178/32          password
host    all         all         140.142.198.123/32          trust
host    all     all             192.168.0.0/24  md5
# IPv6 local connections:
#host    all         all         ::1/128               trust
</pre>

===users===
* createuser sde
* createuser phurvitz


==PostGIS stuff==

copy <tt> /usr/local/lib/libgeos-3.1.0.so</tt> to <tt>/usr/local/pgsql/lib/libgeos-3.1.0.so</tt>
===Installing PostGIS basics to the template database===
Assures that new databases will be populated with PostGIS functionality
* createlang plpgsql template1
* psql -d template1 -f /usr/local/src/postgis-1.3.5/lwpostgis.sql
* psql -d template1 -f /usr/local/src/postgis-1.3.5/spatial_ref_sys.sql

===ArcSDE schema===
While logged in as postgres
<pre>
psql
CREATE SCHEMA sde AUTHORIZATION sde;
GRANT ALL ON SCHEMA sde to sde;
GRANT USAGE ON SCHEMA sde TO public;
</pre>

Created a new database:
<pre>create database msb owner phurvitz;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.geometry_columns TO sde;
</pre>

===msbdata user===
Created in order to have a PostgreSQL user with no special privelege

==ArcSDE stuff==
===Authorization key===
Get an authorization key from Luke, save as '''<tt>/usr/local/sde/ArcGIS_Server_v93.ecp</tt>'''


===sdesetup for a database===
* as sde user:
*: sdesetup -o install -d POSTGRESQL -D msb
*: sdesetup -o update_key -d POSTGRESQL -l /usr/local/sde/ArcGIS_Server_v93.ecp  -D msb

'''NOTE:''' do not perform the sdesetup routine on the template1 database!

===Start the sde service===
sdemon -o start -i esri_sde


===Adding layers===
# Create a PostGIS table using standard methods, e.g., 
#:<tt>
#:CREATE TABLE gtest ( ID int4, NAME varchar(20) );
#:SELECT AddGeometryColumn(’sde’, ’gtest’,’shape’,2926,’LINESTRING’,2);
#:</tt>
# Register this as a layer in ArcSDE
#: <tt>sdelayer -o register -l s01_line,shape -D msb -e l3M -C id -k PG_GEOMETRY -t PG_GEOMETRY -G 2926 -u msbdata -p msbdata
#: sdetable -o alter_reg -t s01_line -V multi -D msb -u msbdata -p msbdata -q -N</tt>


==Linux stuff==
Might need to open port 5151 if there is an iptables firewall:

/etc/sysconfig/iptables
<pre>
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 5151 -j ACCEPT
-A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 5151 -j ACCEPT
</pre>