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.  We assume that some external process will update the CSV file.    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 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

eg_create_dwg_readonly_table01_01.png

 

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

 

eg_create_dwg_readonly_table01_02.png

 

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.

 

eg_create_dwg_readonly_table01_03.png

 

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.

 

eg_create_dwg_readonly_table01_04.png

 

Back in the New Data Source dialog we click Create Data Source.

 

eg_create_dwg_readonly_table01_05.png

 

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.   

 

eg_create_dwg_readonly_table01_05a.png

 

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.

 

Copy / Paste to Create a Local, Writable Table

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.

 

eg_create_dwg_readonly_table01_05b.png

 

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

 

eg_create_dwg_readonly_table01_05c.png

 

In the main, read / write part of the project we Right-click.

 

eg_create_dwg_readonly_table01_06.png

 

In the context menu we choose Paste to paste a copy of the Dots table.  

 

eg_create_dwg_readonly_table01_06a.png

 

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 a good idea to rename the new Dots table to avoid confusion.

 

eg_create_dwg_readonly_table01_07.png

 

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.

Add an Index and a Geom Field

We will add a Geom field and 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.   

 

eg_create_dwg_readonly_table01_08a.png

 

Next, as seen above, we 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

 

eg_create_dwg_readonly_table01_08.png

 

The 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.   We press OK.

 

eg_create_dwg_readonly_table01_09.png

 

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

 

eg_create_dwg_readonly_table01_10.png

 

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.

 

eg_create_dwg_readonly_table01_11.png

 

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

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.

 

eg_create_dwg_readonly_table01_12.png

 

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.

 

eg_create_dwg_readonly_table01_13.png

 

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

Run the Query to Update the Drawing

eg_create_dwg_readonly_table01_14.png

 

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.

 

eg_create_dwg_readonly_table01_15.png

 

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 panel to make the dot size slightly smaller than default in the DotsGeom Drawing.

 

eg_create_dwg_readonly_table01_16.png

 

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.

Experiment with the Query

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.

 

eg_create_dwg_readonly_table01_17.png

 

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:

 

DELETE FROM [DotsGeom];

 

eg_create_dwg_readonly_table01_18.png

 

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

 

eg_create_dwg_readonly_table01_19.png

 

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.

 

eg_create_dwg_readonly_table01_20a.png

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

 

eg_create_dwg_readonly_table01_20.png

 

Now would be a good time to Save the project.

Notes

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:

 

eg_create_dwg_readonly_table01_09a.png

 

See Also

Tables

 

Drawings

 

Add an Index to a Table

 

Queries

 

Selection

 

Web Servers and Image Servers

 

Street Address Geocoding

 

Coordinate System

 

File - Create - New Data Source

 

Schema

 

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.