I joined the LifeShield team near the beginning of a site-wide overhaul to move from a static page framework to a more marketing-friendly CMS approach. The IT team had identified WordPress as their CMS of choice, and I moved into a lead role in customizing the site and integrating the existing framework. The layout had been designed by third-party firm and required extensive modification to fit into the existing business model. Additionally, there was a marketing and tracking codebase that didn’t play well with the way WordPress runs. Integrating the two was not straightforward, and involved a lot of dancing around the WordPress core. My primary goal was to have a seamless integration that did not involve a single edit to a WordPress core file, and achieved that by making extensive use of WordPress hooks and error handling.
In addition to the WordPress core, I added a number of external pages for community crime stats using the Google Maps V3 engine and a third-party crime aggregator. The first step was to format the data imports to be search-friendly, and then it was on to creating a JSON app to load new data points when the user scrolled around the map. There was a lot of query-magic to generate the returns in a web-friendly time frame.
The database contains millions of records, and we only wanted around 1000 at any given time – in a particular radius around a particular point for a particular time frame. The solution was to write a query that narrowed itself: first by calculating a box, then by calculating the radius, and finally narrowing by date. My first attempt produced results in around 3 minutes, and the final production query returns in under a second.
For the curious:
d.latitude > [latitude_minimum]
AND d.latitude < [latitude_maximum] AND d.longitude > [longitude_minimum]
AND d.longitude < [longitude_maximum]
AND ROUND(SQRT(POWER(69.1 * (d.latitude - [requested_latitude]), 2) + POWER(69.1 * ([requested_longitude] - d.longitude) * COS(d.latitude / 57.3), 2)), 0) AND timestamp(d.datetime) = DATE_SUB(now(), INTERVAL [requested_time_start] DAY);
The latitude and longitude mins and maxes are calculated on the fly by adding or subtracting the following from each: (distance_in_miles + (distance_in_miles * .25)) / 69.