This article outlines a step-by-step process to turn a spreadsheet of data associated with geographic points into a Shapefile or GeoJSON which can be imported into TileMill. Before we begin, you will need:
- The latest version of QGIS installed.
- A spreadsheet of data with latitude and longitude coordinates for all the rows that you wish to display as points on the map (pre-prepped sample csv is here)
Spreadsheet as comma-separated values
QuantumGIS can’t import native Excel or OpenDocument spreadsheet files, so you will need to make sure your data is exported to the plain-text comma-separated values (CSV) format.
However, QGIS has a bug for handling text that includes commas, so when we export we need to actually make this file TAB delimited (but we’ll still call it a csv).
Choose File > Save as with the file type of “Text CSV (.csv)” but also make sure to check the “Edit filter settings” to ensure that during the save you can choose to use TAB delimited rows in this menu:
Now you will see that if we view that .csv in a text editor the file uses tabs between columns and the only commas are inside of text values – so we should be safe!
Importing to QuantumGIS
In QGIS, pull up your “Plugins” menu and select “Manage Plugins”. Click on “Add Delimited Text Layer” and press OK. Going back to the “Plugins” menu, you’ll see “Delimited Text” is now an option in this pull-down (if not you’ll need to hunt for the icon which should be on the menu bar.
Click on the “Add Delimited Text Layer” entry in the plugins menu or in the icon menu. Then navigate to your csv and QGIS should automatically fill in a few values. Make sure to check “Comma” for the delimiter (or what ever you exported with) and the grid display at the bottom will provide instant feedback on the impact of your choice.
You will notice that QGIS has done some intelligent guessing to determine which columns contain your X and Y coordinates. Verify that the longitude values are in the “X-field” and the latitude values are in the “Y-field”. Click on OK and you should have a layer of points representing the latitude and longitude of all of your rows in your CSV.
Now you should have your points on the map. With this sample data a QGIS bug appears where the text still has quotes around it – hopefully that will be fixed in future QGIS versions:
From here, right click on the layer in the layers panel and click “Save As”, which brings you to this menu:
For format, choose either ESRI Shapefile or OGR GeoJSON. Name your file and specify its output location. Unless you’d like a different encoding or CRS, leave these alone. Click OK and your file will be saved. If you chose Shapefile, you will need to create a zip file of the component .shp, .dbf, .shx, and .prj files before bringing them into TileMill.
Shapefiles vs. Geojson
The two main vector formats TileMill supports are ESRI Shapefiles and GeoJSON files. Shapefiles tend to be smaller in file size and faster at rendering, especially if they are indexed. Shapefiles come on average with 4 to 5 different corresponding files that need to be kept together to implement the data they contain. A GeoJSON, by contrast, is always a single plain text file.
One limitation of shapefiles is that the .dbf file, which contains all of the text and numeric data associated with each object in the shapefile, can only handle cell values of limited size. So, if you have columns in your spreadsheet whose entries are sometimes very lengthy (i.e. a “Description” column containing a large amount of text), you may want to use GeoJSON, which can handle any amount of text in your cells. Otherwise, the shapefile will truncate some of your cells’ values when they reach its limit.