Creating Drawings from Geocoded Tables

A geocoded table is a table where every record has a location given in latitude and longitude. In Manifold, it is easy to create a drawing from a geocoded table. We simply copy the table and paste it as a drawing.


For an example of a geocoded table consider the following table:




Each record has a value in the latitude and longitude columns. The latitude and longitude of each record specify where that record is located. Note that the latitude and longitude values should be expressed in decimal degrees, the standard GIS notation for latitudes and longitudes. For an introduction to geocoding, see the About Geocoding topic.


Creating a Drawing from a Geocoded Table


Geocoded tables may be used to create drawings. Drawings created from geocoded tables contain points at the latitude and longitude positions of each record in the table.




The illustration shows a map where a drawing of points created from the geocoded table is overlaid in a layer above a drawing showing the United States. The points for each town were plotted at the latitude and longitude of that town as given in the table.


There are three ways to create a drawing from a geocoded table. The first method is to copy the table and paste it as a drawing. The second method is to import a drawing directly from an external geocoded table. The third method is to create a linked drawing from a geocoded table that is either part of the project or residing in some file or database external to Manifold.


To create a drawing from a geocoded table:


1. Import the table into a Manifold table.

2. In the project pane, use Edit - Copy to copy the table and then use Edit - Paste As - Drawing to paste it as a drawing. A new drawing will appear in the project pane.



To import a drawing from an external geocoded table:


1. Choose File - Import - Drawing and open the desired database file or connect to the desired data source.

2. Choose the table desired and check the desired fields. Specify the fields that contain latitude and longitude values (by default, the system will guess that any fields named "Latitude" and "Longitude" are the fields to use). Press OK. A new drawing will appear in the project pane.


Pasting New Drawings and Copying Fields


Pasting a geocoded table as a drawing in the project pane will create a new drawing together with a new associated table in the project. The drawing will contain points at the location of each record.


The associated table (like the tables for all drawings) will be created with at least one field, the object ID field. During the paste process a Paste As Drawing dialog will appear asking if any additional fields should be pasted from the geocoded table into the new drawing table. If the Latitude / longitude coordinates check box in the Paste As Drawing dialog is checked, drawings will be created in Latitude / Longitude unprojected form.


For example, if copying the geocoded table illustrated above we might wish to paste the table into a new drawing as points without copying any of the data fields. In that case, the new table created with the drawing will be empty except for the object ID field. In another example, we might wish to copy the Name field as well so that each point record has an object ID field and a Name field containing the name of the town at that point. Depending on our desired use of the data we might import all of the fields from the original table. We might even wish to import the latitude and longitude fields as well.


Very Important: If we copy the latitude and longitude fields from the original table into the table associated with the drawing, it is very important to realize that these are just copies of the latitude and longitude fields that were part of the original table's records. They are not a "handle" into the geometric coordinates that define the position of the points within the drawing's internal structure. If we click open the table and change these values they will not move the points in the drawing about. See the Editing Data in Tables topic for some additional notes on this concept.


Geocoded Data and Projected Coordinates


This section involves more advanced concepts requiring an understanding of projections. So far this topic has discussed geocoded tables where each record has valid latitude and longitude fields in decimal degrees. Geocoded tables can also have locations for each record expressed in projected coordinate measures within a certain projection. Such geocoded tables can be copied and pasted to create a new drawing, imported to create a new drawing or used to create a linked drawing.


When using a table that is geocoded using projected coordinates as a new drawing into the project pane we must uncheck the Latitude / Longitude coordinates box in the import or pasting dialog. Manifold will then create the new drawing using default Orthographic projection values. Once the drawing is created we can open it and change the drawing's coordinate properties into whatever projection coordinate system was being used by the coordinate values in the table. To do this, we use the Edit - Assign Projection dialog after the import to make any necessary adjustments. See the Import a Shapefile example topic for a simple example and the Import Drawing - SHP, Shapefiles topic for a more extensive example of using Edit - Assign Projection to adjust projection parameters after import.


To paste a table geocoded with UTM coordinates


1. Import the table.

2. Copy the table and paste as a drawing.

3. In the Paste As Drawing dialog, uncheck the Latitude / longitude coordinates box.

4. Choose the fields giving UTM coordinates for X / longitude and Y / latitude and press OK.

