idea Environment setting to default Integer to Long not BigInteger in ArcGIS Pro Ideas

Environment setting to default Integer to Long not BigInteger

KimOllivier — Thu, 23 May 2024 22:40:08 GMT

When importing data from geopackage or sqlite any field defined as Integer in the DDL gets cast to BigInteger instead of (Long) Integer. This is a new incompatible behaviour since BigIntegers were included (ArcPro 3.2?). There are several tools that do not work with BigIntegers such as RelationshipClasses.

It would be helpful if there was a switch to turn off this unwelcome enhancement. There is a switch in Options/Map and Scene - but it doesn't work.

The only way to fix the problem is to define a FieldMapping on every copy operation and there are many of these that do not have FieldMapping as a parameter.

If the field is readonly such as OBJECTID then it cannot be changed at all. I see another user has used FME to fix this.

This is like a reverse single precision / double precision incompatibility!

Maybe a switch somewhere in the settings or the environment settings to retrofit a fix?

Re: Environment setting to default Integer to Long not BigInteger

BruceHarold — Thu, 22 Feb 2024 14:22:44 GMT

Thanks Kimo I'll get this to the right team.

Re: Environment setting to default Integer to Long not BigInteger

Ranga_Tolapi — Thu, 07 Mar 2024 14:24:08 GMT

Completely agree @KimOllivier

In ArcGIS Pro 3.2, at least tools like Copy Features, Feature class to Geodatabase, etc... should honor the version of the pre-created target File Geodatabase.

If the pre-created target File Geodatabase version is 10.0 or below then data conversion/import tools should not introduce the new features of ArcGIS Pro 3.2 e.g., 64-bit OID, Big integer, Timestamp offset, Date only, Time only.

@SirishByreddy

Re: Environment setting to default Integer to Long not BigInteger

KoryKramer — Fri, 15 Mar 2024 16:14:13 GMT

Related is this bug that is currently being investigated: https://support.esri.com/en-us/bug/geopackage-feature-class-objectids-are-read-as-64bit-in-bug-000164015

Re: Environment setting to default Integer to Long not BigInteger

MelanieWawryk — Mon, 06 May 2024 23:09:33 GMT

ArcPro and AGOL also do not work well with Big integer. We don't have anything that is a big integer and only use long. If you look at the layer in ArcCatelog it shows as long integer but the data design view of the same layer shows it as big integer. If we export to AGOL it gives a warning message about big integer and sure enough, it is big integer in AGOL. I guess the bug is in ArcPro 3.2.2. Please fix

Re: Environment setting to default Integer to Long not BigInteger

KimOllivier — Sat, 02 Nov 2024 23:04:02 GMT

Not fixed in 3.3. Ah well, back to 3.1 so that I can attempt to port workflows from ArcMap!

There is a setting in Options/Map and Scene - but it doesn't work for me. [Edit: it casts BigInteger to Double, not Long so cannot be used as a key]

Re: Environment setting to default Integer to Long not BigInteger

DannyMac — Thu, 05 Sep 2024 20:03:38 GMT

@KimOllivier

After delving deeper into the issue of INTEGER vs BIGINT, I discovered the following information. ArcGIS Pro didn't support 64-bit integers until ArcGIS Pro 3.2. It's important to note that GeoPackage uses SQLite under the covers, and INT and INTEGER datatypes in SQLite have always been 64-bit. Consequently, with ArcGIS Pro 3.2 now supporting 64-bit integers using BIGINT and considering that an INTEGER is 64-bit in a GeoPackage, the columns are cast to BIGINT. From a technical standpoint, this behavior is correct, which could argue against it being classified as a bug. However, I believe a more effective approach would involve inspecting the column values before automatically casting them to BIGINT. For instance, ArcGIS could examine the values for all INTEGER columns first to determine if casting to BIGINT is necessary.

Here's an example query:

SELECT * FROM MyRoads
WHERE MyRoads.LINK_ID < -2147483648
OR MyRoads.LINK_ID > 2147483647;

If the query returns nothing, then casting to INTEGER would be more appropriate than using BIGINT.

Re: Environment setting to default Integer to Long not BigInteger

KimOllivier — Sat, 02 Nov 2024 23:02:52 GMT

Thanks for that explanation. It does show why it is happening. But the option switch says "to use field types that are compatible with ArcGISPro 3.1", which are long integer. So even if it is correct to move 64 bit fields in Sqlite to BigInteger they do not work in Pro for many functions if they are cast that way. This must be a problem with other databases in Enterprise too. I suspect they had a very narrow view of this problem when implementing the option.

All I am asking for is for the switch to work everywhere. [Edit. It only works inside ArcGISPro, you can use the switch in arcpy.env.useCompatibleFieldTypes = True when running scripts outside Pro. But a cast to Double is not useful for me.]

Re: Environment setting to default Integer to Long not BigInteger

DannyMac — Fri, 06 Sep 2024 15:37:03 GMT

@KimOllivier I currently have an enterprise support ticket open for this issue, and I've referenced your post in my ticket to ensure that they are aware that I'm not the only one affected by this unexpected change. For us, it means we either need to update all of our application code to utilize BigInt, or we can modify all the columns after importing features and change them back to Integer. At the moment, we are choosing the latter option because none of the columns in our database would require a BigInt data type.

Re: Environment setting to default Integer to Long not BigInteger

Marshal — Wed, 02 Oct 2024 16:53:29 GMT

Just to chime in here. It appears BigInt is not a compatible sort_field in tools such as ExportFeatures and PointsToLine (only tested in arcpy), which is problematic for many of our script tools. Hopefully this is resolved quickly.

ERROR 003911: The field is not of type SHORT | LONG | FLOAT | DOUBLE | TEXT | DATE | XML | OBJECTID

