SELECT * FROM MLS WHERE Remarks = ‘Whoa’

I thought I’d take a moment to reflect on how Rain City’s favorite MLS Search is implemented. I’m a little tired of thinking in computer languages (mostly T-SQL, C# and Javascript), so I figured I’d blog a bit in Geek English for a little while before I hit the compiler again.


I’m always interesed in how web sites & computer software works under the covers, so I thought I share some of the more interesting points about how I’ve implemented “Zearch” to date for the “geekier” folks in the blogosphere.

It all began way back in the fall of 2005 shortly after I got my first MLS feed. At the time, Microsoft’s 2.0 platform was still in beta. However, after learning what Microsoft’s next generation web development tools were going to do (and seeing what Google Maps and Microsoft’s Virtual Earth teams were doing), I saw a great unrealized potential in MLS search tools and decided to do something about it.

Anyway, it’s all built on top of 2.0 and MS SQL Server 2000 (yeah, I know I’m old school). One of the first things I did is combined all the property types into a VIEW and create a dynamic SQL query when you search for properties. Some search tools only let you search for residential properties or condominums at one time (which I thought was lame). I orginally tried to implement stuff doing a bunch of UNIONs, but keeping track of the schema variations for the different property types eventually drove me nuts, and I encapsulate all that crud into a VIEW.

I also find it a little ironic, that I’m not the only one who found the MLS schema differences a PITA to deal with. I’m glad the various MLS software vendors and the CRT are working toward a common industry schema (aka RETS), so us application developers can focus on the real problem (developing compelling & useful software), instead of remembering that the ld column in one table, is really the list_date column in another table.

Another interesting thing I do on the back end is that I geocode every listing after I do data download. The main reason is that I don’t trust the MLS data and their bogus geo-coding would make my app look bad. I also knew when I started, I’d eventually do maps, so as soon as a new listing hits my database, it’s gets more accurately/correctly geo-coded. In case your wondering if I’m screen scraping w/ Perl or something else, it’s all done with T-SQL stored procdures. (Well, technically it’s a proc that calls the MSXML2.ServerXMLHTTP COM object, to issue an HTTP request against a geocoding web service, and then uses OPENXML on the response’s XML to get the latitude & longitude).

As you might have guessed, there are also stored procedures and functions to get the distances between two points, doing a radius search, and other stuff of that ilk. Fortunately, all that stuff can easily be found using your favorite search engine, so you don’t need to know how all the math in the law of cosines works (you just need to know of it).

Well that’s it for the back end. Next time I’ll talk about the front end put on my Web Developer hat.

Did you know:

How cool is our home search? Ice Cold!

In case you haven’t dropped by our home search tool recently, we’ve made some improvementsicecube. Changes include…

Market Analysis Tool Improvements
We thought it would be helpful, if you could get a second opinion when you get an estimate. So, we’ve made arrangements with Zillow to use their Zestimate web services on our Market Analysis page. That way, when you type in a property address, we’ll give you our estimate, get your property’s Zestimate (and the link to it’s page on Zillow), and save you some typing.

Radius Search
Want to find the all houses, within 2 miles of your house or office? Now you can here! And yes, the search results pages are Bookmark-able, RSS-able, and Google Earth-able. (I wouldn’t have it any other way).

Improved Location Search
The list boxes on the location search page are multi-selectable. Big whoop, I hear you say? Well, ours doesn’t refresh the entire page when you change the city or download a big city / community list when you first navigate to the page. Yes, you are seeing AJAX in action. It’s not something most people are going notice, until they wonder “Gee how come your page is so much faster than all the other ones”?

As always, the results from the improved location search are Bookmark-able, RSS-able, and Google Earth-able.

What’s next
Well, it’s a given that at some point I’m going have to have Virtual Earth or Google Maps integration, instead of static Yahoo Maps. If I’m going to compete with the big boys of real estate search, I gotta do maps. I’m probably going to have to create profiles, so you can save your searches, favorite properties, favorite places and other stuff that requires server side persistence.

What features would consumers and realtors like to see next? I’m more interested in hearing what realtors would like to see next because they are the ones who’ll be writing the check, when I eventually decide to release this. I have a billion ideas for what I’m going to do, but I’d get to some more feedback to find out what features I should implement next. Otherwise, I’ll continue to make it up as I go along…