CivicPlus Asset Management maintains data integrity during synchronization by aligning Esri feature-service field types with their equivalent SQL Server data types.
In most deployments, the Esri GIS serves as the authoritative system of record. During implementation, Asset Management SQL data structures are adjusted to mirror the Esri feature-service schema, field names, data types, and domain mappings, to ensure fidelity during synchronization and data migration.
Field Type Mapping
Esri Field Type | Description | Recommended SQL Server Data Type | Notes and Considerations |
|---|---|---|---|
Integer (Short) | 16-bit integer | smallint | Range -32 768 to 32 767; used for small coded values |
Integer (Long) | 32-but integer | int | Common for IDs, domain codes, or asset classifications |
Float | Single-precision float | real | May lose precision beyond 6 digits. |
Double | Double-precision float | float | Preferred for coordinates and continuous values. |
String (Text) | Variable-length text | nvarchar(length) | Match field length (Esri max 255). Use nvarchar(max) for long descriptions. |
Date | Date and time stamp | datetime2(7) | Use UTC or ISO 8601 format for consistency. |
GUID / GlobalID | Unique identifier for features | uniqueidentifier | Required for all synchronized layers. |
OID / ObjectID | Esri system-managed feature ID | int | Managed by Esri; not synced directly. |
Boolean (Yes/No) | Binary true/false value | bit | Aligns natively; avoid NULLs if the field is required. |
BLOB | Binary large objects, such as attachments or images | varbinary(max) | Currently not supported for synchronization. Attachments or image references, such as photos in pop-ups, remain accessible through the Esri-hosted services or URLs. |
Raster / shape | Geometry or geography data | geometry | Stored as WKB (Well-Known Binary) through Esri conversion; not editable directly in SQL. |
Implementation Notes
CivicPlus Asset Management treats the GIS environment as the authoritative source for asset and spatial data.
SQL data structures within Asset Management are modified as needed to match the Esri feature-service schema and prevent data loss or type coercion.
This alignment includes adjusting receiving SQL columns for length, precision, or data type to mirror the GIS source.
Domain-coded fields map to lookup tables during integration setup to maintain code-description parity.
GlobalIDs must exist in all synchronized feature layers to enable reliable inserts, updates, and deletions.
Date fields should use datetime2(7) to preserve sub-second precision and avoid rounding during sync operations.
Attachments and BLOBs (Binary Large Objects) are excluded from synchronization; these assets remain available through the client’s Esri-hosted services for reference or display.
Common Migration and Normalization Tasks
Task | Purpose or Benefit |
|---|---|
Remove trailing spaces in text fields | Prevents false mismatches during lookup alignment |
Replace NULL numeric values with defaults | Ensures insert/update operations do not fail on non-nullable columns |
Standardize date formats to ISO 8601: YYYY-MM-DD hh:mm:ss | Maintains temporal consistency across time zones and services |
Verifying string lengths are less than or equal to 255 characters | Prevents truncation when migration nvarchar(255) columns |
Confirm domain codes and descriptions align with lookup sets | Prevents NULL domain values and sync validation errors |
Validate GlobalID presence on all feature classes | Ensures stable linkage between GIS features and Asset Management records |