CSV files are a popular way of providing geocoded tables. In this example, we import a CSV file by creating a New Data Source, copying and pasting the table conveyed by the CSV, prepping the table for use as a geocoded table and then creating a drawing.
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 will acquire data from a file called amphitheaters.csv, which provides the location and other data for Roman gladiatorial amphitheaters in the former Roman empire.
Opening the .csv file in Notepad we see it is a typical .csv file, with the names of fields in the first line and text values set off by double " quote characters.
We can bring data from CSV files into a project either by linking the CSV file into the project using File - Create - New Data Source or by importing data directly using File - Import. See the Importing and Linking topic for discussion on the difference between importing and linking. In this example we will use File - Create - New Data Source to show the greater options that dialog allows.
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 choose File: csv as the Type and we click the [...] browse button.
We browse to the location of our file, click it to highlight it and to load the name into the File name box and then we press Open. Windows associates .csv files with Excel if we have Microsoft Office loaded on our machine, so such files will be shown with an Excel icon.
That loads the path to the file into the Source box. We click Create Data Source.
A new data source appears in our Project. We can open it to see the amphitheaters table within. The data source is linked from the .csv file. Everything within that linked data source is stored within the .csv file. See the discussion in the Importing and Linking topic.
Connecting to a .csv file by creating a new data source provides many options, but now that we have that connection we would prefer to bring the amphitheaters table into the main, local part of the Manifold project, where we can work with it with greater flexibility. We do that by Copying the table within the data source and then Pasting it into the main part of the project.
We open the data source and click the amphitheaters table within to highlight it, and then we press Ctrl-C to Copy the table.
We click into the main part of the project, outside of the data source, to move the focus outside of the data source.
We press Ctrl-V to Paste. This pastes a copy of the amphitheaters table into the Project. We have copied the table from within the CSV file and have pasted a copy of the table within our project, where it will be stored within the project file without any link back into the CSV file.
We can now click on the data source to highlight it and then we press Delete to delete the data source. The end result is a project with an amphitheaters table in it, as though we had used File - Import to import the table directly from the CSV file.
Why did we not simply use File - Import to import the table directly from the CSV file? Why did we add the complication of creating a data source and then copying the table from the data source into the project? We did that because creating a data source provides more options for dealing with CSV files than does the simplified File - Import dialog.
We double-click the amphitheaters table to open it. It opens with light gray background, since it does not have an identity field and index and is thus read-only. CSV files are not database files and cannot contain indexes.
We Shift-click the amphitheaters table's name tab to undock the table window. That allows us to widen the window a bit to provide a better display within the limited space available in this documentation. We see that the amphitheaters table as brought in from the CSV contains numerous fields, including Latitude and Longitude fields that, by the look of their contents, provide latitude and longitude locations for each amphitheater.
Tables must have an identity field and index to be writable. See the discussion in the Editing Tables topic. Adding those to the table is easy, a one click command, to add an mfd_id field and an mfd_id_x index to the table as described in the Add an Index to a Table topic.
Choose Edit - Schema to launch the Schema dialog. We see that most of the fields are nvarchar text data types, but that the Latitude and Longitude fields have been brought in as numeric, float64 data types.
In the Schema dialog we press the Add Identity command button.
Instantly, an mfd_id field and an mfd_id_x index appear in provisional, bluish color, ready to be added to the table. The two new items are also selected, which would be shown in red color, so the combination of red selection color and bluish provisional color results in a shade of violet.
While we have the Schema dialog open, we will add another field that will be used to create a drawing from the table.
Next, we will add a geometry field called Geom, which we will use for geometry from which we can create a drawing.
Press
the Add command button
and then choose Field in the drop down menu.
In the Field dialog we enter the name Geom and choose type geom for the new field. We could name the field anything, but Manifold users will usually name the geometry field of a table Geom if it uses type geom so that it is instantly clear when looking at a table's fields which one is the geometry field.
We know that the Geom field
will contain geometry created from latitude and longitude values, so we
click on the coordinate picker button and choose Latitude
/ Longitude for the coordinate system. Latitude
/ Longitude appears in the short list of Favorites, being one of
the default two favorites that is built in and available even if we have
not added any of our own Favorite
Coordinate Systems.
Press OK.
Back in the Schema dialog, we review our work and then press Save Changes to commit the changes to the table, adding the mfd_id identity field, the mfd_id_x index and the Geom geometry field.
The table now has an identity field and index and is fully writable, with all fields appearing in white color indicating they are fully read/write. The new Geom field appears as well, for now filled with NULLs for all records since we have yet to populate it.
The table is visually busy, so to simplify it we will hide some fields.
We use the Layers pane, shown above undocked to allow a larger illustration, to adjust field sizes and turn off some of the fields we will not use in this example. That will reduce visual clutter in the illustrations.
We can now clearly see the Latitude, Longitude and Geom fields we will be using.
Next, we will use the Transform pane to populate the Geom field with point geometry created from the Latitude and Longitude fields. This is a typical task when creating drawings from geocoded tables, as seen in the Example: Create a Drawing from a Geocoded Table topic. A Transform template makes that easy to do.
With the focus on the drawing, in the Transform pane, we choose the Geom field. We double-click the Compose template to launch it.
In the Compose template we choose point as the Compose option.
From the X box pull down menu we choose the Longitude field. From the Y box pull down menu we choose the Latitude field.
For the Result destination, we choose Same Field, to save the new point geometry that will be created into the Geom field.
Press Transform.
The template populates the Geom field with point geometry for each record that is computed from that record's Latitude and Longitude values.
We now have a table with a geometry field for each record, from which we can create a drawing. We right-click on the amphitheaters table in the Project pane and choose New Drawing.
The New Drawing dialog opens with reasonable options. The coordinate system appears as Latitude / Longitude in black because the system knows that is the coordinate system we specified when creating the Geom field.
We click Create Drawing.
A drawing called amphitheaters Drawing is created in the Project pane. We can double-click it to open it in a window.
A more interesting display would be to use the new amphitheaters Drawing as a layer in a map, seen against background layers that provide cartographic context.
In the illustration above, we have created a map with a Bing satellite layer base layer. We have also created a new data source that is a Google transparent streets image server, and we have dragged and dropped that into the map as a layer. At the scale seen above, the Google layer results in country boundaries being overlaid on the satellite display. We then dragged and dropped the amphitheaters Drawing into the map as the uppermost layer. We have also used Style to color the dots in the amphitheaters Drawing layer using yellow color.
We can see that the CSV file containing the locations of Roman amphitheaters has resulted in a drawing with dots at the location of each such amphitheater.
We can use the data set for practical purposes. For example, suppose we are planning a trip to Paris and we would like to visit any Roman amphitheaters in the area. Much to our surprise, zooming in we see there is a yellow dot right in Paris.
We can zoom further in to see where it is located, right in the heart of Paris.
In the illustration above, we have added a Google satellite layer since that provides higher resolution than Bing within Paris. We have Alt-clicked the yellow dot to call up the Info pane display that shows the fields, giving information for that record from the table, such as the name, Arènes de Lutèce, and a link to a wikipedia photo.
Zoomed all the way in, we see there is a remarkably well preserved Roman amphitheater, or, at least a good part of it, right in the middle of Paris, hidden in a small park that is not visible form the adjacent Rue Monge, see at left in the view above. The amphitheater was discovered between 1860 and 1869 during Baron Haussmann's renovation of Paris and the construction of the adjacent Rue Monge.
This particular amphitheater was built as both as an arena and a theater. The structures visible to the right in the view above were the foundation for a large wall or backdrop to enable use as a theater. Statues were placed in the semicircular niches. During theater performances, wooden seating was placed on the arena floor in addition to the permanent stone arena seats. When used as an arena for gladiator combat or other arena spectacles, wooden seating was placed on the elevated stage area at right. The stone arena seating to the left was destroyed during the construction of Rue Monge and the modern buildings on that side.
It is the most ancient structure in Paris, yet virtually no tourists know about it and even few Parisians have seen it, because the park is hidden from the street and is accessed through a modest portal in the facade of buildings on Rue Monge. The portal is seen above in a Google Street View, with an arched window over the green doors. There is no view of the Roman amphitheater on the back side of the building. Rue Monge is a popular route to bypass some of the more jammed streets during rush hour when headed to destinations south of Paris. Millions of people (including the author of this topic) have driven on Rue Monge for years without knowing they have driven past a Roman amphitheater.
When visiting Paris, the amphitheater is a ten to fifteen minute walk at a relaxed pace from Notre Dame cathedral or from the medieval museum and ancient bath complex at Cluny. It is worth a visit to see in person a reminder of the deep antiquity and Roman past of Paris.
File - Create - New Data Source
Example: Create a Geocoded Table from a Drawing - A partner example to Example: Create a Drawing from a Geocoded Table 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: Create a Drawing from a Geocoded Table - A partner example to Example: Create a Geocoded Table from a Drawing 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 Drawing from Read-Only Geocoded CSV File - A detailed example using a mix of dialogs and SQL to create a drawing that shows data from an external, read-only geocoded CSV file that is linked into the project.