This could be a trivial question, but I've been kind of stuck on it for awhile. I'm trying to construct multiple maps that reflect aggregated data showing overall risk by county in a SQL Database (using Microsoft SQL 2012). Each entry in my data set reflects an insurance contract with a risk score associated with a FIPS county code. I would like to use the AddJoin tool to join to an associated shape layer on FIPS county codes. The resulting map would show each county's risk with an associated color ramp depending on the risk level.
EDIT: I've taken RyanDalton's advice and created a seperate table that joins my data to a \ GIS database with shape entries on matching FIPS codes using MSSQL Server. I was able to connect to my specific database and make a new query layer useing ArcPy. The only problem I'm having is creating a color ramp based on the entries in my table (i.e. create a color ramp that shows differences between counties on values of Overall Risk Score).
The below image shows what I am trying to do with ArcPy, as well as the resulting map on the right (shows overall risk for an earthquake in the US).
Below is a rough sample of my code which doesn't output a map with a color ramp:
import os, arcpy from arcpy import env # Declare Directories workingDir = r'C:\Users\I55316\Documents\DQScore\ArcPy_Analysis' layerDir = workingDir + r'\Layer_USNat' outputDir = workingDir + r'\Output' dbDir = workingDir + r'\DB' sdeFile = 'DQ.sde' # Declare Database Server Connection specifications databaseType = r'SQL_SERVER' dataBaseInstance = r'pmfldt43\sql2012' systemAuthType = 'OPERATING_SYSTEM_AUTH' # Declare Query Layer Specifications PerilType = 'EQ' layerName = 'AllNationalCounty_EQ' query = 'SELECT * FROM dq.dbo.AllNational_EQ_GIS' oid_fields = 'CNTY_FIPS' shape_type = 'POLYGON' srid = '4326' spatial_reference = arcpy.SpatialReference('WGS 1984 UTM Zone 12N') # Create Database connection via .sde file if os.path.isfile(dbDir+ '\\'+ sdeFile): os.remove(dbDir+ '\\'+ sdeFile) arcpy.CreateDatabaseConnection_management(dbDir, sdeFile, databaseType, dataBaseInstance, systemAuthType) arcpy.env.workspace = workingDir+'\\'+sdeFile arcpy.MakeQueryLayer_management(dbDir+'\\'+sdeFile, layerName, query, oid_fields, shape_type, srid, spatial_reference) # Declare map document mxd = arcpy.mapping.MapDocument(workingDir+r'\NationalDQScoring_EQ.mxd') mxd.author = "Krishna Soni" # Add Layer #lyrCounties = arcpy.mapping.Layer(layerDir + r'\UnitedStates_Counties_TS2.0.0.lyr') lyrFaultLines = arcpy.mapping.Layer(layerDir + r'\UnitedStates_FaultLines_TS2.0.0.lyr') # Define data frame df = arcpy.mapping.ListDataFrames(mxd, "Layers") # Add Layers arcpy.mapping.AddLayer(df,lyrFaultLines) # Add graduated Colors for lyr in arcpy.mapping.ListLayers(mxd): if lyr.symbologyType == 'GRADUATED_COLORS': lyr.symbology.valueField = 'Overall_Risk_Avg' lyr.symbology.numClassed = 10