Enterprise geodatabase domains from source table are not applied to view

184
1
02-28-2024 04:39 PM
GreenCollarGroup
New Contributor

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

0 Kudos
1 Reply
GreenCollarGroup
New Contributor

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...
0 Kudos