Re: Environment setting to default Integer to Long not BigInteger

KimOllivier — Sat, 02 Nov 2024 23:19:33 GMT

Even if the policy is correctly set for casting the ambiguous Integer type I still need a solution that works for me. I can see that computing has evolved from 16 to 32 to 64 bit integers and some are defined as signed and unsigned. I have seen LONG, MEDINT, INTEGER and SHORT as well as the new BigInteger to try to extend the meaning of 'LONG' which in 32 bit days meant only 32 bits. To keep backward compatibility the temptation is to reuse 'Long' for 64 bit when using a 64 bit compiler.

I was interested to see that MEDINT was interpreted as Long and INTEGER as BigInteger going from a gpkg to a filegeodatabase. So redefining the gpkg field type might be a good workaround that avoids having to explicitly cast the fieldtype using a fieldmappings parameter with ExportFeatures_conversion() replacing CopyFeatures(). [note that sqlite does not care what you call it in the DDL, they are always 64 bit integers internally]

The small print in the help also explains why the option setting in Options in ArcGISPro does not work for me. Because if running a script outside ArcGISPro in the command line you have to set the options in the arcpy environment. arcpy.env. But Double is useless for me. I want to use the field as an indexed key so it has to be some sort of integer. Insert below:

useCompatibleFieldTypes

(Read and Write)

Specifies whether field types that are compatible with ArcGIS Pro 3.1 will be used. This setting relates to the use of the Date Only, Time Only, Timestamp Offset, and Big Integer field types in CSV tables and unregistered database tables with tools that create layers or table views.

When set to True, a layer or table view created from these sources will use field types compatible with ArcGIS Pro 3.1. Date Only, Time Only, and Timestamp Offset fields will be displayed as Date fields, and Big Integer fields will be displayed as Double fields. When set to False, all original data source field types will be used.

Note:

This property is applicable when used from stand-alone Python or for a geoprocessing service. When used in ArcGIS Pro, this property will always match the Use field types that are compatible with ArcGIS Pro 3.1 and earlier releases when adding query layers and text files option.

A better option I have implemented is to replace CopyFeatures_management() with my own function CopyFeaturesLong() that looks for any BigInteger arcpy interpretations and keeps them as a compatible Integer, not a Double. Here it is if you want to use it. There will need to be a similar function to replace CopyRows().

def CopyFeaturesLong(input_fc, output_fc): """CopyFeatures replacement to ensure every BigInteger is mapped to Long with a fieldmappings parameter using arcpy.conversion.ExportFeatures() with a bit of help from copilot! full paths to featureclasses assumed """ # Create FieldMappings and FieldMap objects field_mappings = arcpy.FieldMappings() # List of fields to process # it would be nice if objectid and shape could be excluded easily fields = arcpy.ListFields(input_fc) for field in fields: if field.type not in ('OID', 'Geometry'): # not allowed in fieldmappings field_map = arcpy.FieldMap() # new for each field field_map.addInputField(input_fc, field.name) # Check if the field type is BigInteger and change to Long field_name = field_map.outputField if field_name.type == 'BigInteger': field_name.type = 'Long' field_map.outputField = field_name # Add the FieldMap to the FieldMappings object field_mappings.addFieldMap(field_map) # Use ExportFeatures to apply the field mappings and create the output feature class arcpy.conversion.ExportFeatures(input_fc, output_fc, field_mapping=field_mappings) if debug: for fld in arcpy.ListFields(output_fc): print(fld.name,fld.type) print(f"{output_fc} Feature class successfully exported with updated field types!") return True def CopyRowsLong(input_tab, output_tab): """CopyRows replacement to ensure every BigInteger is mapped to Long with a fieldmappings parameter using arcpy.conversion.ExportTable() with a bit of help from copilot! full paths to featureclasses assumed """ # Create FieldMappings and FieldMap objects field_mappings = arcpy.FieldMappings() # List of fields to process # it would be nice if objectid and shape could be excluded easily fields = arcpy.ListFields(input_tab) for field in fields: if field.type not in ('OID', 'Geometry'): # not allowed in fieldmappings field_map = arcpy.FieldMap() # new for each field field_map.addInputField(input_tab, field.name) # Check if the field type is BigInteger and change to Long field_name = field_map.outputField if field_name.type == 'BigInteger': field_name.type = 'Long' field_map.outputField = field_name # Add the FieldMap to the FieldMappings object field_mappings.addFieldMap(field_map) # Use ExportFeatures to apply the field mappings and create the output feature class arcpy.conversion.ExportTable(input_tab, output_tab, field_mapping=field_mappings) if debug: for fld in arcpy.ListFields(output_tab): print(fld.name,fld.type) print(f"{output_tab} Table class successfully exported with updated field types!") return True

Re: Environment setting to default Integer to Long not BigInteger

KimOllivier — Tue, 03 Dec 2024 23:35:02 GMT

You may need to fix a bug(?) that adds a blank field to the end of the arcpy.ListFields(fc) command.

fields = arcpy.ListFields(input_fc) print(f"L42 {[f.name for f in fields]}") for field in fields: if field.type not in ('OID', 'Geometry'): # not allowed in fieldmappings if field.name: # very strange blank name added to list by Esri field_map = arcpy.FieldMap() # new for each field print(f"L46 {input_fc},<{field.name}>")

Re: Environment setting to default Integer to Long not BigInteger

KimOllivier — Fri, 04 Apr 2025 21:34:13 GMT

But there is another problem not covered by my workaround. OBJECTID can be 32 or 64 bit as well. This is much harder to change and even detect. The type is "ÓID" for both (not helpful) but you can find out the length is 4 or 8.

If the table is empty you can edit the field schema by hand to fix it, but I don't know how to do it in a script.