Using SAS® and Google Earth™ to Access and

Display Air Pollution Data

RTSUG MeetingSeptember 10, 2008

Joshua DrukenbrodU.S. EPA

Office of Air Quality Planning Standardsdrukenbrod.josh@epa.gov

Improve access to emissions information

Provide information in easy-to-use format

Provide direct link to data

BackgroundPurpose

Previous “high-level” emissions pages on our web site…

Do not provide data in an “easy-to-use” format

Do not answer some of the most common questions

Do not provide the spatial, temporal, or sector-specific resolution many people need

Background

How are we changing that?

Linking the site directly to the data, providing…

National, state, and local summaries

Source category detail not previously available

Geospatial access not previously available

Background

SAS/IntrNet – What is it?

Component of SAS that enables dynamic access to programs and data via the Web

Anything you can program in SAS can be output to Web browser/Google Earth

Users don’t need to have SAS or know how to use SAS

How it Works

SAS program queries data

Workstation(Web Interface or Google Earth™)

Web Server(SAS/Internet®)

Database (Oracle, Access DB)

Data returnsto program

Web Page/Google Earth links to SAS service

Results displayed inWeb browser or Google Earth placemark

SAS/IntrNet – How does it work?

Program generates results

43

5

1 2

How it Works

Getting Started

A SAS/IntrNet service must be set up to utilize online capabilities.

A quick read through Google’s KML Reference guide will help with the basics of creating custom kml files.

Data must include either (lat & long) or (county & state fips) for point plotting in Google Earth.

Determine variable for analysis when creating coloring counties.

Determine a “color scheme” for counties.

Data MUST include either (county & state) FIPS or (county & state) name when coloring counties.

Plotting Points and Coloring Counties

Getting Started

Output data to an external file using the file extension kml.

Data is output through a series of PUT statements.

KML support of HTML provides increased flexibility on how to display data in the placemark windows.

Using SAS to develop these kml files allows for more efficient coding and provides the abilities to plot conditionally.

SAS provides county perimeter coordinates when developing county colored kml files.

Creating KML files

Plotting Points

Step 1: Prepare data for creating kml files. Standard cleaning of data. Determine if placemark elevation is a feature you

will like to use then determine variable to base elevation on.

Determine if data includes (lat & long) or (county & state fips).

Plotting Points

Step 2: Create kml file.

Name your kml file in filename path:

filename <fref> - <directory>

ex. Filename kmlfile ‘C:\Documents and Settings\user\Desktop\mykml.kml

Be sure to include .kml extension.

Plotting Points (Step 2 cont’d) Begin data step for creating kml. When creating kml files a

basic format is used in kml writing.<?xml version=“1.0” encoding=“UTF-8”?><kml xmlns=http://earth.google.com/kml/2.2>

<Document> <Style id=“A”> <IconStyle>

<scale>1</scale> <Icon>

<href> “location if placemark icon” </href> </Icon> </IconStyle> </Style> <Placemark> <name>A name here</name> <StyleUrl>#A</StyleUrl> <Point> <extrude>1</extrude> <altitudeMode> (relativeToGround , clampedToGround or absolute) </altitudeMode>

<coordinates>Long,Lat,Elevation </coordinates> </Point> </Placemark></Document><kml>

Plotting Points (Step 2 cont’d)A sample SAS data step is:

data _null_;

file <fref>;set <dataset> end=last;

if _n_ = 1 then do;put ‘<?xml version=“1.0” encoding=“UTF-8”?>’;

put ‘<kml xmlns=http://earth.google.com/kml/2.2>’;put ‘<Document>’;put ‘<Style id=“A”>’;put ‘<IconStyle>’;put ‘<scale>1</scale>’;put ‘<Icon>’;put ‘<href> “location if placemark icon” </href>’;put ‘</Icon>’;put ‘</IconStyle>’;put ‘</Style>’;

end;

Plotting Points (Step 2 cont’d) After this initial step we must prepare the lat & longs for the kml file by

removing white spaces.

points = ‘<coordinates>’||trim(left(xcoord))||’,’||trim(left(ycoord))||’,0 </coordinates>’;

Using the basic kml format and a put statement in SAS, we are able to plot our data in Google Earth.

………. put ‘<Placemark>’; put ‘ <name>A name here</name>’; put ‘ <StyleUrl>#A</StyleUrl>’;

put ‘ <Point>’; put ‘ <extrude>1</extrude>’;

put ‘ <altitudeMode> (relativeToGround or clampedToGround or absolute) </altitudeMode>’;

put points;

put ‘ </Point>’; put ‘ </Placemark>’; …………….

Plotting Points

Step 3: Closing the kml file. Closing the kml file in SAS is as simple as opening the

file:…………put ‘ <altitudeMode> (relativeToGround or clampedToGround or absolute) </altitudeMode>’;

put points;

put ‘ </Point>’;

put ‘ </Placemark>’; …………….

if last then do;

put ‘</Document>’;

put ‘</kml>’;

data _null_;file <fref>;set <dataset> end=last;

