Govt can now legally Shoot Down Drones

I recently read that Congress has now passed a bill that allows the US govt to shoot down drones that are in the wrong area or doing the wrong thing.

I’m not sure how I feel about this. I still think of drones as a curiosity.. not necessarily the first thing I am going to go out and pursue. For example, I would much rather start hoarding Geographical data from sources like Tiger and integrate with other datasets.

I’ve worked with Geographical data on many occasions. Sql Server 2005 made it a LOT easier to do these types of calculations.

In 2006 I was working for Starbucks and I was brought in to work on their Sql Server database infrastructure. It was an amazing project. The girls were incredible and they all wanted to teach me how to make an espresso.

I swear I was drinking about twenty shots a day that month.

At Starbucks we had one main table that stored latitude and longitude information. There were about forty thousand geographic locations. Perhaps fifteen thousand were physical stores and the rest were either in the planning stages or something along those lines.

Basically, Starbucks operations where I was working they had no way to correlate that the Redmond stores were all supplied from the Bellevue Warehouse. They didn’t really believe in warehouses, every store was basically a mini warehouse where the products would ebb and flow.

It just blows my mind that any company would ever have fifteen thousand locations and not have a real physical database infrastructure that correlated the Redmond Stores together with the Bellevue stores into a region or an area.

We basically had been tasked with triangulating which stores were the closest to each other and which stores were not in the vincinity. There was only one major problem with the algorithm. When you have forty thousand locations trying to calculate against the same forty thousand locations.. the number of calculations I think it is 160 billion operations? I’m sure I am exaggerating a little bit.. but I’m positive that it was more than a billion.

Every time that an employee would open their dashboard this important query took at least sixty seconds to complete. I pointed it out to them and laughed. I said

Your math is right, your database is performing extremely well for what you are asking of it.

But that didn’t change the sixty seconds that it took every employee in the department on their startup page. I swear it was about five minutes of coding before I had a solution. I called this solution the ‘Magic Circle’. There was some business rule that was not being accounted for properly. It was some sort of rule that said any stores more than 300 miles apart from each other need not be considered as their nearest store.

So when I got there the query looked like this (don’t worry this is not REALLY a user defined function)

Select distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude)

From storelocations s1

Inner join storelocations s2

On distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude) < 300

The query worked it did what they wanted.. but it was at least a hundred times slower than it needed to be. Again this whole month involved five minutes of coding and then the rest of the month was simple arguing that the magic circle concept was fundamentally solid.

So what was the magic circle? Looking back it was really a square of two points latitude and longitude deviation was the approximation for 300 miles distance.

So without further ado… Here was my proposal

Select distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude)

From storelocations s1

Inner join storelocations s2

On s1.latitude between s2.latitude-2 and s2.latitude+2

And s1.longitude between s2.longitude-2 and s2.longitude+2

distancecalc(s1.latitude, s1.longitude, s2.latitude, s2.longitude) < 300

This basically stopped the calculation from doing a billion operations every minute and it turned the execution time to under one second. Five minutes of coding and one month of arguing with a dozen people about math. Good times.

Today Sql Server 2005 or 2008 makes the calculations simpler for sure. This project was literally the only time I had to use geometry in my career as a programmer.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.