Which program that merges excel data onto shapefiles is easiest to use?

Question

some options for merging looks like fusion tables, vizmaps, and these 2
http://mapbox.com/tilemill/docs/guides/joining-data/
http://webhelp.esri.com/arcgisdesktop/9.3/index.cfm?TopicName=About%20joining%20and%20relating%20tables..

which is easiest?

the shapefiles are probably going to be TIGER census ones unless I find better ones.

Answer 1

Fusion Tables, Google Visualization API and VizMaps with Google Maps API may be what you are looking for:

Fusion Tables: "Google Fusion Tables lets you store, share, query, and visualize data tables. It offers a REST API to manage tables, info window templates, and styles. The query endpoint offers allows you to manage data rows (insert/update/delete), and query the table for all rows that match spatial or data conditions. The results of queries can be CSV or JSON, or used in the Google Maps API or Google Chart Tools." https://developers.google.com/fusiontables/

VizMaps Application: VizMap is the collection of components that allow you to create a data driven application that will merge google visualization and google mapping straight out of Excel. This consists of several components, which are all integrated in the Excel Driving application, and many of which can be seen in other roles throughout this site.

And see this link: http://ramblings.mcpher.com/Home/excelquirks/getmaps/mappingapplications

Electing (or not) Google Maps API should not be dependent on this issue - you can adress do that kind of integration using other platforms like Mapserver, Geoserver ('more GIS powerful') or Bing Maps.

Answer 2

I do not see how Google Maps can join an Excel file to a Shapefile but I know ogr2ogr can join Excel data, outputing a new Shapefile.

Answer 3

I've done something similar lately using an update cursor and the xlrd-library with python. The script updates some existing records with the data from the excel sheet. Hopefully this will be of some use for someone else.

import xlrd
import sys, os

# Geoprocessor-object (ArcMap 9.1 and older)
try:
    import win32com.client
    gp = win32com.client.Dispatch('esriGeoprocessing.GpDispatch.1')

# Geoprocessor-object (ArcMap 9.2 and newer)
except:
    import arcgisscripting
    gp = arcgisscripting.create(9.3)



class ArcSql:
    type_book = {'str':("'","'"), 'int':('', ''), 'float':('','')}
    format_book = {'shp':('"','"'), 'GDB':('[',']'), 'SDE':('','')}

    def __init__(self,colomn_name, values_list, value_type = 'str', arc_format ='shp'):
        self.colomn = colomn_name
        self.values = values_list
        self.valtype = self.constructor(self.type_book, value_type)
        self.aformat = self.constructor(self.format_book, arc_format)
        self.colomn_formated = "%s%s%s" %(self.aformat[0], self.colomn, self.aformat[1])


    def statement(self, connector = "OR", operand = '='):
        space = ' '
        connector += space
        operand = space + operand + space
        temp_state = []
        try:
            count_values = len(self.values)
        except TypeError as terr:
            return str(terr) + ' values must be passed as a list'

        if count_values:
            for v in self.values:
                x = self.colomn_formated + operand + self.valtype[0] + str(v) + self.valtype[1] + ' ' + connector
                temp_state.append(str(x))
        else:
            return "error, object as no attribute values..."

        state = "".join(temp_state)[:-4]              
        return str(state)


    def constructor(self, book, book_key):
        return next (v for k,v in book.iteritems() if k==book_key)



def update_fields(shape, sql, fields_to_update, data_to_put, z = ''):
   '''
   Loops through possible multiple fields of a shape to update their records. Based on the
   ArcGIS UpdateCursor method.

   Takes a 'shape' (with full path), a 'sql statement' (optional) as a selection of the 'fields to update',
   those fields and the data to store as arguments.

   fields_to_update has to be a LIST or TUPLE
   if it is a single field that is to be updated,
   data_to_put can be a flat list otherwise is HAS TO BE! LIST of LIST, or a List of Tuples
   example:

   ['field_name','Value']
   [['Der','Die','Das'],[1,2,2]]
   '''
   if sql: 
       rows = gp.UpdateCursor(shape, sql)
   else:
       rows = gp.UpdateCursor(shape)
   row = rows.Next()

   if row == None:
       msg = "no row opbejct!"
       print msg
       gp.addmessage(msg)
       return 

   elif row != None:
       count = 0
       while row:
           if fields_to_update and data_to_put:
               try:
                   for n in range (len(fields_to_update)):
                       if isinstance (data_to_put[n], list) or isinstance (data_to_put[n], tuple):
                           row.setValue(fields_to_update[n], data_to_put[n][count])
                           rows.updateRow(row)
                           print 'updating...'
                       else:
                           row.setValue(fields_to_update[n], data_to_put[count])
                           rows.updateRow(row)
                           print 'updating...'

                   count += 1 
                   row = rows.next()

               except:
                   msg = gp.getmessages() + " Field update failed!"
                   gp.addmessage(msg)
                   print msg
                   return

   del row, rows




path = r'D:\xxx'    
shape = r'%s\shape.shp' % path
xls = gp.getParameterAsText(0)
#xls = r'D:\opdrachten\importKwaliteitInMoeder\Excelsheet kwaliteitgegevens voor import in tool.xls'

if xls[-4:] != '.xls':
    gp.addmessage('File is not a XLS.\n')
    sys.exit(1)
else:
    pass


book = xlrd.open_workbook(xls)
sheet_name = 'Blad2'
## get the index of the sheet named Blad2 as a list
index = [i for i,name in enumerate(book.sheet_names()) if name == sheet_name]

## check if index[0] has a value, otherwise sheet_name couldn't be found
try:
    sh = book.sheet_by_index(index[0])
except IndexError, Ierr:
    print str(Ierr), 'No sheet named', sheet_name, '!'
    sys.exit(1)


db = {}
##  get the cellvalues from the second row, these are the (shape-) field names
header = [sh.cell_value(1,i) for i in range(sh.ncols)]
for head in header:
    ## the values gonna be stored in simple lists
    db[head] = []

## loop through all the cells, starting at row three
for c in range(sh.ncols):
    for r in range(2,sh.nrows):
        db[header[c]].append(sh.cell_value(r,c))

## re-arrange the data: data to put is a list of list and col is a list of the fieldnames         
data_to_put, col = zip(*[(db[k],k) for k in db.iterkeys()])
## get a sql statement based on the identifier HYDRO_CODE to select only those rows from the shape
sql=ArcSql(header[0],db['HYDRO_CODE']).statement()
## write the data to the shapefile
update_fields(shape, sql, col, data_to_put)

Answer 4

I guess "easiest" depends on what the "merging" process requirement is. If you're doing a straight format conversion, then ogr2ogr (as suggested by klewis) is an easy, scriptable approach.

If you want to do some more manipulation (e.g. exclude some entries, reproject, select some additional data from another table), then "easiest" will probably depend on exactly what manipulation you are doing. However an alternative to user890's answer would be to use SpatiaLite, which can give you a "virtual table" representation of the excel file, and you can then do spatial SQL operations. How "easy" that (or any other SQL based tool) is to use obviously depends on your SQL experience.

Answer 5

You can load the DBF from your shapefile straight into Excel as another sheet - its a spreadsheet file (DBASE Format). Then do whatever magic you want to join your data to that sheet. Make sure the sheet is in the same order as when you loaded it and with the same number of rows and then save it. No need to leave Excel. Also works with OpenOffice and LibreOffice.

Answer 6

Similar to Spacedman's answer, you could also import the shapefile into a Personal Geodatabase (aka MS Access database), along with whatever Excel worksheets your heart desires.

Then just use the sql capabilities of MS Access to query, join, update...etc