Wednesday, November 9, 2016

Why I like good old fashioned SQL

Every election year I work on the election portal at https://pacific.nwportal.info/. This year, instead of storing the election data that I retrieve from state and county sources in cache as JSON, I stored it in a SQLITE3 database. This added tremendous flexibility in dealing with election data.

One of the new features of the election portal is the Balance of Power chart that shows which party, given current real-time election results, will be in control of the Washington State Senate and the Washington State House. This is where SQL comes in and helps eliminate the need for a bunch of code. For example, this is the SQL I created to determine how many candidates from each party are ahead in the election for the WA State House:
Select Party, count(Party) Winning, MaxVotes from (select max(TotalVotes) MaxVotes, Party, Candidate, Race from (select rc.Name Race, c.Name Candidate, c.Party, sum(rs.votes) TotalVotes from races rc, candidates c, results rs, feeds f where f.ID = rc.FeedID and f.FeedType = "WAXML" and rs.CandidateID = c.ID and c.RaceID = rc.ID and rc.Name like "%State Rep%" group by c.Name) group by Race) group by Party;
This statement returns a record set that looks like this:
Party              Winning       MaxVotes
Democratic           52            21822
Republican           46            22407 
With this, it is easy to determine in code which party will control the house. I only use the "MaxVotes" value to know when the election reporting has started. If the value is 0, I know that the reporting has not begun yet and can set up my programming logic accordingly.

Tuesday, November 8, 2016

Is your Drupal 8 site crashing on PHP 7?

I ran into this problem on a Drupal 8 site running on bleeding edge technology. The site would not come up and it would display an error coming from Symfony "Request.php".
It turns out that PHP 7 has some cool performance features including a feature where PHP will not create the standard global variables (like $_SERVER) if it doesn't see any references to them in the PHP code. The problem was that there were references to the global variables in Symfony, but there were no references within "index.php", so Symfony would crash the drupal site when it tried to access the $_SERVER global variable.
A quick fix was to add this line to index.php: "$server = $_SERVER;" and the site was up and running.

Wednesday, June 1, 2016

Determining the Age of Bing Maps Imagery

Context

At work, we use Bing Maps to provide users a way of visualizing their home and aiding them to select where their roof is for the purposes of calculating roof dimensions for the potential installation of solar panels. We don't use the image itself for this calculation; we use the location. The map just provides a visual aid so that the user can say "this is where the roof of my house is."
Why do we use Bing Maps instead of Google Maps? In a word - price.

The Problem

Bing provides different imagery sets with the main ones being "Aerial" and "Birdseye" where "Birdseye" has the advantage of providing different "oblique-angle" images for each area. The problem is that not all Bing Maps images are created equal. In some areas of the US, the Birdseye image can be much older than the Aerial image, or vice versa. Depending on where a customer's address is, one image set could be much older than the other. This can result in the user looking at a map of trees or a desert with their house nowhere in sight. The user would have to guess which cactus is closest to where they think their roof-line might be.

Bing to the Rescue (or not)

Bing provides an API where you can retrieve the age of the imagery for a given imagery set. You can do this by using the API to retrieve the Imagery Metadata. The idea would then be to get the metadata of both the Birdseye and Aerial imagery and use the newest one to determine which map to display to the user. However, there is another problem. Although Bing provides this nice API to retrieve the metadata which includes the imagery age by way of two properties: "vintageStart" and "vintageEnd", these properties are not returned for the Birdseye imagery. So, the imagery metadata is completely useless for trying to programmatically determine which imagery set is newer. Thanks, Microsoft!

The Solution

Well, we didn't find a solution, other than to add a configuration setting to determine which imagery set to use. However, a colleague of mine did suggest a solution. The only problem now is to somehow implement this flowchart using C#.
Map Age Guide
Map Age Guide