5. Open the drawing created by the Paste As operation.

6. Choose Edit - Assign Projection.

7. In the projection dialog, choose the UTM zone that was used for the table's coordinates and press OK.


Geocoded Tables Define Points by Default


When we consider a geocoded table like the examples above it is pretty obvious how each record in such a table corresponds to a single point. Each record has a single latitude/longitude location, so it makes sense to create a point at that table. GIS newbies will often ask, "Why not have a geocoded table that defines lines and areas too?"


That's actually not a bad idea and, in fact, is accomplished at a more expert level by using geometry tables. However, usually the request is to store lines in a table using coordinate columns that give lists of latitude and longitude locations, just as a geocoded table gives locations for points.


The problem that there is no obvious, single standard for defining what records in a table represent a line or an area. A line, for example, consists of a sequence of locations, each of which must be specified. What would such a table look like? Would there be one record that gives the beginning location of a line followed by a series of records that give each location in the body of the line until the last one? Where would the data attributes go? Would they be repeated for each record that gives one of the locations in a line? How would the table indicate that some records define points, some define locations in a line and some define locations in an area? How are multi-branched objects, like areas with holes or islands in them, represented?


It's fairly easy to work out a simple standard for representing points, lines and areas in tables. The problem is that everyone who has done so seems to have worked out a different standard. As a result, there is no single recognized way of encoding anything other than points within geocoded tables. Instead, people use various GIS formats, such as shapefiles or mid/mif files or other typical formats to save and exchange GIS information that contains lines and areas as well as points. If they want to use tables to exchange such information, they use a more sophisticated approach of using geometry tables, which have a "geometry" column that can for each record define the metric of areas, lines or points.


Manifold does allow one exception to the above, a method of saving coordinates in geocoded tables that allows lines to be created when a drawing is linked to that table. Although there is no standard GIS method for representing lines in geocoded tables, Manifold uses a simple convention that can be used to create lines in linked drawings from tables.


When importing or linking a drawing from a geocoded table, we may optionally specify a LineID column that will be used to identify which points will be used to draw lines. However, note that this convention applies only when creating linked drawings or importing drawings from geocoded tables and does not apply when copying a table and pasting it as a drawing. In the copy / paste operation only points are created.


Tech Tip: Changing Ordinary Fields in Tables does not Move Objects


This is a challenging idea for newcomers to GIS who may find it difficult to keep in mind the distinction between the original geocoded table used to create a drawing and the drawing's table.


Recall that every drawing in Manifold has its own table associated with the drawing. The drawing's table contains the data attributes (if any) for the objects in the drawing. If there are no data attributes, then the drawing's table will simply contain one column, the object ID, for each object.


The positions of objects in drawings are embedded within the geometry of the drawing without need to have latitude and longitude values listed in a table. Sometimes we create drawings from geocoded tables that have a latitude and longitude for each record. We might wish to have the latitudes and longitudes that were used to position the points immediately at hand in the drawing's table and so when pasting the geocoded table as a drawing we may tell Manifold to bring along the latitude and longitude fields along with the other fields.


Whatever the motive or process that was used to create a drawing, it is often the case that "latitude" and "longitude" numbers will be copied to fields in a table as well as being embedded within the geometry of a drawing. GIS beginners often mistakenly think that the positions of items in the drawing are determined from the listings of latitude or longitude fields in the table, so that editing these fields will move the objects. That's not the case.


We can use intrinsic fields to display in a table the actual latitudes and longitudes of objects associated with records. However, intrinsic fields are special, system-generated fields and are different than "latitude" and "longitude" fields we might bring into a table that are ordinary text or numeric fields. The rest of this topic explains why this is so.


Drawings are often created in Manifold from geocoded tables. A geocoded table is a table where each record has latitude and longitude fields that specify the location of that record.


Suppose we have an Access database that contains a list of towns together with their latitude and longitude coordinates:




We can import this Access table into a table in Manifold [either by importing the data or by linking to the external table]:




To create a drawing from this table we use Edit - Copy to copy the table and then we use Edit - Paste As - Drawing in the project pane to create a new drawing based on the data in the table. Each geocoded record will appear as a point in the drawing.




We can combine this new drawing in a map together with a base map of the United States to see at a glance where the various points are located


