‘Real’ type fields lose scale in ArcGIS

That Pesky Decimal Problem: Why Your Numbers Might Be Lying to You in ArcGIS

Ever feel like your GIS data is playing tricks on you? I’ve been there. One of the sneakiest issues I’ve run into – and one that can really mess with your analysis – is when ‘Real’ type fields from SQL Server start losing their decimal places in ArcGIS. It’s like they’re slowly vanishing, taking your precision with them! And trust me, nobody wants inaccurate GIS.

Decoding the Number Soup: Data Types 101

ArcGIS juggles a bunch of different data types for storing numbers, each with its own quirks. Think of them as different-sized buckets for holding your data. The main contenders in our story are:

• Float (Single-Precision): This is your standard 32-bit number-with-decimals option. Okay for some things, but floats in file and personal geodatabases? They only hold about 6 digits precisely.
• Double (Double-Precision): The big kahuna. A 64-bit data type that gives you way more room to play with – up to 15 digits of precision.
• Real (SQL Server): In SQL Server, ‘Real’ is a floating-point type. Seems simple enough, right?

The Scale-Stealing Gremlin: What’s Going Wrong?

Here’s where things get dicey. Imagine you’re pulling data from a SQL Server table into ArcGIS using a query layer. Everything looks fine, but those ‘Real’ fields? They might not be showing the full picture. ArcGIS sometimes struggles to accurately represent the scale (those all-important decimal places) defined in SQL Server.

I’ve seen it happen firsthand. A value of 123.4567 in SQL Server suddenly becomes 123.457 or even just 123 in ArcGIS. Talk about a data downgrade! The exact behavior depends on a few things, but the bottom line is: you’re losing precision.

Why the Mix-Up? Blame the Translation

The problem boils down to how ArcGIS interprets the ‘Real’ data type coming from SQL Server. It’s like trying to translate between two different languages – sometimes, things get lost in translation. ArcGIS has its own rules for handling precision, and they don’t always line up perfectly with SQL Server’s way of doing things. File and mobile geodatabases? They don’t even show precision or scale settings because you can’t set them!

The Quick Fix: A Little SQL Magic

Don’t despair! There’s a pretty easy fix. Inside your query layer definition, use the CAST function in SQL to explicitly convert that ‘Real’ field into a ‘Float’. Like this:

sql