Create a Linked Drawing from a Geocoded Table

In this example we create a linked drawing from a geocoded table created in Microsoft Access. Before beginning this topic, please read the Creating Drawings from Geocoded Tables topic.


Important: If you have installed 64-bit Manifold, do this example in 32-bit mode by launching Manifold System using the Manifold System (32-bit) shortcut. See the note on 64-bit Manifold at the end of this topic.


There are two ways of creating drawings from geocoded tables. A simple, straightforward way is to copy the table and paste it as a drawing. When the drawing is created the coordinates for points in the drawing and their data attributes (if specified for transfer) will be copied from the table. The created drawing (and its table) will be new components that are completely independent from the table that was copied. This process is shown in the Create a Map from a Geocoded Table topic.


Another way of creating a drawing from a geocoded table is to link the drawing from a geocoded table, which may be within the project or which may be some external geocoded table. In this case every time the linked drawing is opened or refreshed it will be dynamically recreated on the fly based on the data in the geocoded table. The position of points and their data attributes will be dynamically taken from the geocoded table. If the geocoded table is altered to add, delete or change the coordinates of records then when the drawing is refreshed the points in the drawing will appear, disappear or move as the table records are updated. This topic shows an example of this process using a geocoded table in an external Access .mdb file.


The distinction between the above two scenarios is subtle, but important. In the first case the data from a table are copied and a new, independent drawing is created. In the second case, a "virtual" drawing is created that remains linked to the table and is controlled by it.


If we have been working through the examples in order we have seen use of import and linking dialogs using simple file types.


In such dialogs the Files of type choice acts as a filter for what is displayed in the browse pane in the File - Import or File - Link dialogs. Simple file types are imported directly from the Import dialog by choosing the desired type in the Files of type box. More complex imports from data sources such as databases are imported by choosing Data Sources () in the Files of type box and then using the Data Source dialog.


Using the File - Link - Drawing dialog takes us into more advanced territory, because whenever we link a file the Data Source dialog is always used to make the connection, even if we would like to link to a simple file type such as Access .mdb.


The reason for this is that a linked drawing is normally a bidirectional, live connection between the linked drawing within Manifold and the originating data source. It is not a simple matter of reading some file, importing the data into Manifold and then forgetting about the original file. Linking a drawing means that Manifold must do all possible to propagate any changes made within the Manifold product back into the originating data source. That normally requires more complicated machinery within Manifold and to maintain modularity within Manifold's internal programmatic structures a more sophisticated access method, the Data Source dialog, is used in all cases of linked drawings.


Step 1: Create a table in Access




We begin by launching Microsoft Access and creating a geocoded table called Towns in the Access database. The table shows various towns in the United States together with a latitude and longitude location for each town given in decimal degrees. It also includes a URL for the official website for each town and a size field giving the relative population of each town. The fields are all text fields except for the size field which is a number. We save the Access database as a file called towns.mdb.


Although Access is a fairly simple DBMS it can be used for multi-user work. More than one program at the same time can use an .mdb database file. In this example we will use the towns.mdb database file within Access and at the same time we will use it within a Manifold session by linking to it from Manifold.


Step 2: Create a linked drawing in Manifold


Keep the Access session running with the Towns table open. Launch Manifold and create a new project. Choose File - Link - Drawing to link a drawing into Manifold using a DBMS table.




That launches the Data Source dialog to allow us to choose a data source. This dialog lists previously-created data sources. On the system used for this example there are some spatial DBMS data sources created but not yet one for the file containing the Towns table. We'll create a new data source by pressing the Add Data Source button.




That launches the Connect To dialog (very similar to the standard Windows File - Open dialog). We choose MDB Files in the Files of type box and then browse over to the towns.mdb file and click Open.




This creates a new data source in the Data Source dialog, initially named something forgettable like OLE DB Data Source. We immediately rename it to something we will remember, Towns mdb file. We can then double-click the new data source to use it.




This gets us to where we want to go, a Link Drawing dialog. This dialog allows us to choose which table in the .mdb file we want to use to control the drawing and which fields we would like to include in the drawing's table. This is a geocoded table (latitude and longitude values for each record), so the Type is Table with coordinate columns.


We choose the Towns table and choose all columns for import. We use the Longitude and Latitude fields for the coordinates. [Advanced users will be interested to learn that it is possible to choose a query from the database file (if one exists in it) and not just a table. Queries provide a set of records just like a table does and can be used to control a linked drawing just like a table. The only limitation is that parameter queries cannot be used since there is no way to interactively provide a parameter to such queries.]


