idea Improved Coded Value Domain Sorting Using SQL Statements in Data Management Ideas

Improved Coded Value Domain Sorting Using SQL Statements

ZacharyOrdo__GISP — Thu, 25 Aug 2022 22:35:47 GMT

I have a coded value domain that is called fcDiameter, and it looks like this:

1 - 1
2 - 2
102375 - 2.375
48 - 48
42 - 42
0 - Unknown
100500 - 1.500
-1 - Unknown (Verified)

I would like to sort by description with number-like descriptions sorted numerically followed by text descriptions. You should add functionality to coded value domain management allowing for SQL statements to be used for changing the order where sorting by code or by description ASC or DSC alone is not providing the desired results.

For example, if I were to query the domain table in Oracle, a select query that would return the desired order would look like this:

SELECT * FROM CVD_VW_FCDIAMETER ORDER BY REGEXP_SUBSTR(DESCRIPTION, '^\D*') NULLS FIRST, TO_NUMBER(REGEXP_SUBSTR(DESCRIPTION, '\d+'))

And the results of the query would look like this:

1 - 1
100500 - 1.500
2 - 2
1002375 - 2.375
42 - 42
48 - 48
0 - Unknown
-1 - Unknown (Verified)

Re: Improved Coded Value Domain Sorting Using SQL Statements

cspielman_bouldercounty — Fri, 23 Feb 2024 04:59:42 GMT

I would like a sort order field added to the domain table so that I can specify what should be on top to meet the needs of the day.

Re: Improved Coded Value Domain Sorting Using SQL Statements

Bud — Fri, 23 Feb 2024 10:21:51 GMT

@ZacharyOrdo__GISP

I wonder, as a last resort, if you could use some sort of scheduled/nightly Python script to:

Export the domain to an Oracle or Mobile Geodatabase table using Domain to Table.
An existing database view or query layer (EGDB only) could sort the domain using your SQL query.
Delete the domain values via Delete Coded Value From Domain.
Replace the domain values via Add Coded Value To Domain using the rows in your database view.

I haven’t tested replacing domain values. I’m not sure if/how that would work. Maybe @MarceloMarques would have some insight.

Re: Improved Coded Value Domain Sorting Using SQL Statements

BillFox — Fri, 23 Feb 2024 16:15:54 GMT

I think the esri domain concept used to me implemented using database check constraints. I'm not sure if that is still the case.

I think the domain might complain if feature classes are already pointing to it.