Spatial Geocoding with Match

The Table - Match command provides spatial geocoding within Manifold System. See the Geocoding topic for a general discussion of geocoding and the difference between address geocoding and spatial geocoding.

Match can be used to geocode tables by matching locations to drawings of postal codes, provinces, regions, counties or other geographic entities. Such drawings/maps are much more frequently available in international settings than are detailed data sets necessary for address geocoding. In addition, Match can be used to spatially geocode tables of all types using drawings as a guide. For example a table of well drilling information can be geocoded using a drawing of well locations as a guide.

Controls

	Match - Choose a drawing to which the table should be spatially matched.
$images\btn_move_to_top.gif$	Move to Top - Move the highlighted field to the top of the field list. This gives it the highest priority in matching.
$images\btn_move_up.gif$	Move Up - Move the highlighted field up one position in the field list.
$images\btn_move_down.gif$	Move Down - Move the highlighted field down one position in the field list.
$images\btn_move_to_bottom.gif$	Move to Bottom - Move the highlighted field to the bottom of the field list. This gives it the lowest priority in matching.
$images\btn_match_usecase.gif$	Use Case - Consider upper or lower case when matching text values in fields. When pressed, "nAme" will not match "Name" or "name".
$images\btn_match_useside.gif$	Use Side Whitespace - Consider space characters occurring before or after text strings. If pressed, " 89701" will not match " 89701" (with two spaces before the 8).
$images\btn_match_useinterior.gif$	Use Interior Whitespace - Consider space characters occurring within text strings. If pressed, "8 9 7 0 1" will not match "89 701".
$images\btn_match_matchall.gif$	Match All - If pressed, requires a match to all fields.
	Column / Match To Pane - Choose fields that must be matched to guide a spatial match.
	X - Field name to save longitude / x data. Suggested name: "Longitude".
	Y - Field name to save latitude / y data. Suggested name: "Latitude".
	Latitude / Longitude coordinates - Write location in decimal degrees latitude and longitude.

Match is easy to use. If we have a database table that has one field in common with a drawing, we can use the drawing to geocode the table.

To spatially geocode a table using Match:

1. Open the table.

2. Choose Table - Match - Drawing

3. In the Match Drawing dialog choose the name of the drawing to serve as a spatial guide.

4. Choose the fields in the table that are to be matched to fields in the drawing.

5. Check boxes as desired for matching text values.

6. In the X and Y boxes specify a name to use for the longitude field and latitude field.

7. Verify the Latitude / Longitude coordinates box is checked and press OK.

Each record in the table will be compared to all objects in the drawing for the specified fields. If the record matches field values for an object in the drawing the latitude and longitude of that object's centroid will be written into the record's latitude and longitude fields.

If only one field is specified, the records in the table will be geocoded by matching objects against that single field. If two or more fields are specified, Manifold will try to match against all fields.

Multiple fields are used for sub-matches to resolve duplicates. For example, if we are geocoding a table that has a city name and a country name for each record there may be multiple cities of the same name in different countries (for example, a "Paris" in France as well as a "Paris" in the United States). If both the city name and the country name are specified (with the city name field listed above the country name field in the Match dialog), then Match will first match all city names and then match by country names to resolve city name duplicates.

Example

We have a ZIPs drawing that shows zip code areas using Census Bureau ZCTAs. We also have a Customers table that has a zip code field for each customer. The zip code field is a text column. We want to geocode the table so that each record acquires latitude and longitude values to position it at the zip code centroid for that customer.

$images\eg_match_drawing_01.gif$

The drawing used is called ZIPs. The ZIPs drawing shows Census Bureau Zip Code Tabulation Areas (ZCTAs) in the San Francisco Bay region. ZCTAs are standardized areas that approximate the coverage of particular zip codes. The drawing was created by selecting ZCTA's near the Bay from a drawing of all ZCTAs in California, copying them and then pasting them to their own drawing. The areas have been colored using the Color dialog for no reason other than to avoid a bland, default look.

$images\eg_match_drawing_02.gif$

If we click open the ZIPs drawing's table we see that for each area in the drawing we have one field, a ZIPCODE field that gives the ZIP code for each area. (For our international readers, "ZIP" codes are postal codes in the US.)

$images\eg_match_drawing_03.gif$

If we click open our Customers table we see it has only two fields, a Name field giving the name of the customer and a Zip field giving the customer's zip code. Of course, a real "customers" table would likely have many more fields than just these two. This example shows only two fields to keep the illustrations as simple as possible.

To spatially geocode the table by matching it to the ZIPs drawing we choose Table - Match. This launches the Match Drawing dialog.

$images\eg_match_drawing_04.gif$

In the dialog we choose ZIPs as the drawing to match. To match using the Zip field we double-click into the Match To column for the Zip field and choose ZIPCODE in the list box that appears. Press ENTER to accept the ZIPCODE choice.

$images\eg_match_drawing_05.gif$

