Mike 1.10.2012

  • Finished the GeoEncoder, it’s available in our SVN at /trunk/Java_folders/Projects/GeoEncoder
    • It works pretty well but will likely need tweaks and will become more developed as it sees use
    • It should work on any MySQL table that contains addresses, it expects 3 columns: street address, city, and state but should work fine if all three are lumped in to one column
    • the minimum requirement for an address is the state, geoencoding ‘MD’ would just give a lat/lng of the center of Maryland
    • Each lookup takes about 1/3 a second on my wireless connection here
  • Working on making a few more charts in AppBoard
    • One chart which displays donations by state over time, grouping states the following way: RI, NY+NJ+MA, Rest
    • A query that returns donations by year for ‘Rest’: SELECT substr(GFDate, length(GFDate)-3, 4) as year, sum(GFAmt) FROM `test`.`donors` WHERE AddrState <> ‘MA’ AND AddrState <> ‘NJ’ AND AddrState <> ‘NY’ AND AddrState <> ‘RI’ GROUP BY year;
    • Another chart that breaks down the donors by Top 3 givers, Top 100, Rest each year.
    • A query that gives the top 3 donors of 2011 SELECT substr(GFDate, length(GFDate)-3, 4) as year, sum(GFAmt) as sumGift, ImportID, FullName, AddrStreet FROM `test`.`donors`
      WHERE GFDate LIKE ‘%2011’ GROUP BY ImportID ORDER BY sumGift DESC LIMIT 3;