Go Live: LifeShield, Inc.

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.

LifeShield, Inc.

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.

LifeShield Crime Stats

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:

SELECT
*
FROM
(SELECT
d.*,
FROM
[crime_table] d
WHERE
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);
) tt
ORDER BY
tt.datetime ASC
LIMIT
0, [requested_limit];

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.

  • Patrick Nelson

    “My first attempt produced results in around 3 minutes, and the final production query returns in under a second.” – Hah, that’s awesome. There are all sorts of things you can do to offload effort to the database or bring back into PHP, just depends on the context of the situation at hand, for sure (and what each application is good at). In this case, the database absolutely excels at fetching/limiting data prior to sending it over to PHP, which is definitely what you want first. In some situations, these calculations are actually about as fast (if not faster) on the PHP end, but the more data you’re working with, it’s better to offload to the database, especially if the database server works independently of the application server where PHP lives (if you have that setup).