[GIS] How to automate joining SQL Server non-spatial database tables to ArcGIS shapefile

attribute-joinsshapefilesql server

I have dozens of SQL Server non-spatial database tables. These are common tables, the column types are: nvarchar, int and float.

I try to use Arcpy to automate joining these database tables to shapefiles. The tables have to be registered in ArcGIS first.
The ArcMap I use is 10.3.1. Unfortunately, ArcMap 10.3 doesn't support "sdetable -o register -t" anymore? I tried a while and couldn't make it work.

So, are there other options to automate joining the tables?

Best Answer

You aren't looking for table join automation as much as table registration automation.

It is not necessary to use sdetable to register tables with ArcSDE because ArcGIS supports a full "Register with Geodatabase" operation (which does far more than what the command-line utility did).

For example, here we have an opened connection:

Catalog

A right-click and navigation through "Manage" leads to "Register with Geodatabase"

Register

(Note that you can only register as the table owner)

Registration requires a new objectid column, so there may be a prompt:

Confirm

though if you already have an "ObjectID" column (INTEGER NOT NULL) -- which is actually the best option to prevent table fragmentation -- you may get a confirmation prompt instead:

use existing

And then the table is registered:

field list

The only effective limitation to geodatabase registration is highlighted in the documentation --

Note: Because ArcGIS cannot add an ObjectID field to a view or update the values in an existing ObjectID field in a view, you cannot register a view with the geodatabase.

and it's possible to use sdelayer -o register to at least register spatial views with ArcSDE, which promotes them to "simple feature class" status (without a Query Layer requirement).

It is also important to note that once a table is registered with ArcGIS, it has an SDE-set rowid column, which impacts the ability to insert rows outside of ArcGIS, and it should not be altered or dropped with SQL tools (at the risk of geodatabase metadata corruption).

This process can also be accomplished via ArcPy through Register with Geodatabase, as described at the end of the command-line migration page:

""" Register third-party tables with the geodatabase """

import arcpy
import sys

def RegisterThirdPartyTables(in_tables):
    """
    Register third-party tables with the geodatabase

    in_tables -- semi-colon delimited string of tables
    """
    try:
        tables = in_tables.split(';')
        for table in tables:
            arcpy.RegisterWithGeodatabase_management(table)
    except arcpy.ExecuteError:
        print(arcpy.GetMessages(2))

if __name__ == "__main__":
    RegisterThirdPartyTables(sys.argv[1])

Now this: Twenty years ago, shapefiles were pretty wicked, but their time has passed. File geodatabase capabilities exceed those of shapefiles by just about any metric. Joins between file-based geometry sources (.shp or .gdb) and databases are very inefficient, and should be avoided where possible. It sometimes takes a bit of unconventional thinking to work within the limits of native Geometry/Geography types, but there's usually a way to get adequate (and often superior) performance on a database join (and with an enterprise geodatabase, there's always the fallback to SDEBINARY storage).

Related Question