I'm currently exploring different integrations that can be done between Google App Script and Git. This is part of a bigger attempt to integrate Git data into my project management system. I'll post more on that later. GitHub supports a number of very nice sub-systems. One of them is the GitHub Issues ticket tracking system. It's fairly robust and comes with GitHub - which means it's highly integrated out-of-the-box. Integration with Google's App Script is done via the fetchURL command and JSON object manipulation. After a good bit of experimentation, I've settled on querying GitHub Issues with an "on open spreadsheet event" to avoid issues with Google's quota system. Essentially, Google prevents you from calling the fetchURL command more than X times (see the quota limits ) per day. That's not great if you have people actively working and changing data in a spreadsheet. Some of my other App Script routines are running thousands of times per d
A few weeks ago I started down the path of discovering how to GeoCode addresses for Delphi. This is related to a project we had where we wanted to show a map of a given point and then show facilities located near it on a map. I had originally started working with maps.live.com (which is a MS site). They have a decent little interface for JS that seemed to work for what I wanted AND I didn't have to store the annoying little GeoCoding addresses anywhere. This works great if all you want is a single address. For instance, the code below works for single addresses:
The problem showed up when I went to add the facilities to the map. Thinking it would all work the same way, I added the same Map.Find code for each facility in the state of interest and voila. Except the voila was, "How come I only get one pushpin?" not "See how great this works!" It turns out that part of the answer is the MS VEMap object works asynchronously. I could live with that if the map moved around a little as it added pushpins... well, at the very least, I could just hide the map until it was done.
Unfortunately, MS also made the calls non-reentrant. This is actually what they document the problem as. To me, the code IS non-reentrant in that it never calls itself. Apparently, our definitions of re-entrant differ significantly. To MS, non-reentrant means that you have to wait for the asynchronous call to complete before you can call it again. Of course, Javascript has little support for "waiting". I was able to create a work around using a do {} while (!completed); but it causes the browser to complain mightly, ties up a lot of CPU and is just generally ugly.
Seeing that this wasn't going to work very well, I went back to the drawing board. If I actually could provide the Lat/Lng for each object, the problem would go away. It turns out that there are not very many services that want to let you do this for free using a desktop tool. If you are willing to do a web mashup you can get away with quite a bit, but the traditional languages are left out in the cold for the most part.
This is where MapQuest comes in. They have a free service (you have to register, but that's it) that will let you have access to their mapping systems. Of course, they have no interface for Delphi, but they did publish their XML specifications so it's not too hard to write one. Keep in mind that you have to comply with the MapQuest TOS. The major one for me was that you can't use the Lat/Lng addresses with any other system than theirs. That means I can't use maps.live.com to produce my maps, I have to figure out how to use MapQuest. Considering my other choices was a complete web mashup rewrite or some commercial system that was going to cost a fortune, I think I'll live within those parameters.
I've only implemented the single interface I needed so far (GeoCoding), but the other interfaces are equally easy. This code is virtually identical (in the HTTP POST) as my DHL code. I've reposted the whole thing here for those needing a complete unit but will likely factor it out for my personal use.
Enjoy!
1. This is how the classes are used. I assume you recognize that the txtXXX are just text box fields and the resolver form has a single control on it that is a list box (other than the Select / Cancel buttons).
2. And this is the actual unit that does the work. BTW, the XML parser I'm using is OpenXML, a freely available, full source implementation that works for Delphi and Kylix.
<html>
<head>
<title>Address Lookup </title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<script type="text/javascript" src="http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6"></script>
<script type="text/javascript">
var map = null;
var findPlaceResults = null;
function GetMap()
{
map = new VEMap('myMap:newmap');
map.LoadMap();
map.Find(null,'100 Enterprise Way, Scotts Valley, CA 95066', null, null, null, null, true, true, null, true, GetCoordinates);
}
function GetCoordinates(layer, resultsArray, places, hasMore, veErrorMessage)
{
findPlaceResults = places[0].LatLong;
var myShape = new VEShape(VEShapeType.Pushpin, findPlaceResults);
myShape.SetTitle('CodeGear');
myShape.SetDescription('100 Enterprise Way<br>Scotts Valley, CA 95066<br>'+findPlaceResults.toString());
map.AddShape(myShape);
}
</script>
</head>
<body onload="GetMap();">
<div id='myMap:newmap' style="position:relative; width:800px; height:600px;"></div>
</body>
</html>
The problem showed up when I went to add the facilities to the map. Thinking it would all work the same way, I added the same Map.Find code for each facility in the state of interest and voila. Except the voila was, "How come I only get one pushpin?" not "See how great this works!" It turns out that part of the answer is the MS VEMap object works asynchronously. I could live with that if the map moved around a little as it added pushpins... well, at the very least, I could just hide the map until it was done.
Unfortunately, MS also made the calls non-reentrant. This is actually what they document the problem as. To me, the code IS non-reentrant in that it never calls itself. Apparently, our definitions of re-entrant differ significantly. To MS, non-reentrant means that you have to wait for the asynchronous call to complete before you can call it again. Of course, Javascript has little support for "waiting". I was able to create a work around using a do {} while (!completed); but it causes the browser to complain mightly, ties up a lot of CPU and is just generally ugly.
Seeing that this wasn't going to work very well, I went back to the drawing board. If I actually could provide the Lat/Lng for each object, the problem would go away. It turns out that there are not very many services that want to let you do this for free using a desktop tool. If you are willing to do a web mashup you can get away with quite a bit, but the traditional languages are left out in the cold for the most part.
This is where MapQuest comes in. They have a free service (you have to register, but that's it) that will let you have access to their mapping systems. Of course, they have no interface for Delphi, but they did publish their XML specifications so it's not too hard to write one. Keep in mind that you have to comply with the MapQuest TOS. The major one for me was that you can't use the Lat/Lng addresses with any other system than theirs. That means I can't use maps.live.com to produce my maps, I have to figure out how to use MapQuest. Considering my other choices was a complete web mashup rewrite or some commercial system that was going to cost a fortune, I think I'll live within those parameters.
I've only implemented the single interface I needed so far (GeoCoding), but the other interfaces are equally easy. This code is virtually identical (in the HTTP POST) as my DHL code. I've reposted the whole thing here for those needing a complete unit but will likely factor it out for my personal use.
Enjoy!
1. This is how the classes are used. I assume you recognize that the txtXXX are just text box fields and the resolver form has a single control on it that is a list box (other than the Select / Cancel buttons).
uses mapquest_api, w_AddyResolver;
procedure TForm1.btnGeoCodeClick(Sender: tobject);
var geocode:TMapQuestGeoCoder;
index, ix:integer;
begin
geocode:=TMapQuestGeoCoder.Create; //create the geocoder object
geocode.Password:='YOUR PASS'; //assign the password and client id you got from mapquest
geocode.ClientID:='YOUR ID'; //this will NOT work without it!!!
geocode.Address := edtAddress.Text; //assign the address info you want to code
geocode.City := edtCity.Text;
geocode.State := edtState.Text;
geocode.PostalCode := edtPostalCode.Text;
geocode.Country := edtCountry.Text;
if geocode.getFromMapQuest(true) then //this will send the info to mapquest and return the results
if geocode.ReturnedAddresses.Count>0 then //if we got a response
begin
if geoCode.ReturnedAddresses.Count=1 then //if there's only 1, no need to show a resolver
index:=0
else //we got multiple responses, the user will have to resolve which addy is correct
begin
index:=-1; //default to failure
for ix := 0 to geoCode.ReturnedAddresses.Count-1 do //for each addy
with geoCode.ReturnedAddresses.Address[ix] do
addyResolver.lb.Items.Add(Address+' '+City+' '+State+' '+Country); //load the listbox
if addyResolver.ShowModal=mrOk then //prompt the user, if they select one, mrOk will be set
index:=addyResolver.lb.ItemIndex; //get the addy
if index<0 then exit; //if the user failed to pick, get out
end;
lblLat.Caption:=geoCode.ReturnedAddresses.Address[index].Lat; //display the lat and long values
lblLong.Caption:=geoCode.ReturnedAddresses.Address[index].Long;
end;
end;
2. And this is the actual unit that does the work. BTW, the XML parser I'm using is OpenXML, a freely available, full source implementation that works for Delphi and Kylix.
unit mapquest_api;
interface
uses classes, contnrs;
type
//base class, you must derive descendants to make use of this class
//in order to successfully communicate with mapquest, you must attach YOUR ClientID, Password and APIKey
//you can register for free at mapquest.com
TMapQuestAPI = class
private
fClientID : string;
fPassword : string;
fAPIKey : string;
fURL : string;
protected
function getXML:string; virtual; abstract;
procedure parseResponse(response:TStringList); virtual; abstract;
public
constructor Create;
function getFromMapQuest(enableLogging:boolean):boolean;
property ClientID : string read fClientID write fClientID;
property Password : string read fPassword write fPassword;
property APIKey : string read fAPIKey write fAPIKey;
property URL : string read fURL;
end;
TReturnedAddyList=class;
//this descendant will implement the GeoCoder functionality from mapquest
//you must comply with mapquests terms of service, check mapquest.com to make sure
//that works for you
TMapQuestGeoCoder = class(TMapQuestAPI)
private
fAddress : string;
fState : string;
fCity : string;
fCountry : string;
fPostalCode: string;
fCounty : string;
fReturnedAddresses : TReturnedAddyList;
protected
function getXML:string; override;
procedure parseResponse(response:TStringList); override;
procedure ClearReturnedAddys;
public
constructor Create;
destructor Done;
property Address:string read fAddress write fAddress;
property City:string read fCity write fCity;
property State:string read fState write fState;
property Country:string read fCountry write fCountry;
property PostalCode:string read fPostalCode write fPostalCode;
property County:string read fCounty write fCounty;
property ReturnedAddresses:TReturnedAddyList read fReturnedAddresses;
end;
//container class for a returned address
TReturnedAddy = class
public
Address,
City,
State,
PostalCode,
Country,
County,
Lat,
Long : string;
constructor Create(aAddress, aCity, aState, aPostalCode, aCountry, aCounty, aLat, aLong:string);
end;
//you can get back MULTIPLE addresses if you send in something that is generic enough
//in that case, the COUNT will be > 1. Make sure you implement a resolver screen to handle this
TReturnedAddyList = class(TList)
protected
property Items;
property List;
public
function getAddy(index:integer):TReturnedAddy;
property Address[Index:Integer]:TReturnedAddy read getAddy;
end;
implementation
uses sysutils, dialogs, IdHttp, IdSSLOpenSSL, IdResourceStringsCore, IdLogFile, xdom_3_2;
const
CR = #13;
LF = #10;
EOL = CR+LF;
xmlEOL = EOL;
type
// this is a descendant of TidLogFile, it will create a plain text file with
// information about the transfer session
TlogFile = class(TidLogFile)
protected
procedure LogReceivedData(const AText, AData: string); override;
procedure LogSentData(const AText, AData: string); override;
procedure LogStatus(const AText: string); override;
public
procedure LogwriteString(const AText: string); override;
class function buildLogLine(data, prefix: string) : string;
end;
// this ensures the output of error and debug logs are in the same format, regardless of source
class function TlogFile.buildLogLine(data, prefix: string) : string;
begin
data := StringReplace(data, EOL, RSLogEOL, [rfReplaceAll]);
data := StringReplace(data, CR, RSLogCR, [rfReplaceAll]);
data := StringReplace(data, LF, RSLogLF, [rfReplaceAll]);
result := FormatDateTime('yy/mm/dd hh:nn:ss', now) + ' ';
if (prefix <> '') then
result := result + prefix + ' ';
result := result + data + EOL;
end;
// ---------------------------------------------------------------------------
procedure TlogFile.LogReceivedData(const AText, AData: string);
begin
// ignore AText as it contains the date/time
LogwriteString(buildLogLine(Adata, '<<'));
end;
// ---------------------------------------------------------------------------
procedure TlogFile.LogSentData(const AText, AData: string);
begin
// ignore AText as it contains the date/time
LogwriteString(buildLogLine(Adata, '>>'));
end;
// ---------------------------------------------------------------------------
procedure TlogFile.LogStatus(const AText: string);
begin
LogwriteString(buildLogLine(AText, '**'));
end;
// ---------------------------------------------------------------------------
procedure TlogFile.LogwriteString(const AText: string);
begin
// protected --> public
inherited;
end;
// ---------------------------------------------------------------------------
{ TMapQuestAPI }
//create the class, default everything to blank
constructor TMapQuestAPI.Create;
begin
fURL:='';
fClientID:='';
fPassword:='';
fAPIKey:='';
end;
// ---------------------------------------------------------------------------
//send the http post to mapquest and return a stringlist with the results (or nil if error)
function TMapQuestAPI.getFromMapQuest(enableLogging:boolean): boolean;
const logFileName = 'http.log';
var
plainData : TStringList;
HTTP : TidHTTP;
response : TStringList;
logFile : TLogFile;
//as mentioned in my article on http post for DHL, indy converts CR/LF pairs to &.
//This causes a problem when sending xml data.
//This routine will remove CR/LF pairs and replace them with a space
procedure ConvertCRLFtoSpace;
begin
if plainData.Count > 1 then begin
// break trailing CR&LF
plainData.Text := StringReplace(Trim(plainData.Text), sLineBreak, ' ',[rfReplaceAll]);
end else begin
plainData.Text := Trim(plainData.Text);
end;
end;
begin
result:=false; //by default, we get failure response in error
plainData := TStringList.Create; //init the container for xmiting the xml
try
plainData.Text:=getXML; //get the xml from the class and put it in the container
Http:=TidHTTP.Create(nil); //create the http class
try
HTTP.readTimeout := 10000; //setup the http timeouts and xmit types
HTTP.ConnectTimeout := 10000;
HTTP.Request.Contenttype := 'text/xml';
HTTP.HTTPOptions := [];
if enableLogging then //if we are going to log the events (useful if there's a problem)
begin
logFile := TLogFile.Create(nil); //create the log file
logFile.FileName:=logFileName; //set the default log file name - NOTE: you could get fancy with this, but I normally don't use it
logFile.Active:=true; //activate the log file (creates the file and intializes everything)
HTTP.Intercept := logFile; //set the logFile to the intercept of the HTTP to capture the events
end;
response:=TStringList.Create; //create the response container
try
ConvertCRLFtoSpace; //make sure you strip cr/lf pairs out or the xml will be malformed
response.Text := HTTP.Post(URL, plainData); //post the xml and receive the response xml
result:=true;
parseResponse(response);
finally
response.Free;
end;
finally
Http.free; //free the http class
end;
finally
plainData.Free; //free the container class
end;
end;
{ TMapQuestGeoCoder }
procedure TMapQuestGeoCoder.ClearReturnedAddys;
var ix:integer;
begin
for ix := 0 to fReturnedAddresses.Count-1 do
fReturnedAddresses.Address[ix].Free;
fReturnedAddresses.Clear;
end;
constructor TMapQuestGeoCoder.Create;
begin
fURL:='http://geocode.free.mapquest.com/mq/mqserver.dll?e=5&'; //this is the free server address. if you sign up for a different type of account, you'll want to set this to your account type
fReturnedAddresses:=TReturnedAddyList.Create;
end;
destructor TMapQuestGeoCoder.Done;
begin
ClearReturnedAddys;
fReturnedAddresses.Free;
end;
function TMapQuestGeoCoder.getXML: string; //returns the xml formated per the MapQuest API
const geocodeXML =
'<?xml version="1.0" encoding="ISO-8859-1"?>'+
'<Geocode version="1">'+
'<Address>'+
'<AdminArea1>%s</AdminArea1>'+
'<AdminArea3>%s</AdminArea3>'+
'<AdminArea5>%s</AdminArea5>'+
'<PostalCode>%s</PostalCode>'+
'<Street>%s</Street>'+
'</Address>'+
'<GeocodeOptionsCollection Count="0"/>'+
'<Authentication Version="2">'+
'<Password>%s</Password>'+
'<ClientId>%s</ClientId>'+
'</Authentication>'+
'</Geocode>';
begin
result := Format(geocodeXML, [Country,State,City,PostalCode,Address,Password,ClientID]); //make the request xml for geocding an address
end;
procedure TMapQuestGeoCoder.parseResponse(response: TStringList);
var
DOC:TXMLtoDomParser;
DOM:TDOMImplementation;
XML:TDOMDocument;
LocCollection:TDomNodeList;
Geo,
Node:TDomNode;
//you can't trust that the xml contains ALL element names so this wraps the testing conditions up
//to safely get the node values or a blank when not in the xml
function SafeGetNodeValue(node:TDomNode; name:string):string;
var DomElement:TDomElement;
begin
result:='';
DomElement:=Node.GetFirstChildElement(name);
if DomElement=nil then exit;
if DomElement.ChildNodes=nil then exit;
if DomElement.ChildNodes.Length=0 then exit;
result:=DomElement.ChildNodes.Item(0).NodeValue;
end;
//same as the previous but designed to get a subelement (LatLng/Lat or LatLng/Lng)
function SafeGetSubNodeValue(node:TDomNode; rootName, subName:string):string;
var RootElement,
SubElement:TDomElement;
begin
result:='';
RootElement:=Node.GetFirstChildElement(rootName);
if RootElement=nil then exit;
SubElement:=RootElement.GetFirstChildElement(subName);
if subElement=nil then exit;
if subElement.ChildNodes=nil then exit;
if subElement.ChildNodes.Length=0 then exit;
result:=subElement.ChildNodes.Item(0).NodeValue;
end;
begin
ClearReturnedAddys; //make sure there are no saved addys
DOC:=TXMLtoDomParser.Create(nil); //create the xml parser
DOM:=TDOMImplementation.Create(nil); //create the dom
try
DOC.DOMImpl:=DOM; //assign the dom to the xml parser
XML := DOC.StringtoDom(response.Text,'mapquest.xml',nil,true); //convert the response text to and xml document. NOTE: 'mapquest.xml' forces the correct creation of the node structures. If you pass blank, you get NO NODES
try
LocCollection := XML.GetElementsByTagName('LocationCollection'); //get the location collection (group of geoaddresses 1..n)
if LocCollection=nil then exit;
Node:=LocCollection.Item(0); //get the top level node
Geo:=Node.GetFirstChildElement('GeoAddress'); //get the first GeoAddress
while Geo<>nil do //while we have a GeoAddress
begin
fReturnedAddresses.Add(TReturnedAddy.Create(SafeGetNodeValue(Geo, 'Street'), //addy
SafeGetNodeValue(Geo, 'AdminArea5'), //city
SafeGetNodeValue(Geo, 'AdminArea3'), //state
SafeGetNodeValue(Geo, 'PostalCode'), //zip
SafeGetNodeValue(Geo, 'AdminArea1'), //country
SafeGetNodeValue(Geo, 'AdminArea4'), //county
SafeGetSubNodeValue(Geo, 'LatLng', 'Lat'), //Latitude
SafeGetSubNodeValue(Geo, 'LatLng', 'Lng') //Longitude
));
Geo:=Geo.NextSibling; //get the next GeoAddress
end;
finally
XML.Free; //free the XML document
end;
finally
DOM.Free; //free the dom
DOC.Free; //free the xml parser
end;
end;
{ TReturnedAddy }
//create the object and initialize the data
constructor TReturnedAddy.Create(aAddress, aCity, aState, aPostalCode, aCountry, aCounty, aLat, aLong: string);
begin
Address:=aAddress;
City:=aCity;
State:=aState;
PostalCode:=aPostalCode;
Country:=aCountry;
County:=aCounty;
Lat:=aLat;
Long:=aLong;
end;
{ TReturnedAddyList }
//easy way to get access to the accumulated returned addresses
function TReturnedAddyList.getAddy(index: integer): TReturnedAddy;
begin
result:=TReturnedAddy(Items[index]);
end;
end.
Comments