How can I connect to a PostGIS database from ArcCatalog 9.3 via ArcSDE?

Question

I have a problem connecting to my postgis database via ArcSDE in Arc Catalog. When i want to establish connection to my database i get an error:

Answer 1

The first thing I would do would be to check your permissions. I would check it by trying to login to the DB via pgAdmin using the same account from ArcCatalog. Once you login, you should also try running a select query on one of your datasets to verify you can view it. Additionally are you on the same machine as your postgis DB? If not, verify that postgresql is set up to receive remote connections (check pg_hba.conf). If everything looks good, try posting your connection string so we can have a look. It could be malformed.

Update 1 (8/17/10)

Several Questions:

When you created the database did you do the ArcSDE post-install?

What schemas are listed in your postgresql db?

Update 2 (8/17/10)

How did you load your data into postgresql? One of your post has the boundaries in the public schema. That is probably not correct.

What user was used to load data into postgresql?

Update 3 (8/21/10)

I hate to say this but you really need to read the docs for ArcSDE and postgresql. Your data as you have it loaded will only work for postgis. Although you used the post install for ArcSDE, it does not appear you did all the needed followon steps, such as creating schemas and users. Although there are several steps, they are not difficult. Read the docs and then recreate your data.

Answer 2

seconding wilsongis... Granting users rights to create tables with PostGIS geometry type columns

When a database is enabled for PostGIS, a table—geometry_columns—is created in the public schema. You must grant, at a minimum, SELECT, INSERT, UPDATE, and DELETE privileges on this table to any users who will create data in the geodatabase.

GRANT select, insert, update, delete ON TABLE public.geometry_columns TO

helpful 9.3 link http://webhelp.esri.com/arcgisserver/9.3/dotNet/index.htm#geodatabases/using_th485132809.htm

Answer 3

I believe that to have it show up in ArcCatalog, you need to register the table as a feature class. See this page, near the bottom:

http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/using_th485132809.htm

Your commandline command should be something like this:

sdelayer –o register –l properties,shape –e p –C fid,SDE –u <user> –p <pw>

Answer 4

I have done eveythink like both of you suggested. Unfortunately it didn't help...I am little bit confused because I can connect to my db via Quantum Gis but i cannot with ArcCatalog. I have no idea why it is like that. Anyway thank's for your help.

Answer 5

Sorry for answering but i cannot add a screen in "add commend"

Ad. For SDE to recognize that your data set is a 'feature class', you have to register it with the database. This is commonly done using the commandline tool 'sdelayer'. There is a set of commandline tools that are part of the SDE install that are used to monitor, stop, start, maintain the SDE instance. You can find a reference to these commands here resources.esri.com/help/9.3/geodatabase/pdf/admincmd.pdf Right now, SDE is seeing your spatial data set as a table of records, not a feature class. – DavidF 31 secs ago

Ok i would try to do that (later i will post if i can manage). But now i have even a problem with seeing properties and the content of a table so i think somethink is wrong...it looks like that:

relacja "public.boundaries nie istenieje means relation "public.boundaries" does not exist.

I can only add that i can see one table and another one i cannot.

Answer 6

Answering your edited post (wilsongis):

1. I was not a creator of a database and i do not know when it was created but i have done the ArcSDE post-install

2. This is how it looks like (schemas) in pgAdmin III

3. I am loading my data via pgAdmin III

4. The user used to load data is: postgres