if _n_ = 1 then do; put ‘<?xml version=“1.0” encoding=“UTF-8”?>’;

put ‘<kml xmlns=http://earth.google.com/kml/2.2>’; put ‘<Document>’; put ‘<Style id=“A”>’; put ‘<IconStyle>’; put ‘<scale>1</scale>’; put ‘<Icon>’; put ‘<href> “location if placemark icon” </href>’; put ‘</Icon>’; put ‘</IconStyle>’; put ‘</Style>’;end;

points = ‘<coordinates>’||trim(left(xcoord))||’,’||trim(left(ycoord))||’,0 </coordinates>’; put ‘<Placemark>’;

put ‘ <name>A name here</name>’; put ‘ <StyleUrl>#A</StyleUrl>’;

put ‘ <Point>’; put ‘ <extrude>1</extrude>’;

put ‘ <altitudeMode> (relativeToGround or clampedToGround or absolute) </altitudeMode>’; put points;

put ‘ </Point>’; put ‘ </Placemark>’; if last then do; put ‘</Document>’; put ‘</kml>’;end;

run;quit;

Writing in Description BalloonsVery simple knowledge of html is required along with a few extra lines

of code.

…………………….

put '<Placemark>';

put '<description>';

put '<![CDATA[';

put (Any html coding is done between the line above and below);put ']]>';

put '</description>';

…………………….

Using SAS/Intrnet Service

Place link to service within the description tag.…………………….

put '<Placemark>';

put '<description>';

put '<![CDATA[';

link='<img src="http://www.epa.gov/cgi-bin/broker?_service=data'||

'&_program=dataprog.<Name of Program>.sas'||

'&year1='||"&year1"||

'&year2='||"&year2"||

‘&debug=0'||

'&site='||trim(left(site_id))||

'"width="460" height="360">';

put link;put ']]>';

put '</description>';

…………………….

County Coloring

Step 1: Prepare data for kml files. Standard cleaning of data. Determine variable for analysis. Determine color scheme. Data must include (county & state) fips and/or

county name.

County Coloring Step 2: Perform any analyses required on data and

determine levels for coloring counties. (i.e. Q1, median, Q3)

Assign levels to macro variables.Example:proc univariate data = USmap3 noprint;var dense;

output out = USmap pctlpts=30 60 90 pctlpre=P;run;quit;data test;

set USmap;call symput('first',P30);call symput('second',P60);call symput('third',P90);

run;quit;

County Coloring Step 3: Create variable for each observation to hold

hexadecimal color code. Note: hexadecimal color codes are slightly different

in Google Earth. The order is aabbggrr while standard web hexadecimal is rrggbb. aa = alpha (00 to ff). For alpha, 00 is completely

transparent and ff is completely opaque. bb = blue (00 to ff) gg = green (00 to ff) rr = (00 to ff).

The range of any one color is from 0 to 255. A list of hexadecimal colors may be found online.

County Coloring (Step 3 cont’d)

Temporary datastep example:data temp;

set mapdata3;length one 8 two 8 three 8;one = &first;two = &second;three = &third;

if dense >= three then do;color = 'c0000000';end; *Top 10 percent color is black for all pollutants;

if two <= dense < three then do;color = &color1;end; *60th-90th;

if one <= dense < two then do;color = &color2;end; *30th-60th;

if dense < one then do;color = &color3;end; *0-30th;

run;quit;

County Coloring

Step 4: Retrieve perimeter lat / long for each county. Lat / long stored in SAS maps.counties Lat / long stored in radians Dataset includes (county & state) fips

Lat / long must be converted to degrees. Lat / long must be ordered in counter-

clockwise order for Google Earth to plot correctly.

County Coloring Step 5: Prepare perimeter lat / long coordinates.

data county;set maps.counties;if state ^= &stfips then do;

delete;end;if density > 4 then do; *remove high density for lower resolution map;

delete;end;if segment > 1 then do;*remove multiple segments;

delete;end;if x = ‘.’ OR y = ‘.’ then do; *remove missing data in maps.counties;

delete;end;

*Convert radian lat / long to degrees;

pi = gamma(0.5)**2;long = x * -(180/pi);lat = y * (180/pi);elev = 3000; *preset elevation for kml plotting;

run;quit;

County Coloring Step 6: Putting lat / long in counter-clockwise order. Sort both datasets (temp & county) by county fips

then merge:data count;

set merged;by county;if first.county then counter=0 and num = 0;counter+1;if last.county then num = counter;

run;quit;

proc sort data = count;by county_name descending counter;run;quit;

data nums;set count;if num = '.' then do;delete;end;

run;quit;

County Coloring Step 7 & 8: Prepare array’s and do statements for

lat / long (Note: 2 counties & all of Alaska require a different method)

This step involves a smaller section of code which writes the arrays to hold the lat / long.

*write code to text file for arrays;data _null_;

