Welcome back! In the first article of this series, I walked you through the basics of creating and populating a custom table of location data using MapQuest Data Manager. In this article I will take you through the remaining functions that Data Manager offers, and make you a Data Manager expert extraordinaire!

A Multi-Step Process

As you might recall from the first article, to populate your custom table you perform the following steps:

  1. Create the custom table.
  2. Generate a text file that contains location information.
  3. Upload the text file.

Although I showed you how to push data from staging to production, you will normally go through several iterations of data maintenance before doing this push (unless you are one of the few software developers who never make a mistake). These maintenance steps will likely include adding new records, removing existing records, and changing records that are already in the database--basic database maintenance type stuff.

By the way, as I walk you through this article I am going to further refine my own personal collection of locations. I encourage you to come up with your own list of locations, even if you randomly pick them off a map. My locations are pretty standard fare in the D.C. area, but you might choose instead to come up with locations that are closer in proximity to where you live.

With that said, let's look at the database maintenance functions that Data Manager provides for those of us prone to make mistakes. The first is the Clone Table function.

Cloning a Table

The majority of table maintenance functions are accessed on the Tables page. Remember that you need to log in at http://developer.mapquest.com before you can use any of these functions.

Figure 1. The Tables page

The Clone Table option, as the name implies, is very useful if you want to make a duplicate of an existing table. You can use it to copy the entire table, including its contents, or just the table schema. In this case, I have decided I no longer like the name of the table I created in the last article, so I am going to use Clone Table to make a copy of the table with a new name, and I will do this without losing any data!

To begin, click Clone in the menu of options on the right of the page to load the Clone Table page.

Figure 2. Clone Table page

Two options specify how the table will be cloned: Clone Structure Only and Clone Data and Structure. Click the latter, type a new table name--I am using PhotoTrekLocs--in the New Table Suffix field, and then click Clone Table. Data Manager creates a copy of the PhotoTrekPOIs table named PhotoTrekLocs.

It should be no surprise by now that while Data Manager is cloning your table, both the source and target tables will be locked, just as they are when Data Manager is in the midst of any operation. When the clone operation is complete, you will receive a notification e-mail.

If you haven't guessed it yet, what you are actually accomplishing here is a rename of the PhotoTrekPOIs table. Cloning a table in this manner is the first step, and the second--after verifying the copy completed, of course--is deleting the original PhotoTrekPOIs table.

After receiving an e-mail that indicates the table has been cloned, click Tables on the main Data Manager page. The first thing you will notice is that there are now two tables: the original PhotoTrekPOIs table, and the newly cloned PhotoTrekLocs table.

Figure 3. Table list

Change the "active" table by selecting PhotoTrekLocs in the Change Active Table drop-down list. You are making this selection to verify that the data was copied to the newly cloned table as expected. It really isn't a big deal at this point, because there are only two records in the table, but it is a good habit to get into. To verify the data is in the new table, click Edit Locations on the menu at left.

Figure 4. Edit locations

Next, click Find Locations. This will display a list of all of the records in the staging area--only two at this point.

Figure 5. Listing the locations

I cannot recommend highly enough that you take the time to go through this extra step of verifying your data, especially if your location table has a significant amount of records in it.

Now that you have verified that the location data was copied correctly, you can go ahead and delete the PhotoTrekPOIs table.

Deleting a Table

To delete a table, begin by clicking the Tables menu option. This returns you to the Tables page, where both tables show up (see Figure 3). Go ahead and click the Delete icon that is in the same row as the PhotoTrekPOIs table. Data Manager politely asks if you really want to delete this table. Click YES-DELETE, and Data Manager will permanently remove this table.

Figure 6. Verify table deletion

Deleting a table is a synchronous "blocking" process, unlike most of the other Data Manager operations. When the table is deleted you will be returned to the Tables page, where you will now see only one table listed: PhotoTrekLocs. Deleting a table is just that easy, so be careful and make sure you have a backup copy of your data!

Adding a New Location

In the first article of this series, I showed you how easy it is to add a group of location records all at once. In fact, at the end of this article I will show how easy it is to add even more location records, using something called "incremental batch files."

But what if you want to very quickly add a new location without going through the process of generating an import file? What if you don't know the entire address for the location, but know just enough to get close? The answer is on the Add Location page.

Let me show you just how cool this feature is. To start, click Add Locations on the menu.

Figure 7. Adding a new location, step 1

On the first Add Location page, type as much information as you know about your location. What the form doesn't provide is fields to enter custom information; these will appear on the next page, so don't be concerned.

For my example, I have typed "International Spy Museum" as my next location, using a record ID of "0000000050." I have added as much of the location information as I know. You should do the same with whatever location you choose to enter. When you are ready to move on, click CONTINUE-ENTER LOCATION, which tells Data Manager to proceed to the next page.

