Buying a house is a long-term investment in the sense that a family stays in a house for an average of 13 years. The quality of the neighborhood in terms of parks, shopping malls, bookstores are important factors to consider when purchasing a home. But the most important factor is taking the commute times into account which can really make a big impact on the quality of life. From our discussions with a number of real estate companies, there is quite a bit of interest in providing potential homeowners the ability to restrict homes based on the commute distance for the potential homeowner, spouse and children. Using our technology we can easily minimize the sum of the distance from each home to office location, office location spouse and nearest school.
Suppose that there is a table called homes of all the available homes, office1 and office2 are the two workplace locations and schools is a table of all schools in the country.
An unoptimized version of the query would be something like this, that orders each house in the market by the sum of the commute distance for yourself, spouse and child. And this query can be performed really fast in a matter of a few milli-seconds with little effort. This query took us less than 2 minutes to write which shows how quickly such a feature can be added. Furthermore, given that the query is written in SQL the database takes care of the bulk of optimization.
SELECT home_id, sum(d1 + d2 + s1) as total_distance AS (SELECT h.home_id, dist(h.lat, h.lon, office1_lat, office1_lon) as d1, dist(h.lat, h.lon, office2_lat, office2_lon) as d2, (SELECT dist(h.lat, h.lon, schools.lat, schools.lon) as s1 FROM schools ORDER BY s1 LIMIT 1) as s1 FROM homes h) as foo ORDER BY total_distance LIMIT 10