Example: Import CSV and Create a Drawing

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.  

 

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.

Connect to the CSV File

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.   

Make a Local Copy of the Table

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.

Add an Identity Field and Index to the Table

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.

 

While we have the Schema dialog open, we will add another field that will be used to create a drawing from the table.

Add a Geometry Field to 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 identify 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.

Clean Up the Display

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.  

Populate the Geometry Field

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.

 

We switch to the Transform pane, shown below undocked to allow a larger illustration.

 

 

In the Transform pane we choose  Geom as the target field and then we click on the Compose Point template to choose it.  We choose the Longitude field in the X box and we choose the Latitude field in the Y box. The system shows a preview in the table (not illustrated).   

 

We press Update Field to apply the template.

 

 

The result is a Geom field in the table that is populated for each record with point geometry computed from that record's Latitude and Longitude values.

Create a Drawing

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.  

 

 

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 heart of Paris, hidden away from most tourists in a small park.

 

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 Record - Values display that shows the fields, giving information for that record from the table.

 

See Also

Importing and Linking

 

Tables

 

Data Types

 

Add an Index to a Table

 

File - Create - New Data Source

 

CSV, ASC, PSV, TAB

 

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.