Note that if you click DONE-ADD LOCATION, you will not have the option of adding any custom information, and the record will immediately be added to your staging database, as is.

On the next page, your custom-defined fields are ready and waiting for you to populate them.

Figure 8. Adding a new location, step 2

Here I have entered the URL of the Spy Museum, as well as the appropriate location type. If you don't recall the location types I am using in this series, refer to the first article. You can, of course, use any location type you want, because this is a custom field. Now comes the fun part!

After you click ADD LOCATION, Data Manager automatically geocodes the location and then takes you to an interactive page that displays a map of the location. On this page you have a variety of options to further refine your location.

Figure 9. Adding a new location, step 3

In addition to displaying the location information you have already entered, before adding custom fields, this page also displays the geocode quality of the location. Most importantly, however, are the options you have to customize and refine your location data on this page.

Move - Clicking this will take you to an interactive map in which you can refine your location by simply clicking the map itself. I will cover the details shortly (see the section "Moving an Existing Location" later in this article).

Edit - Clicking this will take you to the Edit Location page, which I also will cover shortly (see the section "Editing an Existing Location" later in this article).

Add Another Location - Clicking this will, as you have no doubt guessed, display a blank form for entering your next location.

Push to Production - Clicking this will push the entire contents of your staging database to production, overwriting any data you currently have stored there.

Editing an Existing Location

My second most favorite function for maintaining my data in Data Manager is Edit Locations. Edit Locations is a powerful feature that lets you filter your collection of locations to a manageable size, and then make changes to the locations one at a time.

Start by clicking Edit Locations on the menu.

Figure 10. Editing locations

You use the first page to find locations; more specifically, you use it to find and display only the locations that match the search criteria you specify. So, not only can you use it to locate a specific record, you can also use it to locate a number of locations that share common information among them.

In my case, two of the three locations I have specified share the same ZIP Code. So, if I type 20224 in the Zip or Postal Code field, and then click Find Locations, only locations I added where the ZIP was 20224 are displayed. In this case, there are only two. Depending on what you choose to use for search criteria, you might see more, or less. The important thing to realize is that this is an extremely powerful way to filter your location data down to a reasonable size. In addition, you can display a single record very easily if you simply enter that record's ID, or any other information that is unique to that record.

Figure 11. A filtered location list

Now, here is the real power of the Edit Locations function. Simply click the Edit icon that corresponds to the location you want to edit, and Data Manager displays a form for editing the existing record data--essentially the same form you would use for adding a new location.

I am going to update the second record. After I click Edit next to record number 2, I notice that I previously misspelled "District of Columbia" as "Distric of Columbia," and I definitely want to change this.

Figure 12. Editing a record

After correcting my misspelling, I simply click DONE-UPDATE LOCATION, and my location data is saved in my staging area. It's that simple.

Did you notice the Move Location link below the thumbnail map of your location? This is yet another way to access the interactive movement feature I mentioned earlier, a feature that I will cover very soon.

Deleting an Existing Location

On occasion, you might find that you want to remove a location from your MapQuest-hosted location table. The simplest way to do this is to locate the record, and then simply delete it using the Delete option in Data Manager.

To delete a location you must first find it using the same method explained earlier, in the "Editing an Existing Location" section. First, click Edit Locations to display the Find Locations page. Enter enough information to find the location record you want to delete, and then click Find Locations. In my case, I am going to delete a record I added earlier in this article, the "International Spy Museum."

Because there are only three records in my staging table, I am simply going to click Find Locations without typing any search criteria. The result is that all of my locations are displayed.

Figure 13. Displaying all locations

Now all I have to do is click the Delete icon in the row corresponding to record number 3, the "International Spy Museum" record I added earlier. When I do this, Data Manager displays a warning page, asking me to verify that I really want to delete the record.

Figure 14. Verifying record deletion

Because I really do want to delete this record, I click YES, which immediately removes it from my staging database. When the record is deleted, Data Manager confirms the deletion and then asks whether I want to return to my list of locations or push my changes to production. Although I have now deleted the record I added earlier, I am going to go ahead and push my staging database to production, because I also corrected a misspelling in another record.

Moving an Existing Location

Now let me show you the coolest feature that Data Manager offers for data maintenance. No matter how you get here, the Move Location page is a great way to visually refine an already geocoded location. In my example, below, I click Edit Locations on the menu, click Find Locations to display all my locations (all two of them!), and then click the Move icon beside my record with the ID "0000000020," which happens to be the Civil War statue.

Figure 15. Moving a location

The page that Data Manager displays includes a map of the location based on the current geocoding, along with two options: Enter a latitude and longitude or Re-geocode with MapQuest. Before I explain these two options, however, I want to show you just how cool this page really is.