The Customers table does not yet have any fields in it that store the longitude and latitude value. We now tell Manifold what to name the fields that will be created. In the X box we enter Longitude and in the Y box we enter Latitude. Manifold will use these names for the X coordinate (the longitude) and for the Y coordinate (the latitude) values. We could name these fields whatever we choose but it is wise to name them "Longitude" and "Latitude" so that the table's column names are self-documenting.

The ZIPs drawing is not a projected drawing so automatically the coordinates taken from it are degree-based latitude and longitude coordinates. If the drawing being used for the match was a projected drawing, the Latitude / Longitude coordinates checkbox would be enabled and would be checked by default.

Checking this box when using projected drawings will geocode the table records using degree-based latitude and longitude coordinates. Unchecking this box when matching to a projected drawing will geocode the table's records using whatever native coordinate system is used in the projected map. For example, if one matches to a drawing projected into UTM coordinates and unchecks the Latitude / Longitude coordinates box the coordinates written into the table will be UTM coordinates. This is a rarely used option designed for expert usage.

$images\eg_match_drawing_06.gif$

The result seen in the table is that the Customers table now has two new columns called Longitude and Latitude. For each record these columns contain the precise location of the zip code centroid for the zip code area shown in the ZIPs drawing that has the same ZIPCODE value as the ZIP value for that record. In the illustration we've formatted the columns so that only four digits appear after the decimal point (see Formatting Columns )

If we like, we can create a drawing from our newly geocoded table. To do so, in the project pane we Copy the table and then Paste As a drawing.

$images\eg_match_drawing_07.gif$

In the Paste As Drawing dialog that appears we choose the Name and the Zip fields and choose the Longitude and Latitude fields to be used as our X and Y fields. These fields will be loaded into the X and Y boxes by default since Manifold knows that table columns called "Longitude" and "Latitude" quite likely contain the desired values. This also illustrates the wisdom of using these names in the Match Drawing dialog.

The result of the Paste As operation is to create a new drawing in the project called Customers 2 by default (since we already have one component, the table, called Customers Manifold will call the next component created by pasting it "Customers 2.")

$images\eg_match_drawing_08.gif$

We can drag and drop the new Customers 2 drawing into a map that also shows the ZIPs drawing. This drawing shows us the points at which each customer record in the table was geocoded. We can use this map to discuss three important aspects of geocoding using Match.

First, note that locations taken from the ZIPs drawing objects appear at the centroids of those objects. With unusual, bow-shaped areas (such as those at the bottom center of the map) the centroid of an area will often be located outside of the area.

Second, If we have more than one customer at the same zip code there will be more than one point created at exactly the same location. If we wish to disperse these points slightly we can use a script or other method to disperse them or to create displays that show the density of customers at different locations.

Third, if the drawing used to geocode the table has more than one object with the same target value, only the first such object encountered will be used to assign a location to the record. For example, suppose we had more than one area in the ZIPs drawing with the same ZIP code. Any record with that ZIP code would take its location from the first such area encountered (by Object ID order). This is not an issue with full five digit zip code areas like ZCTAs (in which every five digit numeric code is guaranteed to be unique), but it can be an issue when geocoding to other entities.

For example, suppose we use Match to geocode records to a drawing of US Counties. Most such drawings have many counties that consist of several area objects, for example, in the case of coastal counties that have a mainland area as well as several other areas showing coastal islands that are part of the county. Each such area object will usually have the county name, FIPS code and other fields with the same value. If we matched a table of records to such a county drawing the centroids will be taken from the first object with the given matching field value. If we are matching using FIPS code the first object with a particular FIPS code could easily turn out to be a coastal island for some counties. In that case, the point for the matched record would appear at the island and not on the "main body" of the county area where we intuitively might prefer to see it.

That may well not matter in some cases. Match is often used in tasks where the objective is not exact spatial positioning but rather to see a representative spatial distribution where one's customers (or other records) are located. In the example above, for instance, it may well be that we don't really care so much exactly where the customer is located but rather just wish to know if our customer base is located mostly in the South Bay or in the North Bay. If the latter interest is our objective we really won't care whether or not a particular record is geocoded to an "island" bit of an area instead of the "main" area.

However, if we really do care whether or not our table is geocoded to an "island" bit of an area, we can take a moment to clean up the drawing being used to guide the Match so that it contains only one area for each value used to geocode. In the case of a counties drawing where some counties consist of multiple areas we have many ways of undertaking such a clean up.

For example, we could use the Union transform operator to create a single area from all areas having the same FIPS code. Another approach might be to use the Area (I) intrinsic field within a query to find all areas smaller than a given size (the islands, presumably) and to move them to a separate drawing. In that case they could still appear in maps for visual fidelity but would not participate in analytic operations such as Match using the drawing.

Tech Tips

When working with ZIP codes or postal codes make sure to use text columns for the zip code or postal code. Many postal codes, such as ZIP codes, have leading "0" characters (with ZIP codes such as "02138") that are significant and will be preserved if a text column is used and lost if a numeric column is used.

If we have the Manifold Geocoding Data option installed we can use Street Address Geocoding to geocode a table of ZIP codes. In that case, there is no need to use Match.