Arcade Expression using Distinct fails in SDE, not File GDB

DavidColey — Tue, 10 Sep 2024 17:17:54 GMT

Hello - I am putting together a relatively simple Arcade script to eventually function as an attribute rule in order to create an identifier in a building footprint polygon by using a combination of an address number and unit number from address point layer.

The following works just fine in a file geodatabase, where $feature is my building footprint layer

var Address = FeatureSetByName($datastore, 'TRANS.AddressPoints', ['AddNumber', 'UnitNumber'],false); //, 'bldgname' var intAddress = Intersects($feature, Address); //, 'bldgname' var addNums = Distinct(intAddress, ['AddNumber']); // always 1 or gt 1 var untNums = Distinct(intAddress , ['UnitNumber']); // always 1 or gt 1, when 1 just use add number var addCnt = Count(AddNums); var untCnt = Count(untNums); Console(addCnt); Console(untCnt);

But when I run this against our current SQL2022 SDE instance to test a calculation in the Field Calculator (upgraded with the latest Pro 3.3.1 internals), it can't get past line 7, because it can't return a DISTINCT"

Invalid expression.
Error on line 7.Underlying DBMS error [[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The geometry data type cannot be selected as DISTINCT because it is not comparable.] [TRANS.AddressPoints][STATE_ID = 0]

I have to have a distinct array of values for both address number and unit number because they can repeat within a given footprint. Any ideas?

@KenBuja ?

@rlyding

Re: Arcade Expression using Distinct fails in SDE, not File GDB

DavidColey — Tue, 10 Sep 2024 20:16:19 GMT

Ok so for whatever reason, the DISTINCT function is a constraint when trying to operate against SQL geometry. Working with my collegue, this works against an SDE layer:

var Address = FeatureSetByName($datastore, 'TRANS.AddressPoints', ['AddNumber', 'UnitNumber'],false); var intAddress = Intersects($feature, Address); //, 'bldgname' var addNums = []; for (var i in intAddress){ //console(i); Push(addNums, i['AddNumber']); } addNums = Sort(Distinct(addNums)); console(addNums); var addCnt = Count(addNums); Console(addCnt);

By setting up an array first to store the Intersects return, and then passing the initial array into a sorted array, the DISTINCT function works or rather validates in the field calculator.

So I think I basically answered my own question.

@mstranovsky

@rlyding

@KenBuja

topic Arcade Expression using Distinct fails in SDE, not File GDB in Attribute Rules Questions

Arcade Expression using Distinct fails in SDE, not File GDB

Re: Arcade Expression using Distinct fails in SDE, not File GDB