In many cases the location you want to enter does not lie at a specific house number on a street, or at a street intersection. In these cases you might think you either need to know the exact latitude and longitude (Lat/Lng) of the location, or need to settle for a street address that is only close to the real destination. This is not the case at all.

Data Manager's Move Location provides an interactive means of more accurately identifying where your location really lies on the map. In the case of the Civil War statue that I selected earlier, the street address does not quite accurately represent where the statue is physically located, so I will use the map itself to better locate it.

The first thing I do is zoom in as closely as possible using the zoom control on the right of the map; this will give me the best chance to identify the true location of the statue. The next thing I do is click the map in the general area of where I know the statue to be. Realize that the best you can hope to do here is improve the placement of your location on the map--you probably won't get it exactly perfect. You would need to know the Lat/Lng for the location to get it perfect.

My updated location is displayed in Figure 16.

Figure 16. Updated location

Now it is your turn! Click one of the locations that you entered and then click the map to move around the icon.

It might take you a few iterations of clicking the map and seeing where the little blue star moves before you get it just the way you want it. When you have the star in the correct location, simply click UPDATE LOCATION. Note that even though an updated Lat/Lng has now been stored for your location, your street address information will not be updated because you have effectively circumvented this information by directly placing your location on the map.

What happens if you mess up the location really badly and want to move that little blue star back to where you started? Simply click Re-geocode with MapQuest. This geocodes the location according to the street address information currently stored for this location. In other words, it takes you back to where you started--no harm done.

The other option on the Move Location page is specifically for those of you who happen to know the exact Lat/Lng of your location. If you click this option, a dialog box is displayed, in which you can enter the Lat/Lng of your location.

Figure 17. Entering latitude and longitude

Incremental Batch Files

Before leaving Data Manager I want to cover one last topic--incremental batch files.

Not only does Data Manager provide the means for uploading a batch of multiple locations at once, as I demonstrated in the first article in this series, it also allows you to use essentially the same mechanism to add new records, and to change and delete existing records. (If you need to, refer to the first article in this series for a review of how to create a batch file that contains location data for uploading.)

The only difference between a regular batch file and an "incremental" batch file is that uploading an incremental file does not, in and of itself, remove any locations in the currently selected table. An incremental batch file is very close in format to a normal batch file. The main difference is that an additional field appears on each row of an incremental batch file. For example, consider the following incremental file:

c,"0000000020","Statue of Dudes on Horses","100 Maryland Ave SW","Washington","District of Columbia","DC","20224","US","","","","","","","2"
a,"0000000030","Space Shuttle US Enterprise","14390 Air and Space Museum Parkway","Chantilly","Fairfax","VA","20151","US","","","","","","http://www.nasm.si.edu/udvarhazy/","1"
a,"0000000040","The White House","1600 Pennsylvania Ave","Washington","District of Columbia","DC","20500","US","","","","","","http://www.whitehouse.gov","2"

You'll notice that this file looks very similar to the batch file I used in my first article, except for the additional field at the beginning of each row. Note there are two rows that start with a, and one that starts with c. This additional field at the start of each row is how you tell Data Manager that you want to either (a)dd a new record, or (c)hange an existing record. In addition to adding two new records in this batch file, I have changed the description of the statue to the more playful "Statue of Dudes on Horses"--everything else remains the same.

CAUTION: An incremental batch file is loaded into Data Manager the same way a regular batch file is loaded, with one important distinction: be sure that the file extension on your incremental file is .acd instead of .txt, because this gives Data Manager a heads-up about the type of file you are about to upload. Other than that, use the Upload File option to upload your incremental file, just as you did when loading a regular batch file in the first article.

After pushing this incremental file to my table using the Upload File option, and waiting patiently for an e-mail that indicates my upload has been processed, the following results are displayed:

Figure 18. Updated locations

Not only are there two new locations, but the description of the second record has been changed based on the new description I added to the incremental file. Quite nifty, isn't it?

In addition to using an incremental batch file to add new records and change existing records, you can also use the file to delete existing records. To accomplish this you simply create a batch file that contains records with only two fields: d followed by the record ID of the record you want to delete. For instance, if I want to remove the first and third records displayed above, the contents of my incremental batch file will be as follows:

d,"0000000010"
d,"0000000030"

Notice that there is no need to include any other information besides d and the record ID.

I am not going to actually upload this incremental batch file, because I only have four locations in my table as it is, but you should feel free to experiment by uploading an incremental file that deletes records in your table.

One final note: all of the changes we have made, both interactively and using the incremental batch file, are changes to the staging table only. Be certain to push any changes to production that you want to use in your application; I have done the same.

Ready to Trek!

Hopefully you now have a much greater appreciation for Data Manager, and the host of options it provides for getting your custom location data loaded on the MapQuest servers. In the next article we will (finally) dive right in and start work on the PhotoTrek application that will use this location data.

See you then!