Example: Create a Drawing from Read-Only Geocoded CSV File

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.


That requirement 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.

Create New Read-Only Data Source



We launch Manifold and choose File - Create - New Data Source.




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.  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.   

Create a Writable Table within the Project

We click on the Dots table to highlight it and then we click the Copy button in the Project pane's toolbar.



We click into the lower part of the Project pane, to move the focus there, and then we click the Paste button to paste a copy of the Dots table.  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 [Source]::[Dots] while the name of the table in the main part of the project is [Dots].    However, it is easier to rename the new Dots table to remind us it is different.




We change the name to DotsGeom and then 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.

Add an Index and a Geom Field

We will add an index by launching Edit - Schema.


Following the instructions in the Add an Index to a Table topic, we add an mfd_id_x index that is a btree index on the mfd_id field.   




We also add a field called Geom of data type geom to the schema by clicking the <new field> entry, specifying Geom as the Field name and geom as the Type and then pressing Add. 




We do not need to add a spatial index on the Geom field because a dialog will do that for us when we create a drawing in the next step of this example.   We press OK.



The background of the DotsGeom table turns to white to show it is writable, since now it has an index.   A Geom field appears, for now containing NULLs for all records.

Create a Drawing

We right-click on the DotsGeom table in the Project pane and choose New Drawing in the context menu.




In the New Drawing dialog we click the coordinate system picker button so we can change the coordinate system to Latitude / Longitude the coordinate system we want since the coordinates in our geocoded CSV table are in degrees of latitude and longitude.




We choose Latitude / Longitude from the coordinate picker button's menu and then we press Create Drawing.   A new drawing called DotsGeom Drawing appears in the Project pane.

Create a Query

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:




INSERT INTO [DotsGeom] (

  [Name], [Latitude], [Longitude], [Geom]



  [Name], [Latitude], [Longitude],

  GeomMakePoint(VectorMakeX2([Longitude], [Latitude]))

FROM [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.    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 panel template used in the Example: Create a Drawing from a Geocoded Table topic.


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.

Run the Query to Update the Drawing



We begin by double-clicking the DotsGeom Drawing to open it.  We Alt-click the drawing's tab to undock the drawing window and then we resize it and position it as seen above.    We also double-click the DotsGeom table to open it.   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.




The DotsGeom Drawing lives in the Manifold project 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.




To see how the query operates, we can open it in the Command Window again.   We will undock the DotsGeom table and resize it and reposition it so that we can see the Command Window and also the table and the drawing at the same time.  


Manifold allows us to run only selected portions of query text in the Command Window.  We can highlight the portion above:






...and then either press Alt-Enter or choose View - Run Selection to run only the highlighted SQL.




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]



  [Name], [Latitude], [Longitude],

  GeomMakePoint(VectorMakeX2([Longitude], [Latitude]))

FROM [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 panel to adjust the column widths in the table for a prettier display, as follows:




See Also





Add an Index to a Table






Web Servers and Image Servers


Street Address Geocoding


Coordinate System


File - Create - New Data Source




Style: Drawings


Contents - Transform


Transform Templates


Transform Templates - Drawings


Command Window


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 panel 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.