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

[Paragon Corporation]

Yes you can delete the Geometry field.  One thing I find annoying about
OGR2OGR is that it insists on creating a geometry field for attribute only
tables.  So yes I usuall use the 
 
SELECT DropGeometryColumn postgis function to drop the bogus field which
also removes it from the geometry_columns table
 
Try something like this (note I haven't tried this particular variant - it
may not work since this query is actually deleting the records it is
querying)
 
1) 
SELECT DropGeometryColumn(f_table_schema, f_table_name, f_geometry_column)
FROM geometry_columns
WHERE type = 'GEOMETRY';

2) The other annoying thing about OGR2OGR is that its choice of datatypes is
often annoying.  To fix the below - you can try the trick we have documented
here

http://www.postgresonline.com/journal/index.php?/archives/30-DML-to-generate
-DDL-and-DCL--Making-structural-and-Permission-changes-to-multiple-tables.ht
ml

Except your query would be 

ALTER TABLE ...  ALTER OBJECTID TYPE bigint;  (or int)


3) This sounds like a bug in ZigGIS - it is probably assuming you imported
with shp2pgsql which creates a serial field called gid for every table.

Yell at Abe, Paolo, and Bill  for that :)

A work around would be to use trick in 2 and do a

ALTER TABLE ... ADD COLUMN gid serial

OGR2OGR creates a serial field too which defaults to ogc_fid or something
like that so you could do a 

ALTER TABLE .. ALTER ogc_fid RENAME TO gid

(my above syntax may be wrong since I don't use RENAME that often)

Hope that helps,
Regina


________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Juan
Declet
Sent: Wednesday, July 30, 2008 2:27 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ESRI Geodatabases,relationship classes and
PostGIS


Thanks to your input I have been able to input all the ESRI Geodatabase
feature classes and tables. I used the OGR2OGR tool with the parameters
specified by Regina. Note that to append all tables you should omit table
names. 

I am able to display the database tables in ArcMap using zigGIS. However,
when I try to add a feature class, I get the following errors in the zigGIS
Feedback window:

1. Unsupported geometry type GEOMETRY
   I understand this was created by the OGR2OGR tool. Can I just delete it?

2. Unsupported type Decimal on FeatureClassNameORTableName.OBJECTID

This is the most troubling of the error messages. The OBJECTID is,
of course, what ESRI uses as primary key to link the spatial vector table to
the attribute table of a feature class.  If this type is not supported, how
can an ESRI Feature Class work correctly in PostGIS?

3. The FeatureClassNameORTableName.gid field was not found.

No idea on what this means.

Thanks!


On Tue, Jul 29, 2008 at 4:10 PM, Ragi Y. Burhum <ragi at burhum.com> wrote:


	On Tue, Jul 29, 2008 at 12:01 PM,
	<postgis-users-request at postgis.refractions.net> wrote:
	>
	> Message: 20
	> Date: Tue, 29 Jul 2008 09:47:22 -0700
	> From: "Juan Declet" <reguerete at gmail.com>
	
	> Subject: Re: [postgis-users] ESRI Geodatabases, relationship
classes
	>        and PostGIS
	
	> To: "PostGIS Users Discussion"
<postgis-users at postgis.refractions.net>
	> Message-ID:
	>
<c566dff90807290947q48e44807j57efb6e50712d80d at mail.gmail.com>
	> Content-Type: text/plain; charset="iso-8859-1"
	
	>
	> Can anyone suggest a workaround? I am trying to implement
relationship
	> classes from a Personal Geodatabase into PostGIS. What about if I
created
	> the relationship classes natively in the PostGIS database server?
The issue
	> then would be getting ArcMap to recognize the relationship classes
and
	> enforce them via editing. I know this is more of an ArcGIS
question, but am
	> lookin for the input of experienced PostGIS users.
	
	
	You are working with 2 different abstractions... one at the
ArcObjects
	level, and the other one at the RDBMS. Don't get them confused.
	
	If your intent is to simply enforce the cardinality constraint, go
	ahead and create the relationship at the PostgreSQL level. As long
as
	you are doing non-versioning editing, your ArcMap save operation
will
	be done within a transaction... if an RDBMS error is raised (and you
	will get one if you violate the contraint you created) that
	transaction will be rolled back and you will get the RDBMS message
in
	ArcMap. Be mindful that during the edit session you will not see the
	error until ArcMap flushes its edit session (during a save, search,
	etc).
	
	If your intent is to have ArcMap recognize the relationship, for
	example, to be able to navigate it through the object inspector, you
	are going to need to insert the right entries in the GDB_*
	(GDB_Relationships, GDB_ObjectClasses, etc) tables. The easiest bet
is
	to create the relationship through ArcCatalog once and check the
	entries it created in those GDB metadata tables so you know how it
	works.
	

	>
	> On Tue, Jul 29, 2008 at 9:34 AM, Lee Hachadoorian <
	> lee.hachadoorian at gmail.com> wrote:
	>
	>> Yes, ArcCatalog creates a standard Access database table, but
enforces
	>> the creation of a unique index called OBJECTID (which is not,
however,
	>> actually a primary key).
	
	
	Ha ha.. that is debatable :-)
	

	>> So what Regina describes should work.
	>>
	>> There's an ogr2ogr cheatsheet at BostonGIS
	>> (http://www.bostongis.com/?content_name=ogr_cheatsheet).
	>>
	>> I'm not sure about the relationship classes question, although my
	>> suspicion is that the conversion works table-by-table on data
(spatial
	>> and non-spatial) only.  Since the relationship classes are not
stored
	>> in the feature class or non-spatial table itself (they are stored
in a
	>> table called GDB_RelClasses), I suspect that ogr2ogr will not do
	>> anything to convert them.  Can someone confirm?
	>>
	>> --
	
	>> Lee Hachadoorian
	>> PhD Student, Geography
	>> Program in Earth & Environmental Sciences
	>> CUNY Graduate Center
	
	
	My two cents,
	
	- Ragi Yaser Burhum
	
	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net
	http://postgis.refractions.net/mailman/listinfo/postgis-users