A geocoded table has records with a latitude and longitude for each record. This example starts with a CSV file that contains a table providing a list of cities with a latitude and longitude field for the location of each city.
In this example's scenario we want to leave the CSV file as a read-only data source outside of our Manifold project, with no modifications to that file. We assume that some external process will update the CSV file. Therefore, instead of simply copying the data from the read-only data source and creating a table and drawing on a one-time basis, we need a more dynamic process that will allow us to automatically update our drawing. To do that, from time to time, we will update the drawing created in our project by running a query that automatically fetches data from that linked, external CSV file.
That requirement of a dynamic update prevents us from applying the technique shown in the Example: Create a Drawing from a Geocoded Table topic. In that topic, we alter a table already within our project to add a geom field and then we can easily create a drawing from that table. However, if our source table must stay outside of the project in a read-only data source we cannot add a geom field to that table.
Instead, we link the read-only CSV file into our project as a data source, we create an analogous table within our project that is writable, we create a drawing from that table,and we use a small SQL query to update the table in our project as desired from the external CSV file.
That allows us to leave the source data in an external, read-only file while having the freedom to create whatever drawing we like from that data within our project. Since the drawing is fully writable within our project we can also Style it as we like.
In the illustrations that follow we will show many small steps that an experienced Manifold operator might think are too basic to be worth mentioning. That increases the length of this topic but it will help avoid errors by new users.
To fit into this documentation, illustrations show a small Manifold desktop, with only a few panes, docked to the right side. In real life we use a much larger Manifold desktop, and more panes would be turned on, with panes docked to the left or to the right, or undocked, as we prefer.
We launch Manifold and choose File - Create - New Data Source. The dropdown menu provides a list of favorites to choose from as well as a More... option.
Choose More... to launch the New Data Source dialog.
In the New Data Source dialog we provide a short Name for the data source. We will call it Source. From the very long list of formats and sources in the Type box, we choose File: csv. We check the Open as read-only box. The .csv file may or may not in actually be read-only, but given that the point of this example is to show how to deal with read-only files we will force it to be read-only.
We click the [...] browse button to choose the CSV file desired.
In the Select File dialog we navigate to the folder where our desired .csv file is located, click on it to highlight it and then press Open.
Back in the New Data Source dialog we click Create Data Source.
This creates a new data source with a lock glyph in the database cylinder icon, indicating a read-only data source. Expanding the data source we see there is a Dots table within.
The Project pane provides a view of whatever is in the local project and it also provides a view, like a portal into another world, of whatever is within a data source that is linked into the project. When a read-only data source is linked into a project pane, whatever is within that read-only hierarchy exists in a read-only world, as indicated by red outline above. Anything within that read-only hierarchy can be opened and viewed, and we can read data from within that hierarchy, for example by copying or by reading from a query, but we cannot change what is within that read-only hierarchy. We cannot alter the schema of tables in that read-only world and we cannot change the contents of records within tables that are in that read-only world.
We can do whatever we like in the read / write part of the project, which is the world of our local .map project, outlined in green border above. We can right-click into that part of the Project pane hierarchy and create a new table or a new drawing if we like.
We will create a writable copy of the read-only Dots table. To do that, we will Copy the table from within the read-only data source, and we will Paste a copy within the writable, main part of the project.
We
click on the Dots
table to highlight it and then we click the Copy
button in the Project
pane toolbar.
In the main, read / write part of the project we Right-click.
In the context menu we choose Paste to paste a copy of the Dots table.
Alternatively,
we could have clicked the Paste
button in the Project pane toolbar.
Since the new Dots table is in a different path within the project it can have the same name as the table in the data source. Technically, the name of the table in the data source is [Data Source]::[Dots] while the name of the table in the main part of the project is [Dots]. However, it is a good idea to rename the new Dots table to avoid confusion.
We change the name to DotsGeom and then we double-click the table to open it. It appears with light gray background since so far it has no index and thus is neither writable nor selectable. In the illustration above we have right-clicked each column head and have chosen Best Fit Title to make the columns narrower.
We will add an index and a geometry field by launching Edit - Schema.
Following the instructions in the Add an Index to a Table topic, we press the Add Identity button to add an mfd_id identity field and an mfd_id_x index that is a btree index on the mfd_id field.
Press the Add
Identity button in the Schema dialog toolbar.
Immediately, an mfd_id identity field and an mfd_id_x btree index on that field appear. New fields and indexes are shown in provisional, bluish color in the schema. The two new items are also selected, with selection shown in red color, so the combination of red selection color and bluish provisional color is a light violet color.
Press the Add command
button and then choose Field in
the drop down menu.
In the Field dialog, enter the name Geom and choose type geom. We want the geometry field to use latitude and longitude coordinates, like those in the table from which the geometry will be built, so we will change the default Pseudo-Mercator coordinate system to Latitude / Longitude.
Press the coordinate picker button.
Choose Latitude / Longitude from the favorites list in the drop down menu. This is one of the two default favorites. If we wanted to choose different coordinate system, we could press the Edit Coordinate System choice to launch the full Coordinate System dialog to choose whatever other projection we wanted, or to specify a custom projection system.
Press OK.
Back in the Schema dialog we see the new field in provisional, bluish
color. To commit all changes we have made to the table, adding the
mfd_id and the
Geom field as well
as the mfd_id_x index,
we press Save Changes. 
Tech Tip: We could have chosen whatever name we wanted for the new field, but a useful Manifold convention observed by most Manifold users is to name geometry fields that use the geom data type with a name like Geom, Geom1, temp_Geom, or similar. That way, it is easy at a glance to see in a table which fields are geoms and which are something else. It sure beats using a name like Harry or Ringo for a geometry field.
The mfd_id identity field and mfd_id_x index will make the table editable. We do not need to add a spatial index on the Geom field because Manifold will do that for us when we create a drawing in the next step of this example.
The background of the DotsGeom table turns to white to show it is writable, since now it has an index. A Geom field appears, containing NULLs for all records since it has not yet been populated.
We right-click on the DotsGeom table in the Project pane and choose Create - New Drawing in the context menu. We have not yet populated the Geom field with geometry, so there is nothing for the drawing to show, but we will create the new drawing all the same so that the full infrastructure of a drawing ready to show geometry from a table will be in place, ready to go when we do populate the Geom field with geometry.
The New Drawing dialog automatically loads with the only geometry field in the table, and with the Latitude / Longitude coordinate system that geometry field uses. We press Create Drawing. A new drawing called DotsGeom Drawing appears in the Project pane.
We will now create a query that we can use to populate our new drawing by pulling fresh data from the linked CSV file that remains outside of our Manifold project. To create a new query we right-click into the Project pane and choose New Query from the context menu.
We write UpdateDotsGeom as the Name of the new query and then press the Create Query button. That creates a new query in the Project pane. We double-click the new UpdateDotsGeom query to open it in the Command Window.
We enter the following query text into the Command Window's query pane:
DELETE FROM [DotsGeom];
INSERT INTO [DotsGeom] (
[Name], [Latitude], [Longitude], [Geom]
)
SELECT
[Name], [Latitude], [Longitude],
GeomMakePoint(VectorMakeX2([Longitude], [Latitude]))
FROM [Data Source]::[Dots];
The query consists of two queries that will be executed one after the other when the query is run. The first query deletes all records from the DotsGeom table, so that the table does not contain any old and out-of-date information. The second query reloads the table with records taken from the linked, read-only Dots table, computing on the fly from the latitude and longitude coordinates a geom value to put into the Geom field.
The GeomMakePoint function used is the key part of the SQL that powers the Compose Point Transform pane template used in the Example: Create a Drawing from a Geocoded Table topic. In this example, we use the function to create geometry for us from the latitude and longitude values the query fetches from the external CSV file.
Important: We now close the Command Window, since we have entered the desired text into the UpdateDotsGeom query. We have created the infrastructure which will now update the drawing using data from the read-only Dots table.
We begin by double-clicking the DotsGeom Drawing to open it. We Shift-click the drawing's tab to undock the drawing window and then we resize it and position it as seen below.
The DotsGeom table is still open and docked. The drawing is empty because the Geom field it visualizes contains NULLs for all records.
We can update the table, and thus the drawing, by right-clicking on the UpdateDotsGeom query in the Project pane and choosing ! Run from the context menu.
Instantly, the DotsGeom table is populated with values, including the Geom field the query computes, taken from the Dots read-only table. The drawing instantly updates as well, showing the locations for the Geom values for each record. In the illustration above we have used the Style pane to make the dot size slightly smaller than default in the DotsGeom Drawing.
The DotsGeom Drawing lives in the Manifold project, and not in the read-only world of the read-only data source, so it is fully writable. We can launch Style to change the fill color for points to magenta. We can create a new data source using a Bing satellite imageserver as shown in the Example: An Imageserver Tutorial topic, and then we can drag and drop that as a background layer into the drawing. In the illustration above we have zoomed into the drawing a bit.
Important: The DotsGeom Drawing window is in Latitude / Longitude. We must zoom far enough into the window to not cross any +/- 180 longitude boundaries or the Bing layer will not appear, since re-projecting the Bing layer on the fly into too big a latitude and longitude range does not work.
We have finished our task with the steps above. If we like, we can experiment with the query by running parts of it in the Command Window to see what different parts do. We are not required to do this, but experimenting with parts of the query is a great way to learn how the query text works.
To see how the query operates, we can open it in the Command Window again. We Shift-click the title tab for the DotsGeom table to undock the table, and then we resize it and reposition it so that we can see the Command Window and also the table and the drawing at the same time. That results in a very busy arrangement of windows, but we must jam them together to fit into the illustrations for this topic. In real life, we would arrange the undocked windows in our Windows desktop with more space between them.
Manifold allows us to run only selected portions of query text in the Command Window. We can highlight the portion of the query as seen above. The highlighted query text is:
DELETE FROM [DotsGeom];
That first part of the query is a DELETE statement that cleans out the contents of the table, to make ready to insert updated data into the table.
To execute only the highlighted SQL, we either press Alt-Enter or choose View - Run Selection.
When we do that, all of the records in the table are deleted. The dots disappear from the drawing because there are no records with a Geom field for the drawing to visualize.
Next, we highlight the rest of the query text:
INSERT INTO [DotsGeom] (
[Name], [Latitude], [Longitude], [Geom]
)
SELECT
[Name], [Latitude], [Longitude],
GeomMakePoint(VectorMakeX2([Longitude], [Latitude]))
FROM [Data Source]::[Dots];
We press Alt-Enter to run the highlighted text and, like magic, the table is populated again and dots appear in the drawing.
Now would be a good time to Save the project.
Usage - We would use the above project to visualize locations provided by a read-only, external, geocoded CSV file. Many applications can create geocoded files in various formats. We might have a data acquisition application that collects GPS locations from a fleet of trucks. Every so often the application writes an updated CSV file. That file might be protected from changes by other applications with read-only status.
We can use Manifold to visualize the locations given in the CSV file using the project above. We can either refresh the drawing manually by running the query, or we could write a tiny script to automatically run the query on whatever refresh interval we like.
Column widths - For the illustrations in this topic we have used the Layers pane to adjust the column widths in the table for a prettier display, as seen below:
File - Create - New Data Source
Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing. This example shows how to add a spatial index using a geom field in a table.
Example: Create a Geocoded Table from a Drawing - A partner example to this topic. A geocoded table has records with a latitude and longitude for each record. This example starts with a table for a drawing of points where the geom field in the table contains geometry information for each point. We extract the Y and X locations for each point from the geom field to create latitude and longitude fields in the table for each record.
Example: Street Address Geocoding - Geocode a table of street addresses using the Google Geocoder.
Example: Create a Drawing from a Geocoded Table - A partner example to this topic. A geocoded table has records with a latitude and longitude for each record. This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city. We create a geom from the latitude and longitude fields using a template in the Transform pane and then we create a drawing that shows the cities as points. This example shows all the infrastructure steps involved.
Example: Create a New Data Source from a Manifold Image Server - Manifold image server modules are snippets of code which use the Manifold Image Server Interface (ISI) to automatically fetch image tiles from popular image servers like Virtual Earth, Wikimapia, Yahoo!, Google Maps, Yandex and many others. Image servers can provide street maps, overhead satellite imagery, combinations of streets and satellite imagery and other data as well. Using Manifold Image Servers is one of the most popular Manifold features.