Google Spreadsheet Data API

I gave a presentation to the U. of Delaware WebDev group and IT-CS&S (RDMS) on the topic of using data from Google Docs Spreadsheets for consumption in a scripted workflow. I'm using this in the UD Map for automatically incorporate rendering decisions from OCM.

The gist is that you open an https stream with headers for
authorizing, capture an authorization token on the response, open
another stream returning an xml of the table, and parse that. In
addition, you could form the url for the second stream with parameters
that query the column of interest, for example.

Here is the presentation:

https://docs.google.com/present/view?id=dx7rb3j_104dxjkq8c6

Here is some sample code (excuse references to my database/tables):

function do_post_request($url, $data, $optional_headers = null)
{
 echo "Begin do_post_request \n";
 $params = array('http' => array(
    'method' => 'POST',
    'content' => $data
  ));
 if ($optional_headers !== null) {
$params['http']['header'] = $optional_headers;
 }
 $ctx = stream_context_create($params);
 $fp = @fopen($url, 'rb', false, $ctx);
 if (!$fp) {
throw new Exception("Problem with $url, $php_errormsg");
 }
 $response = @stream_get_contents($fp);
 if ($response === false) {
throw new Exception("Problem reading data from $url, $php_errormsg");
 }
 return $response;
}

function doFromGoogle(){
echo "Begin doFromGoogle \n";
define("GURL", "https://www.google.com/accounts/ClientLogin");
define("SPREADSHEETREFKEY", "***YOURKEYHERE***");

//creates post to google auth, creates $auth string from google
$dataArr = array();
$dataArr['accountType'] = 'GOOGLE';
$dataArr['Email'] = '***YOUREMAILHERE***';
$dataArr['Passwd'] = '***YOURPASSWORDHERE***';
$dataArr['service'] = 'wise'; //name for spreadsheet data service
$dataArr['source'] = '***YOURSORUCENAMEHERE***'; //this may be optional

$dataStr = http_build_query($dataArr);

$opt_headers = 'application/x-www-form-urlencoded';

$responseStr = do_post_request(GURL, $dataStr, $opt_headers);
$responseArr = explode("\n",$responseStr); //breaking apart request to get auth token
$auth = explode("=", $responseArr[2]);
$auth = $auth[1];

$authStr = 'Authorization: GoogleLogin auth=' . $auth;
$url = 'https://spreadsheets.google.com/feeds/list/' . SPREADSHEETREFKEY . '/1/private/full';


$opts = array(
 'http'=>array(
'method'=>"GET",
'header'=>"$authStr"
 )
);

$context = stream_context_create($opts);

// ini_set('Authorization', "$authStr");

$handle = fopen($url, "r", false, $context);
$contents = stream_get_contents($handle);
fclose($handle);

$xml = new SimpleXMLElement($contents);

$fullquery = null;


$count = 0;
foreach ($xml->entry as $entry){
$count = $count + 1;
$id = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->osmid;
$render = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dorender;
$label = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dolabel;
$labelname = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->labelname;
$maxzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->maxzoom;
$minzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->minzoom;
$labelname = addslashes ($labelname);

//$color = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->color;

//print "$id, $count \n $render \n $label \n $labelname";
print "$id  $maxzoom $minzoom\n";

$query = null;

if($render <> ''){
 $query .= "UPDATE master SET dorender = '" . $render . "' where osm_id = $id and dorender <> '" . $render ."';\n";
}
if($label <> ''){
 $query .= "UPDATE master SET dolabel = '" . $label . "'where osm_id = $id and dolabel <> '" . $label ."';\n";
}
if($labelname <> ''){
 $query .= "UPDATE master SET labelname = '" . $labelname . "'where osm_id = $id and labelname <> '" . $labelname ."';\n\n";
}
if($maxzoom <> ''){
 $query .= "UPDATE master SET maxzoom = '" . $maxzoom . "'where osm_id = $id;\n\n";
}
if($minzoom <> ''){
 $query .= "UPDATE master SET minzoom = '" . $minzoom . "'where osm_id = $id;\n\n";
}

$fullquery .= $query;
}

//print($query);
$result = pg_query($fullquery) or die('Query failed: ' . pg_last_error());
}

