[postgis-users] ESRI Geodatabases, relationship classes and PostGIS

[Lee Hachadoorian]

Juan,

You should have a table called GDB_RelClasses, which has columns named
(among others) OriginPrimaryKey, DestPrimaryKey, OriginForeignKey,
DestForeignKey.  In theory, you could iterate this table and create
the SQL for the necessary foreign key constraints.  The payoff may be
slight if you only need to do this once, but at least if you open
GDB_RelClasses and print it out you'll have all your relationships
listed in one place to refer to as you recreate them in Postgres.  (It
beats opening the properties dialog for each relationship class in
ArcCatalog.)

The SQL might (not tested) look something like:

ALTER TABLE destination_table ADD CONSTRAINT constraint_name_fkey
FOREIGN KEY column_name REFERENCES origin_table (id_column)

I don't see anything in the ESRI documentation that documents the
structure (other than field names) of GDB_RelClasses, so unless you
find something, it will take some legwork to figure it out.  As an
example, I created a one-to-many relationship, and this appears as
Cardinality=2.  The SQL would be as above.  For a one-to-one
relationship (Cardinality=1?), you would have to make the foreign key
column in destination_table be a primary key as well.  If the
relationship is composite (IsComposite=1 in GDB_RelClasses), you will
need to use the ON DELETE CASCADE phrase.

An important question for those who know more about PostGIS than me is
whether there's is anything like the spatial update behavior built
into composite relationships.  For a composite relationship, if the
origin feature is moved or rotated, the destination feature will be
moved or rotated as well.  Is there a way for Juan to implement this
in PostGIS (assuming he needs it)?

Info on foreign keys in Postgres:
http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK

-- 
Lee Hachadoorian
PhD Student, Geography
Program in Earth & Environmental Sciences
CUNY Graduate Center