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

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:

-

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.

-
1. I have checked permissions 2. Same machine 3. What do you mean by posting my connection string (I am quite new user of ArcGis Server and Postges database...) PS. I will be away this weekend- go for hunting so i won't be posting. Have a nice weekend for you :) – com Aug 13 '10 at 16:03
Are you connecting via a service or a direct connect? – wilsongis Aug 13 '10 at 16:09

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

-
I was just about to add this comment – wilsongis Aug 13 '10 at 16:12
Sample sql statements are located at something like "<SDE INSTALL DIR>\ArcSDE\pgexe\tools\postgres\roles_schemas_privileges.sql". I usually edit this file and reuse every time I create a DB. – wilsongis Aug 13 '10 at 16:15

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>

-
Sorry for maybe stupid question but what do you mean by "commandline command"? You mean that one in psql? – com Aug 17 '10 at 8:25
Now i can connect by "Add OLE DB Connection" in ArcCatalog but i can see only a tables in my database. When i want to preview shapes (or in ArcMap by adding data) it is the same problem...And still connection by "Add spatial database connection" is not possible... – com Aug 17 '10 at 9:07
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 Aug 17 '10 at 14:01

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.

-

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.

-
It appears to me that you have your data loaded incorrectly for it to be an SDE-postgresql database. The data should not be in the Public schema. See my edited post above. – wilsongis Aug 17 '10 at 14:58