topic Re: Enterprise geodatabase domains from source table are not applied to view in ArcGIS Enterprise Questions

Enterprise geodatabase domains from source table are not applied to view

GreenCollarGroup — Thu, 29 Feb 2024 00:39:51 GMT

Enterprise geodatabase domains from source table are not applied to view.

I apply something like the following query using the 'create database view' tool.

SELECT t2.OBJECTID, t2.SHAPE, t2.attr, t2.type, t1.attr1, t3.attr2, t2.area_ha
FROM gis_project.sde.table1 t1
INNER JOIN gis_project.sde.table2 t2 ON t1.table1_id = t2.table1_id
INNER JOIN gis_project.sde.table3 t3 ON t2.table2_id = t3.table2_id

t2.type contains integers and the domain maps 0 = 'some text' and so on. The source table retains the domain but resulting view shows the integers.

I am going to see if I can create the view or the lookup reference in PgAdmin, but I would prefer to use esri tools.

ArcGIS Pro 3.2.0, PostgreSQL 15, PostGIS 3.4.1

Re: Enterprise geodatabase domains from source table are not applied to view

GreenCollarGroup — Thu, 29 Feb 2024 02:41:30 GMT

I would still much prefer Esri tools but I have found a work around using PgAdmin.

Process modified from (https://www.the-art-of-web.com/sql/lookup-table/). I manually create a lookup table and set constraints on the original table. Something like this:

INSERT INTO lookup_table (name) SELECT DISTINCT type FROM table1;

UPDATE gisowner.lookup_table

SET description='string_value1'

WHERE name='0';

UPDATE gisowner.lookup_table

SET description='string_value2'

WHERE name!='0';

ALTER TABLE table1 ADD COLUMN typeid int;

UPDATE table1 SET typeid=lookup_table.id FROM lookup_table

WHERE table1.type=lookup_table.name;

ALTER TABLE table1 ALTER typeid SET NOT NULL;

ALTER TABLE table1 ADD CONSTRAINT "table1_typeid_fkey"

FOREIGN KEY (typeid) REFERENCES lookup_table(id);

ALTER TABLE table1 DROP COLUMN type;

SELECT * FROM gisowner.table1 LIMIT 20;

Once this is done I applied the lookup in the view definition:
SELECT t2.OBJECTID, t2.SHAPE, t2.attr, t2.type, t1.attr1, t3.attr2, t2.area_ha
FROM naperville.gisowner.table1 t1
INNER JOIN naperville.gisowner.table2 t2 ON t1.table1_id = t2.table1_id
INNER JOIN naperville.gisowner.table3 t3 ON t2.table2_id = t3.table2_id
JOIN naperville.gisowner.lookup_table lt ON t2.typeid = lt.id

I really hope there is a more straight forward approach using Pro...

Re: Enterprise geodatabase domains from source table are not applied to view

bledos_cergypontoise — Wed, 02 Oct 2024 10:01:06 GMT

It worked very well before!!!

Bug?? 🤔