When we use the Edit - Paste As - Drawing command to create a new drawing, Manifold will automatically create the associated data attribute table for that drawing. By default, that new table will have at least one field, the object ID field. During the Paste As operation Manifold will give us the option of copying additional fields from the original table into the new data attribute table for the drawing. The dialog presents a list of check boxes for each field in the original table. We can copy any of the fields we desire.


For example, if our geocoded table is a list of customer records containing fields such as name, address, city, state, and so forth together with a latitude and longitude for each record, we might wish to copy the name, address and other informational fields. There is no particular technical reason to copy the latitude and longitude fields (these fields in the original table will be used automatically to position the points in the drawing for each record) but we can do so if for some reason we want to have this information in the table.


If we do copy the latitude and longitude fields into a table associated with the drawing, we will see them in the table window when the table is opened. It is very important to realize that these are just table fields that were copied from the original table. They are not a "handle" into the geometric coordinates that define the position of the points within the drawing's internal structure. Altering the values will not move the points, nor are the fields even necessary to keep the points "positioned" at the right spots.




For example, in the Table window we could delete the "Longitude" field.




We could delete the "Latitude" field as well. If we do so the points do not magically disappear from the drawing. They will still be there. All that we have done is delete a field from the data table associated with the drawing. The points in the drawing have already been created when Manifold read the original database. Changing the text data in the table (either the original one or the new table created with the drawing) changes nothing about those points and does not alter the coordinate numbers inside the drawing.


The above discussion may seem obvious to experienced GIS users; however, for new users it is easy to look at a drawing's table that is full of records with latitude and longitudes values for each and to forget that changing the values in the table does not actually move the points about.


How then can we change the actual location of points in a drawing? There are four main ways:


·      We can show the Latitude (I) and Longitude (I) intrinsic fields and then change their values. See Editing Intrinsic Fields in Tables . This is the best method when points must be moved to given coordinates.

·      We can right click on individual points in the drawing and use the Object - Coordinates dialog to change the location of the geometric coordinates that define the points. This is a seriously tedious approach.

·      We can use any one of a wide variety of interactive editing tools in a map or drawing window to move the points by selecting them and dragging them to a new location.

·      If the drawing is a linked drawing created from a geocoded table we can directly edit the latitude and longitude values in the external table and then use View - Refresh Data to update the drawing and move the points.


Note that neither of the above options will change the contents of any "latitude" or "longitude" text fields we might see in a drawing's table.


Remember that this discussion so far is about latitude/longitude values that might be copied into a drawing's table when that table is created. The situation with a table that controls a linked drawing created from a geocoded table is different. Linked drawings created from geocoded tables are drawings of points that are automatically created from and controlled by an external geocoded table using the latitude and longitude values in the table for each record. In addition to the external table that controls them, linked drawings (like all drawings) have their own tables as well.


Attempting to change Latitude (I) and Longitude (I) values in a linked drawing's own table won't move the points about in the drawing because these fields are read-only when a linked drawing is created from a geocoded table (the other, non-intrinsic fields usually will be read/write). However, changing values in the external table that controls the linked drawing and doing a Refresh Data will move points about.


A final subtlety: keep in mind that a linked drawing can be created from a geometry column in external tables. In such cases, the Latitude (I) and Longitude (I) values in the drawing's table will usually be read/write and may be edited to move the points about.




The Skip zero latitude / longitude records option is checked by default in the Paste As Drawing dialog. If checked, any records in the table that have a zero value in the latitude and longitude fields will not have an object created in the drawing. This is a safety measure so that when pasting geocoded tables that have zero values for latitude and longitude (and thus, are not really geocoded) spurious points will not be created for the zero valued records.


Creating Linked Drawings from Geocoded Tables


A geocoded table is a table where every record includes latitude and longitude values in decimal degrees notation that give the position of that record. A geocoded linked drawing is a drawing that is created from the data in a geocoded table and intermediate query. Geocoded linked drawings are read-only drawings that by default contain points and optionally may be configured to include lines as well. The location of each point in a geocoded linked drawing is taken from the geocoded table via an intermediate query.


To create a linked drawing from a geocoded table:


1. Choose File - Link - Drawing and open the desired file or connect to the desired data source.

