postgre enterprise SDE and arcmap - why do they open as query tables?

2825
6
06-01-2016 06:47 AM
LaurenYee
Occasional Contributor

Hi, I've set up postgre /postgis and enterprise SDE. An example of my sql for setting up a table follows:

CREATE TABLE bird_sighting (

    bird_id integer PRIMARY KEY,

    field_id integer REFERENCES field(field_ID),

    birdev_id integer REFERENCES bird_event(birdev_id),

    sight_date date NOT NULL CHECK(sight_date > '2016-05-01'),

    activity character varying (30),

    rel_location character varying (20),

    species character varying (40),

    geom geometry(Point, 26917) NOT NULL

    );

When I open this feature class in ArcMap it opens up the "neq query layer" dialogue:

Is this correct? I thought that if I already defined the SRID and geometry type that this should already be populated?
Can someone explain to me why arcmap does this, or perhaps what I am doing wrong?

Thanks

0 Kudos
6 Replies
George_Thompson
Esri Frequent Contributor

Is the table registered with the Geodatabase?

If you created within the PostgreSQL database and not via ArcGIS tools, then it is an unregistered table. That behavior would be expected with the pop up window and it appearing as a query layer..

PostgreSQL data types supported in ArcGIS—Help | ArcGIS for Desktop

Registering a table with the geodatabase—ArcGIS Help | ArcGIS for Desktop

What is a query layer?—Help | ArcGIS for Desktop

Geodatabase

--- George T.
0 Kudos
LaurenYee
Occasional Contributor

I used the "Create enterprise geodatabase tool" to create a DB in postgre. Then I used SQL to create my tables. In ArcCatalogue it won't let me register the feature classes or tables created in postgresql.  Is the geometry field created above okay?  We are also looking into the possibility that it seems the tables/features created in postgre are under the "public" schema not "sde".

0 Kudos
George_Thompson
Esri Frequent Contributor

The location of the data may be the issue, if it is in the SDE schema it should work. Be sure to review the documentation related to PostgreSQL and ArcGIS for more information.

You may want to open an Esri Technical Support​ case to have them review this in more detail that we can in GeoNet.

--- George T.
0 Kudos
LaurenYee
Occasional Contributor

I have reviewed the documentation and followed all of the steps, so I am currently at a loss as to what the issue is.

0 Kudos
AlexanderBrown5
Occasional Contributor II

Lauren,

Can you successfully view the table in ArcCatalog's Catalog tree?

~Alex

0 Kudos
VinceAngelo
Esri Esteemed Contributor

This is the expected behavior.  Tables created via SQL are not registered (until you use sdelayer -o register).

I avoid the public schema, because data in public can't be registered (only the owner can register, and only in a schema that matches the login). Using the public schema for anything beyond granting general SELECT access also goes against best security practice.  No user tables should ever be created in the sde schema -- 'sde' should be reserved for geodatabase administration tasks alone.

- V

0 Kudos