Replacing Tomcat .jsp pages with Apache httpd .php pages

It turned out that this task was much easier than I'd expected. Here are the steps I took:

1. Edit the httpd.conf file (default path: C:\Apache2.2\conf\httpd.conf). Remove tomcat references to the services (JkMount). Add Redirect 301 lines to .php proxy
2. Create php proxy which is referenced by Redirect 301. Arguments are passed by the redirect, which can be handled by this proxy. Here is the code I used for a single argument, but could easily be extended to other arguments. This also saves arguments that are not handled to a log file:

   $out = array();
   $log = '';
   
   foreach($_GET as $key => $value){
      if (strpos($key, 'max')){};
      switch ($key) {
          case bldgcode:
              $out['id'] = $value;
              break;
          default:
              $log .= 'key is ' . $key . ' and value is ' . $value . "\n";
              break;
      }
   
   }
   
   $fp = fopen('log', 'a');
   fwrite($fp, $log);
   fclose($fp);
   
   $url = 'http://maps.rdms.udel.edu/map/index.php?' . http_build_query($out);
   
   header("Location: $url");

WHERE NOT IN and NULL in Postgresql

NULL values trip up the WHERE NOT IN condition in Postgres. To fix this behavior, use a conditional (not null) in the subquery.

source: http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/

Duplicate OSM Features in UD Campus Map

I noticed a couple places where we have two polygons on top of each other (particularly for parking polygons) in the UD Campus Map. I just wanted to note, if you're trying to modify an existing polygon on the map, make sure that you're changing the existing shape, rather than adding a new one (otherwise we're loosing attributes on the exisiting shape).

Here are two screenshots to demonstrate what I'm talking about (note that only the second polygon retains attribute information/properties as listed in the right-hand sidebar), though it appears the first polygon was intended to replace the second.

I've fixed this problem by adding nodes to the second polygon (the original one that has attributes/properties) and dragging them to match the footprint of the first (new, intended to replace the first), and deleting that one.

Zoom to extent of new vector layer in OL

Zooming to the maximum extent of all features in a vector layer does not work unless all features have been loaded. Therefore it is necessary to register an event handler, as demonstrated below ('layer' is any object of class OpenLayers.Layer.Vector):

layer.events.register('featuresadded', map,function(){this.zoomToExtent(layer.getDataExtent())});

Random Feature Selection

I met with a gentleman today who needed to select random streets in Wilmington in order to do a field survey of urban forestry there. Here are the steps we took to do so:

-Use Excel and the RANDBETWEEN function to generate random numbers in a range = total number of features (in this case, streets in Wilmington). Drag this equation down a column = number of features in sample. Once this has been done, save/export (I usually like to use DBF for ArcGIS)

RANDBETWEEN(bottom,top)

Bottom is the smallest integer RANDBETWEEN will return.

Top is the largest integer RANDBETWEEN will return.

- with data added to ArcGIS, use "Frequency" tool to check that no duplicate values were generated

- do a regular one to one join, dropping out all values that don't match. The result will be a random set of features -- in this case, street segments.

View KML Source from MyMaps

Google MyMaps allows you to export KML by using the "Export to Google Earth" feature. If you copy/paste the URL that this button links to you get a KML, but it is a network reference to the actual KML. If you copy/paste the URL referenced in this KML in your browser, you'll get a warning "{errorText:"Unable to contact server."}". To get past this warning, you must decode the encoded URL reference. That means getting rid of all the "amp;" strings.

For example, change:

http://maps.google.com/maps/ms?ie=UTF8&hl=en&vps=1&jsv=250a&oe=UTF8&msa=0&msid=113097578162956796020.00048fc123b72558b50ff&output=kml

to

http://maps.google.com/maps/ms?ie=UTF8&;hl=en&vps=1&jsv=250a&oe=UTF8&msa=0&msid=113097578162956796020.00048fc123b72558b50ff&output=kml

and you'll get the actual kml. You do not need to do this within any script or program, since those tend to encode URLs anyway. However, it is very useful for debugging issues with KML.