The dialog also includes a Line ID box that allows us to choose a field that can be used to create lines in the drawing. We will not choose a field since for this example we will only create points. See the Linked Drawings from Geocoded Tables topic for information on creating lines in linked drawings.




Press OK and the result is that four new components appear in the project: a new linked table called Towns, a query called Towns Points and a linked drawing called Towns Points Drawing and the linked drawing's table. Note that the linked components are shown with icons that include a yellow "database" cylinder to indicate they are created from a database.


This is a classic example of a linked drawing created from a geocoded table. Manifold first links a table within the project to the external table, and then creates an intermediate query that transforms the coordinate column data (that is, the data in the latitude and longitude columns) into geometry form so that a linked drawing can be created from that query.




If we open Towns Points Drawing we see it contains points, as might be expected from the default name given this drawing by Manifold. In the screenshot shown above we've taken a moment to format the points in a bright green color and to increase their size slightly.


Each point is automatically created from one of the records in the .mdb table, with the data for each point coming from the .mdb table into the linked table in the project, through the intermediate query and then finally into the linked drawing and the linked drawing's table.




If we open the drawing's table we can see that the data attributes for each point are taken from the originating .mdb table as well. All fields appear in this table because we selected all of the .mdb table's columns in the Link MDB File dialog. Note that the fields in this table maintain a "live" connection back to the originating external database table. If we change a value in this table the corresponding value in the external .mdb table will change as well.


Step 3: Show the drawing in a map


We can import the US_main basemap drawing from the Manifold CD, create a map using it and then drag and drop the Towns drawing into the map as a layer.




The map shows us that the towns are, in fact, located in the correct geographic locations. To show a more elegant illustration we've also taken a moment to specify the projection for the map as an Orthographic projection.


Step 4: Alter the Access table and see points move


The useful thing about linked drawings is that if the external database table changes in Access then the linked drawing in Manifold that is created from the table also changes. We can see this by editing the table in Access even as we leave Manifold running.


Using the Windows taskbar we switch back to the Access session.




In Access, let's change the longitude value for Palo Alto.




We'll change it to -132.15 and then press the pencil icon to make the edit take effect.




The Access table has now been changed. Since Access instantly propagates all changes to the .mdb file in use, the towns.mdb file used to control the linked drawing in Manifold has also changed.


Using the Windows taskbar we switch back to the Manifold session. We click on the map window that displays our drawing and we refresh it to update it with any changes.




In Manifold, we choose View - Refresh Data to make sure that the map in Manifold is updated. When working with Manifold as a desktop application we refresh linked components to see changes. When using Manifold as an Internet map server, the refresh time can be automatically controlled to refresh every given number of minutes or on every image served.


[The Autorefresh View setting seen in the illustration above is used to control windows for things like updating red selection color when items are selected. It does not relate to data refresh as used with linked components.]




After we do a View - Refresh Data command we see the dot for Palo Alto has moved farther West into the Pacific Ocean. As the longitude changed in the .mdb table the point moved to the new location. Any changes made to the external table will appear in the linked drawing as soon as it is refreshed. If a record is added to the external table a new point will appear in the drawing. If a record is deleted from the external table the record's point will be deleted from the drawing.


The creation of linked drawings from external tables is extremely useful, because it allows the use of generic external tables as a data exchange interface for our GIS applications. There are many applications in which the locations of items of interest are captured within a geocoded table. For example, we might have some third party software that monitors the output of GPS transmitters in trucks and updates the location of each truck within a database table, or we might have a table from a web service that publishes the locations of recent earthquakes, the locations of ocean buoys, or the locations of lightening strikes.


There are many more programmers who know how to write code for DBMS manipulation than know GIS, and so as a result there are many more applications that know how to place locations of interest into some sort of geocoded table than know how to interface with any particular GIS package, or, for that matter, with Manifold. The use of linked drawings allows Manifold to interface with such applications without the application needing to know anything about Manifold. The application continues to write locations into the database table as it has long been accustomed to doing. Manifold automatically grabs the data from that table and updates the linked drawing created from it.


In this case, the database system in use is not just a convenient, centralized data warehouse for our organization - it also functions as a "firewall" of sorts between the knowledge needed to get information into the database and the knowledge needed to do something with that information in Manifold.


Step 5: Thematically format the drawing


