1

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). enter image description here

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")[0]

            # 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

  • 1
    It sounds like what your asking is doable. But how you are asking is a little confusing. Please select the edit button and add a screenshot, and tell us more about your data. Do you want to calculate multiple values to one county? or do you just have one value for each county? The latter would not really be a heat map. (neither would the former but in that case it would only be possible to get a heat map if you had locations for each of the points. – Brad Nesom Aug 22 '14 at 17:12
  • I want to calculate the aggregate value to one county. Basically I'm just doing validation of a database to show that the "Risk" calculation is accurate, and that it's reasonable given known fault lines and simulated earth quake occurrences. – KrishnaS Aug 22 '14 at 18:01
  • 2
    What are your data sources? Is it a shapefile, personal or file geodatabase that you are trying to join to MSSQL Server tabular data? If both the spatial and tabular data are both contained within the SQL Server database, you should consider applying the join within the database. it will be much faster and eliminate a lot of the headaches that will follow. – RyanDalton Aug 22 '14 at 18:43
  • 1
    So I think you want to duplicate the manual steps to create this map in a script. Right? – Brad Nesom Aug 22 '14 at 18:49
  • 1
    So do you want us to write the py for you? What have you tried? Please edit orig with all info not comment – Brad Nesom Aug 23 '14 at 0:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.