2. Choose the table desired and check the desired fields. Specify the fields that contain latitude and longitude values (by default, the system will guess that any fields named "Latitude" and "Longitude" are the fields to use. Press OK.



Four new components will appear in the project:


·      A linked table - This linked table is a way of linking the table data from the geocoded table that will provide the data for the linked drawing.

·      A query - This query transforms the ordinary, geocoded data in the coordinate columns into geometry data from which the linked drawing will be created.

·      A linked drawing and the linked drawing's table - The linked drawing is like any drawing, except it is created dynamically from the data within the geocoded table.


Why the intermediate query? It is there to make life easier for inexperienced or casual users - the query makes it possible for Manifold to use very powerful, general purpose spatial technology for linked drawings while still retaining a simplified ability for less experienced or more casual users to easily create linked drawings from geocoded tables.


Geocoded tables are a great convenience for simple applications, but for big-time GIS work they are too limiting. A more powerful way of working is to use geometry tables, which save information about the objects they define in a geometry column. In fact, this is such a more powerful technology that all Manifold linked drawings are created from geometry data.


However, to save users from having to create geometry tables when they want to do something so simple it can be accomplished with geocoded tables, Manifold has special internal capabilities to allow us to create linked drawings from geocoded tables. When we attempt to create a linked drawing from a geocoded table, Manifold can tell what we are using is not a geometry table. In that case, it automatically writes a query that converts the geocoded table data on-the-fly into geometry data and then links the drawing from that query.


This retains the simplicity of a geocoded table while allowing all linked drawings to use a common internal technical framework. The chain of action from the original geocoded table into the geocoded linked table in the project through the query into the linked drawing is perfectly dynamic: changing a latitude or longitude in the original geocoded table will dynamically move the point associated with that record in the drawing. Adding a record to that geocoded table will create a new point in the drawing, and deleting a record will delete the corresponding point.




Let us see how this all works in a specific example.




Suppose we use Access to create an .mdb file with a geocoded table called Towns as seen above in a screenshot of an Access session. Each record has a latitude and longitude value as well as other information of interest for each town.


In Manifold, we choose File - Link - Drawing and then choose .mdb in the Files of Type box to open our Access .mdb file.




Manifold knows what we have is a geocoded table so it opens the Link MDB File dialog with appropriate options. Since we named our coordinate columns Latitude and Longitude Manifold can guess these are the ones we intend to use for our X / longitude and Y / latitude values so it will preload those column names into those boxes. We press OK.




The new components are created in the project pane.




If we pop open the linked table called Towns we see that Manifold simply linked a table into the project from the external Access .mdb table. There actually isn't any data saved within the project file for this table: the data is brought dynamically into Manifold from the Access .mdb. If we changed something in the external Access .mdb, such as the name of a town, that change would also appear in the linked table as well.




This linked table in the project is what creates the linked drawing, using the Towns Points query as a dynamic translator.


If all we want to do is utilize the linked drawing we don't need to understand how the query works or what it does. All we need know is that it transforms the simple geocoding style of data from the linked table into the more powerful geometry data form used by linked drawings.


However, for those who have a taste for spatial SQL and are curious about what that query does, we can pop it open in a query window and take a look.




Setting aside for a moment the query telling itself it's going to be doing things in Latitude / Longitude projection, we see the guts of the query use the NewPoint spatial SQL function to create a point from Latitude and Longitude coordinates. As is mentioned in the Spatial Extensions topic, the NewPoint(x, y) function "Returns a point with given XY coordinates in default coordinate system."


One very cool thing about linked drawings is that they can be created on-the-fly not just from tables, but also from queries. The query grabs the lat/lon data from the table and creates points from that data that are expressed as geometry data.




The linked drawing created from that query knows what to do with that geometry data. It makes points in the right positions, complete with all the other data that was in each record.




We can display the new Town Points Drawing in a map with our example US_Main drawing and see that the points are in the right place. Redmond is indeed up there in the rainy Northwest and Round Rock (the home of Dell Computer) is still in Texas.


Read Only Locations with Read / Write Data


One downside of linked drawings when they are created from geocoded tables is that they are basically read-only as regards position of the points. We can't drag points around in the linked drawing and expect the latitude and longitude values to change in the originating Access .mdb file. If we change the latitude and longitude values in the originating geocoded table the points will move in the drawing, but we cannot move the points in the drawing and cause the values in the table to change.


However, and this is totally cool, linked drawings are read / write for all other fields in their originating geocoded table. If we pop open the linked drawing's table in the Manifold project and change Round Rock to Red Rock, if we also pop open the Access .mdb file in Access, we will see that change in Access as well. If we change a value in the Access file we see the change in Manifold. Amazing!


Note: Changes will be displayed in Manifold whenever we trigger a refresh in an open window. This happens when a window opens or closes, pans or zooms or when we issue a View - Refresh command or, if we are using IMS if the web site has been told to refresh on some interval.


In fact, this is so much fun we recommend all Manifold users cobble up a geocoded table in Access, if only with three or four records in it, and then create a linked drawing from it in Manifold. Open Manifold in one window on your computer and open the linked drawing's table and also open the Access table in Access. Watch what happens as you change values in Manifold or in Access: the corresponding value will also change in the other window.




The screenshot above shows a Microsoft Access session opened with the Towns table from the towns.mdb file. Behind it is a Manifold session with a red arrow inserted into the illustration to indicate the point associated with the Round Rock, Texas, record. The Access table has that record highlighted.




If we delete that record in Access, we can see (after refreshing the view) that the point has disappeared from the Manifold drawing.


Uses for Drawings Linked from Geocoded Data


The intent of all this, of course, is not to have fun manipulating tables in one window while points move about or disappear from other windows. The great power we get from using drawings linked from geocoded tables is that we can keep data centralized within databases such as Access or SQL Server or even just tables in simple Excel spreadsheets and then use that data in GIS displays.


The beauty of this idea is that people or applications who know nothing whatsoever about GIS can continue to work with Access, Excel or our favorite DBMS program in their usual way, and automatically any changes they make to the data will be updated in our GIS display in Manifold.


Consider an example: Suppose we have a vehicle tracking application. Each truck in our fleet of trucks has a GPS device and a cell transmitter that reports the location of the truck. We would like to display the location of each truck in a web site that shows points at the location of each truck. When our truck equipment vendor sold us this system we also got some software that listens to the location reports and puts them into a table in our database.


To connect this table data to GIS and to the web we don't need to find a programmer who knows all about the truck hardware and all about databases and GIS and web programming as well.


Instead, we can divide the application into two parts using the common database table:


·      Acquire truck locations - Just about everyone in the business of selling GPS/location devices for trucks provides software that will update an Access table or some other database table with a record for each truck and the current latitude / longitude location of that truck. That's easy to do and we probably have plenty of people on our staff who know how to do anything we want with the database software we've been using all these years. They might not know GIS, but they know their DBMS.

·      Display truck locations - The Manifold part of the task is easy: create a linked drawing from the geocoded table that holds the truck locations and then do an IMS website that shows that drawing. The database and truck equipment people don't need to know anything about GIS, and our Manifold people don't need to learn how it is that the truck hardware communicates latitude / longitude locations into the database table. All they need know is wthe name of the table, the database it is in and the names of the columns that contain the latitude and longitude values.


The above approach is a great way of working with data from geocoded tables for many different types of applications. Whether we are keeping track of trucks, noting the locations of dealers in our retail network, grabbing the locations of recent earthquakes from a web site, marking the positions of favorite sushi restaurants or showing the sites of active fishing spots we can use the same method.


Some standard process with which we have long familiarity creates geocoded tables in our favorite database, which all our organization knows and loves. Without harassing anyone with GIS stuff, we use Manifold to create drawings linked from those geocoded tables. We can then work with those drawings in Manifold projects like any other, we can publish them in IMS web sites, we can serve them out as images using OGC WMS or a Manifold Image server, or we can use them to create things like PDFs from a print layout for publication. In all cases, whenever we use that linked drawing it will show the current data in the table at the moment of its use.


Exchanging Files


If we would like to exchange data with other users we should employ linked drawings with care if the linked drawing is linked from a table outside the Manifold project. If the table controlling a linked drawing is outside of Manifold it will not stored within the Manifold .map project file. Sending a colleague a copy of our .map file will not also send them the database table from which the linked drawing is created.


See Also


Linked Drawings

Create a Linked Drawing from a Geocoded Table

Formatting Lines in a Linked Drawing

About Geocoding


Street Address Geocoding

Spatial Geocoding with Match

Create a Map from a Geocoded Table

Project Pane - Paste as Drawing