topic Re: postgre enterprise SDE and arcmap - why do they open as query tables? in ArcGIS Enterprise Questions

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

LaurenYee — Sun, 12 Dec 2021 02:39:57 GMT

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

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

George_Thompson — Wed, 01 Jun 2016 14:30:00 GMT

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

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

LaurenYee — Thu, 02 Jun 2016 13:58:28 GMT

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".

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

George_Thompson — Thu, 02 Jun 2016 14:05:08 GMT

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.

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

LaurenYee — Thu, 02 Jun 2016 16:17:51 GMT

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

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

AlexanderBrown5 — Tue, 28 Jun 2016 18:35:30 GMT

Lauren,

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

~Alex

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

VinceAngelo — Tue, 28 Jun 2016 19:45:04 GMT

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