West by West North

North by Northwest

Listing alerts is a Redfin feature that lets you keep track of new listings in your area. You start by doing a search on our map page, and then you click on the “Save this Search” link:

Save this Search

This lets you assign a name to your search and save it. You can also sign up to get a daily email with new listings that match your criteria.

We recently released an upgrade to the Redfin website. There were a few minor tweaks to the listing alerts code, but nothing significant. Still, a few days later we got an email from a customer saying (to paraphrase): I used to get an email alert almost every day, but recently they stopped, what’s up?

Being the owner of the Listing Alerts feature, my heart skipped a couple of beats. Then I jumped in to analyze the problem. What I noticed was that indeed this customer (Norm) had a steady stream of emails that mysteriously ended. However, the emails didn’t stop on the day of the release; they stopped several days later. I also noticed that Norm made changes to his saved searches right around the time the emails stopped. So, my hypothesis was that Norm’s new searches were somehow more restrictive and we simply didn’t have results for them. To verify this, I did a test run using two of Norm’s searches, an old search and a new search, for the last 24 hours of data. I found that the old search returned results and the new one didn’t, which seemed to confirm my suspicion. So I went ahead and replied to Norm saying: please relax your criteria and try again. Luckily for me, Norm is a stubborn guy. He replied a few minutes later saying: actually, I think my new search is more broad than the previous one.

Now I was really worried. I took another look and I found the smoking gun: for all saved searches created since our recent release, the search area parameter was not saved correctly. To explain what was wrong about it, I need to give some background on how we store your saved search…

Our saved_searches table was changed a little in the recent release. Prior to the release, it looked like this:

[saved_search_id] [name] [min_latitude] [max_latitude] [min_longitude] [max_longitude] [other criteria...]

After the release, it looked like this:

[saved_search_id] [name] [polygon] [other criteria...]

Why did we make this change? Well, this release was also Redfin’s “toe dip” towards changing our database platform from MySQL to Postgres. We found that Postgres gives us better performance on certain kinds of searches (e.g. past-sales searches). So, in this release we replicated our past sales data from MySQL to Postgres and changed the website to issue all past-sales queries to Postgres. Now, to get the performance benefits from Postgres, you have to store your regions in a column of type GEOMETRY, as opposed to simple columns of type DOUBLE or whatnot. So, we wrote the code to store and fetch GEOMETRY types. And once we had it, we figured we may as well use it for saved searches as well — the search area is just a POLYGON after all.

So where’s the problem? The problem is that while in MySQL coordinates are typically stored as (latitude, longitude), Postgres prefers to store them as (longitude, latitude). Or, to be more specific, Postgres doesn’t particularly care which order you use, but the Java library we used (Vivid Solutions) opts for the (longitude, latitude) style. Why does it do that? Probably because it thinks about these coordinates as points in X-Y space, where X is like the longitude (east/west = right/left), and Y is the latitude (north/south = up/down).

We actually noticed this discrepancy when we were close to our code-complete milestone, and we had to make a decision about it. One option was to standardize — to make all coordinates in both databases follow the same (longitude, latitude) style, the style we will eventually adopt when we move to Postgres 100%. Another option was to keep keep MySQL coordinates (latitude, longitude) and to only use (longitude, latitude) in Postgres. We chose the latter approach because it was less disruptive and we were running out of time. Only we didn’t get it quite right — we missed the code used for the saved searches table. The effect was that all existing saved searches kept their (latitude, longitude) format and continued to work, but all new searches were saved as (longitude, latitude) and were busted.

How did we miss this in our testing? I will be the first to admit that we don’t do as much testing at Redfin as we could or should. However, we did have about 40 unit tests and 70 integration tests, and they definitely included scenarios that checked listings that are in/out of the search area. The trouble was that our test cases were too “symmetrical”. This is best explained by an illustration:

Symmetric and Asymmetric Test Cases

Well, when we discovered this problem, we spent the first few minutes being horribly embarrassed for letting it slip through. Then we buckled down, found a solution, and released it later that day. We also added a bunch more test cases to catch the problem in the future. And we sent an email to all the affected users with our apologies for the missed emails. At this point we believe listing alerts work again.

So, what are the lessons?

Strive for consistency in your data, even if it’s more painful and/or takes more time.

Avoid dual-DB situations. Both MySQL and Postgres support the SQL standard, but there are enough differences between them to make life difficult, and this lat/long issue is just one example. Now, we chose to take a toe-dip, and if we had to do it over again I think I would have made the same decision. In other words, do as I say, not as I do. :)

Pick asymmetric test cases. Discussed above.

Add constraints. Our database didn’t complain when the coordinate (47.5, -122), which is in Seattle, was rewritten as (-122, 47). Take a moment to think about that. A longitude of 47 is probably somewhere around India. A latitude of -122 doesn’t exist: it’s beyond the south pole! Why didn’t the database complain? Because as far as the database is concerned, this is just a point, and it’s a perfectly valid point. If you can afford the performance hit, it helps to add constraints to tell the database: in *my* application, a Y value of -122 is no good.

Don’t repeat yourself. The search area for a saved search was actually stored in two formats for two purposes. The first purpose was for evaluating the saved searches for the purpose of sending emails, as discussed above. The second purpose for interactive use – where you can simply pull up your saved search on the website and click “run search”. In the second case, we chose not to use the bounding box because your view may be a little different each time you pull it up, depending on the size of your screen or whether the window is maximized. For interactive use, it’s better to use the center point of your search and a zoom level – this combination can adjust more gracefully to different situations. The problem in our case was that we separately stored the latitude and longitude for interactive use. Had we simply derived them from the polygon’s center, we would have caught this error earlier.

Image credits: http://www.slashfilm.com/2007/02/20/movies-that-should-have-won-an-academy-award-but-didnt/


  • Bryce Schober

    I was hoping that the implied feature of polygon-mapped zip code searches was implemented, but it doesn’t seem to be. I still get results from other zip codes in a zip code search. This has been a long-standing complaint of mine. Any idea when this will be fixed?

  • http://blog.mattgoyer.com Matt Goyer

    We’ll fix this in early 2008. Thanks for your patience!