We can use the data fields in the drawing's table just as we would any drawing's table. For example, we can use them to thematically format the linked drawing.




We could format the size of the points and their colors based on the size data field in the drawing's table. As the size field changes in the .mdb file the points will automatically change their size and their colors accordingly. This is a very useful way of presenting varying data in an IMS-driven website.


Note also that double-clicking on a point will launch the hyperlink in the URL field, which is also taken from the table that controls the linked drawing. In sophisticated IMS web sites we can use linked drawings to display information from tables that are changed by other Internet processes. For example, a table might be edited to update the contents of URL fields for each record based on the results of other programs.


Step 6: Propagating Changes back to the Access Database


Now, let's do something that usually proves endlessly confusing to beginners. We will edit the longitude and latitude values in the drawing's table and make the corresponding point move in the drawing. But, according to the Tech Tip discussion in the introductory Creating Drawings from Geocoded Tables topic this is not supposed to be possible, true? Let's see for ourselves:




We open the linked drawing's table and note that, as expected, after we changed the Longitude value for Palo Alto to -132.15 it not only moved the point in the linked drawing but it also changed the corresponding data value in the drawing's table, which was copied from the Access .mdb table.




We double-click into the cell to edit it...




...and we change the value back to the original value of -122.15.




If we refresh the drawing, we see that the dot representing Palo Alto has indeed moved back to the West coast and is no longer located in the Pacific Ocean.


But, how can that be? Isn't it true that changing an ordinary latitude or longitude value in a drawing's table will not cause the corresponding point in the drawing to move? Is it not true that the locations of points in a drawing are stored as geometry within the drawing itself, and that any copies of latitude or longitude values in the drawing's table are just that, copies?


All that is indeed true, but linked drawings are bidirectional in that changes to their tables also propagate backward into the data source from which those tables are created. We can see what is going by switching to Access and taking a look at the originating table in the Access database.




When we changed the Longitude value for Palo Alto in the linked drawing's table, because of the "live" connection between the linked drawing and its origin, that change was propagated backwards through the linked table back into the Access table, where the value was also changed to 122.15.




We can diagram what happened as shown above. It is not that the change in the longitude cell in the linked drawing's table move through some sort of hard-wired connection from the table directly to change that table's drawing. Instead, what happened is that the change in the linked drawing's table changed the value in the Access table (blue arrow).


When the linked drawing was refreshed, the change in the Access .mdb table was updated into the Towns linked table in the Manifold project and the change in that linked table was propagated through the Towns Points intermediate query into the geometry information used to build the Towns Points Drawing linked drawing.


Linked drawings in this case are different from standalone drawings. Changing the data fields in a standalone drawing's table won't result in any changes in the drawing, since those fields are not linked to anything and are just copies of the data used originally to create the drawing. But with a linked drawing, that linked drawing's table maintains a dynamic link back to the originating table, so changing the fields in the linked drawing's table in a roundabout way comes back to influence the geometric structure of the linked drawing.


This is a bit tricky and sophisticated for beginners, but it is nonetheless a good illustration of how cascading links work within Manifold and how such cascading relationships are preserved even though the data types within each link may be different. We can exploit these capabilities to build IMS sites and other useful applications.


For example, suppose we would like to build an IMS site that allows people to add and move points by editing their latitude and longitude values. A simple way of doing this is to use a linked drawing that takes its data from a geocoded table. Without needing the relatively more sophisticated ability to edit internal drawing geometry we can simply allow users to edit fields in database tables, a task usually well within the skill set of most web developers, who tend to have greater experience with the manipulation of database tables through web sites than they do with the programming details of working with a specific GIS package such as Manifold.


Important Note when Using 64-bit Manifold Editions


Due to a lack of required Microsoft facilities in 64-bit Windows systems, Manifold in 64-bit mode cannot export, import, export or link to DB, HTML, MDB, XLS or WKx format files. This includes no access to the MDB parts of Manifold MFD and MapInfo TAB imports. The workaround for importing or exporting such files is to launch Manifold in 32-bit mode by using the Manifold System (32-bit) shortcut, perform the export from or import into a .map project file and then re-launch Manifold in 64-bit mode using the Manifold System (64-bit) shortcut. Linking is more complex: the data must be in some format usable in 64-bit mode within 64-bit Windows systems, such as a SQL Server database. Alternatively, the data can be kept within a Manifold .map project file and linked using the Manifold ODBC driver.


See Also


Linked Drawings

Formatting Lines in a Linked Drawing