set nums;file ‘C:\Documents and Settings\user\Desktop\arrays.txt’;;ifname = 'if county_name = "'||trim(left(county_name))||'" then do;';temp = lowcase(county_name);shrtname = substr(temp, 1, 8);cut = compress(shrtname,".","s");arname = 'array lo'||trim(left(cut))||'('||trim(left(num))||') long1-long'||trim(left(num))||';';arname2 = 'array lt'||trim(left(cut))||'('||trim(left(num))||') lat1-lat'||trim(left(num))||';';ends = 'end;';put ifname;put arname;put arname2;put ends;

run;quit;

County Coloring

This section writes the do statements.data _null_;

set nums;file 'C:\Documents and Settings\user\Desktop\dos.txt’;temp = lowcase(county_name);name = substr(temp, 1, 8);cut = compress(temp,".","s");name2 = 'if county_name = "'||trim(left(county_name))||'" then do;';dos = 'do i=1 to '||trim(left(num))||';';cords = 'cord = trim(left(lo'||trim(left(cut))||'(i)))||'',''||trim(left(lt'||trim(left(cut))||'(i)))||'',3000 '';';pcord = 'put cord;';ends = 'end;end;';put name2;put dos;put cords;put pcord;put ends;

run;quit;

County Coloring

Step 9: Prepare lat / long for storing in arrays and printing to kml file.

proc transpose data = count out = count1 prefix = long;

by county_name county_fips color;

var long;

run;quit;

proc transpose data = count out = count2 prefix = lat;

by county_name county_fips color;

var lat;

run;quit;

data latlong;

merge count1 count2;

by county_name;

run;quit;

County Coloring Step 10: Creating our county colored kml overlay.data _null_;

set latlong end=last;file <fref>;

If county_name = “” then do; delete;end;*Paste arrays written to text file here. Arrays must be defined and used in the same datastep.if _n_ = 1 then do;

put '<?xml version="1.0" encoding="UTF-8"?>'; put '<kml xmlns="http://earth.google.com/kml/2.2">'; put '<Document>'; put '<name>Alabama</name>';

end; style = '<Style id=“exNAMEL'||trim(left(county_fips))||'">'; *This is a style reference specific to kml and does not necessarily need county fips;

put style; put '<PolyStyle>';

col = '<color>'||trim(left(color))||'</color>'; put col; put '</PolyStyle>'; put '</Style>' ; put '<Placemark>'; put '<name>' county_name '</name>';

styleurl = '<styleUrl>#exNAME'||trim(left(county_fips))||'</styleUrl>'; *style ref call specific to kml; put styleurl; put '<Polygon>'; put '<altitudeMode>relativeToGround</altitudeMode>'; put '<outerBoundaryIs><LinearRing><coordinates>' ;

*Paste do statements written to text file here. This will call on the arrays and write the lat / long to the kml file; put '</coordinates></LinearRing></outerBoundaryIs></Polygon>' ; put '</Placemark>';

if last then do; put '</Document>'; put ‘</kml>’;

end;run;quit;

Special Cases

There are a handful of special cases: Denver, CO Miami-Dade, FL The state of Alaska

Maps.counties does not have perimeter coordinates for these cases.

Perimeters must be defined slightly different.

Special Cases Before creating the kml file a _null_ datastep must be performed to

define the perimeters.Ex. Using Denver, CO:

data _null_;set latlong;if county_name = "Denver" then do;initial = 8;call symput('denv0' , initial);call symput('denv1' , "-104.7337929,39.812544295,3000 -104.7876694,39.78331108500001,3000 -104.8649872,39.812544295,3000");call symput('denv2' , "-104.9022527,39.783884821,3000 -104.9709917,39.80164331300001,3000 -105.0523529,39.790769652,3000 -105.0523529,39.66758036,3000");call symput('denv3' , "-105.0769962,39.669301567,3000 -105.0620791,39.645805717,3000 -105.1245343,39.616572507,3000 -105.0523529,39.609714997,3000");call symput('denv4' , "-105.0523529,39.613130092,3000 -105.0523529,39.622883602,3000 -105.0523529,39.624031074,3000 -105.0523529,39.627473489,3000"); call symput('denv5' , "-105.0523529,39.629194697,3000 -105.0523529,39.63148964,3000 -105.0277096,39.628047225,3000 -105.0334469,39.65323696200001,3000");call symput('denv6' , "-105.0076561,39.678454021,3000 -105.0099511,39.660121793,3000 -104.9727402,39.66758036,3000 -104.8838931,39.624604809,3000");call symput('denv7' , "-104.8793032,39.652663227,3000 -104.8472287,39.658974321,3000 -104.9027991,39.66758036,3000 -104.907389,39.683617644,3000");call symput('denv8' , "-104.8649872,39.701949872,3000 -104.8838931,39.739761799,3000 -104.855261,39.768995008,3000 -104.7337929,39.768995008,3000");end;

run;quit;

Special Cases

After defining these macro variables in this fashion a simple do loop will insert these coordinates into the kml file.

if county_name = "Denver" then do;

%do i=1 %to &denv0;

put "&&denv&i";

%end;

end;

How do we expect to benefit?

Easier access to emissions information

Information in easy-to-use formats

Ability to answer more questions

Site easier to maintain

Efficient use of our data systems

Saves staff time and resources

Conclusions

Questions?