Example: Convert CSV file of point locations to KML

Goal

Convert a CSV file containing point spatial features and attribute information into a KML file. The point features form the placemarks. Attributes of the features are used to determine folders. Summary statistics for the contents of each folder are also computed.

Input

CSV file containing latitude and longitude coordinates, name, and address information.

Full dataset 

Latitude,Longitude,Name,Address,City,ProvState,PostalZip,Country,Phone
38.389578,-81.834483,Tim Hortons,223 MacCorkle Ave,St. Albans,WV,25177,US,(304) 722-6515
38.465997,-82.658180,Tim Hortons,2206 13th St,Ashland,KY,41101,US,(606) 327-8819
38.472731,-82.628086,Tim Hortons,2524 Winchester Ave,Ashland,KY,41101,US,(606) 325-3093
38.739457,-82.995729,Tim Hortons,1130 Chillicothe St,Portsmouth,OH,45662,US,(740) 353-3734
39.130387,-82.980359,Tim Hortons,510 E Emmitt,Waverly,OH,45690,US,(740) 947-6007
39.263400,-81.558300,Tim Hortons,555 5th St,Parkersburg,WV,26101,US,(304) 428-4104
39.294093,-81.546474,Tim Hortons,3555 Murdoch Ave,Parkersburg,WV,26101,US,(304) 424-0790
39.337771,-82.951622,Tim Hortons,1100 E Main St,Chillicothe,OH,45601,US,(740) 779-3250

...

Script

/*================================
Converts CSV file of donut shop locations to KML
================================*/

baseDir = "C:\data\";
inFile = "donut.csv";

//--- 1. Read data from CSV

tCSV = null;
CSVReader tCSV file: inFile;

tCSVData = select * from tCSV offset 1;

//--- 2. Convert string input data to named/typed columns

tData = select
	Value.toDouble(col0) as lat,
	Value.toDouble(col1) as lon,
	col2 as name,
	col3 as address,
	col4 as city,
	col5 as provState,
	col6 as postalZip,
	col7 as country,
	col8 as phone
	from tCSVData;

//--- 3. Create folders from provState attribute, with count as extra information

tFolder = select provState as folderId, count(*) as locCount
	from tData
	group by provState
	order by locCount;

//--- 4. create shop features,with KML attributes

tFeatureKML = select
	"TH " + city + ", " + provState 	 as kmlName,
	Html.encode(address + ", " +
	city + ", " + provState + " " + country) as kmlDescription,
	Geom.createPoint(lon, lat)               as kmlGeometry,

	provState as folId,
	provState + ", " + country as region,
	country + " - " + provState as folderSort,
	"0" as folderVisibility

	from tData
	order by folderSort, kmlDescription;

//--- 5. join folder information to features

tKML = select tFeatureKML.*, tFolder.*,
		region + " (locations = " + locCount + " )" as kmlFolderName
		from tFeatureKML loc
		join tFolder f on loc.folId == f.folderId;

//--- 6. output KML, with custom icon for features

KMLWriter tKML
	comment: "Converted from " + inFile
	name: "Donut shop locations"
	description: "Donut shop locations in North America"
	iconStyleHref: "http://maps.google.com/mapfiles/kml/shapes/donut.png"
	iconStyleColor: "#ff" + Color.RGBtoBGR(Color.cssNameToRGB("BurlyWood"))
	file: baseDir + "donut.kml";




Description



    

  1. The input data is read from the CSV file.
Currently the CSVReader does not parse the column header line, so this is skipped
by requerying the table and using the offset clause.

  2. The CSVReader reads all input as strings, so the input columns
are named and typed by this query

  3. The folders for the KML file are created from the states/provinces
in which the outlets occur.  The count of outlets in each area is computed
to provide further information for the folder title.
The SQL group by clause makes this easy.

  4. The KML placemark name, description and geometry attributes are computed.
Each row of the computed table forms one placemark feature

  5. The folder information is attached to each feature

  6. The KMLWriter uses the feature table to generate the KML file.
Global settings for the icon for features are supplied as parameters to the procedure.




Output


Note how a default style is defined and specified for each output placemark.
Placemarks are grouped into folders according to the folder information
specified in each feature row.








<?xml version='1.0' encoding='UTF-8'?>
<!-- Generated by Jeql (ver. 0.3-alpha)    Wed Feb 27 22:30:03 PST 2008 -->

<!-- Converted from data/timmys.csv -->

<kml xmlns='http://earth.google.com/kml/2.1'>
<Document>
<name>Timmys Locations</name>
<description>Donut shop locations in North America</description>
<Style id='defaultStyle'>
  <LineStyle>
    <color>ffffffff</color>
  </LineStyle>
  <IconStyle>
    <color>#ff87b8de</color>
    <scale>1.0</scale>
    <Icon>
      <href>http://maps.google.com/mapfiles/kml/shapes/donut.png</href>
    </Icon>
  </IconStyle>
</Style>

<Folder>
  <name>AB, CA (locations = 202 )</name>

<Placemark>
  <name>TH Chestermere, AB</name>
  <description>#300 - 120 Chestermere Station Way, Chestermere, AB CA</description>
  <styleUrl>#defaultStyle</styleUrl>
<Point>
  <coordinates>-113.825925,51.049436</coordinates>
</Point>
</Placemark>

<Placemark>
  <name>TH Okotoks, AB</name>
  <description>10 - 18 Southridge Dr, Okotoks, AB CA</description>
  <styleUrl>#defaultStyle</styleUrl>
<Point>
  <coordinates>-113.980133,50.715446</coordinates>
</Point>
</Placemark>

<Placemark>
  <name>TH Sherwood Park, AB</name>
  <description>100 - 664 Wye Rd, Sherwood Park, AB CA</description>
  <styleUrl>#defaultStyle</styleUrl>
<Point>
  <coordinates>-113.273125,53.512302</coordinates>
</Point>
</Placemark>

...