Automating Bulk Changes to Feature Attributes

For some workflows, there is a need to update attribute data for features on a regular basis from outside sources.    Cartographica's Join capabilities can make this an easy process.

If you are mostly looking to synchronize data from a database to change column data and you have some kind of Unique ID column (feature ID, unique serial number, or something), then you can use the Join functionality to add/replace column data from another source.

To do this, Cartographica can use either an ODBC database connection or a local text file formatted in CSV or TSV format (comma-separated or tab-separted).

When using Join to add or update columns in an existing layer, Cartographica will use a set of one or more key columns to determine any matches in the layer, then add data from any new columns and replace data from any existing columns on any features whose keys match.

The process between databases and CSV files are very similar, the difference being the source of the data, and the fact that ODBC can be a bit difficult to set up. However, we have tested our ODBC connections with mysql, postgresql, and FileMaker Pro databases, so we're confident that as long as there's a functioning ODBC connection, we will be able to communicate with it correctly.

To update information using a CSV file:

  1. Choose File > Import Table Data… 
  2. When prompted, select the data file to import and set any import options (character set, etc) 
    Note: column names will usually be taken from the first row, which is pretty standard and makes this process a bit easier. The first row is ignored as data unless you uncheck the box indicating that the first row is not a header 
  3. In the Import window, select the Join tab 
  4. Now select the Target Layer from the menu in the lower-right corner. This will determine whether data is loaded to a new layer or updates an existing layer 
  5. For each key column, make sure you have selected the matching column in the Map To column and check the Key box. If you have a single unique ID, we suggest using just a single Key column. However, if two or more columns are necessary to uniquely identify a feature in the layer, you will need to check multiple Key boxes. 
  6. Use the Map To column to match the columns in the imported file to columns in the existing layer (or select Do Not Import for columns you want to ignore) 
  7. Once the column data is set up, click Import (if Import is not available, the most common cause is not having a Key box checked).

Additional Notes:

  • Cartographica searches exhaustively for key matches, which means that you can do joins which add data to multiple features based on a common key value. For example, if you had a column for material for curbs which was empty, but you knew that every street which was paved in 1990 had marble curbs, you could join on a column that contained the paving year and set the material using a csv file that contained: 

    Year,Material
    1990,marble  
  • Existing features that do not match the Key field(s) are left unchanged, so you can join with a file that contains only updated items without changing anything that is already in the layer.
  • Information in the update file that does not match an existing key will be discarded.   This procedure can not be used to add new features to the target layer.
Have more questions? Submit a request

Comments

  • Avatar
    Les van Rooyen

    Saved me a lot of work, thanks!

    Can new records be added this way?

  • Avatar
    Gaige

    At this point, we can't add new features this way.    The assumption is that this is for updating data related to geometries already in the system.

    Are you working with point geometry only where the coordinates are part of the imported data?

  • Avatar
    Les van Rooyen

    Yes, point data to be added to the existing layer.

    Not sure if its possible to add/duplicate records.

Powered by Zendesk