Convert Region 25 (MIBOR / Indy) to new updater

[Battler45]

George,

The first project on which you'll be working will be to convert an existing MLS data feed to the new updater system.

Vihar will be able to provide you with the technical details for installing GitHub and downloading a local copy of the current code base.

Vihar, will you also please provide George with whatever server and / or database access he needs for this initial project? Please share with him any necessary logins and create any new users as necessary. Also, I think that converting region 25 will be a good place to start because it shares the same MLS data provider (Marketlinx) as the one you have already converted (SoCal MLS). You can find my existing code in:

C:\Inetpub\mls4.sierrainteractivemls.com\admin\mibor

on DEDQ61

The login credentials to the RETS server are:

• UserID: 31041rets

• Password: BENJ1001

• UserAgent: sierrainteract

• Password: h1ghplateau

RETS URL: http://inr.rets.interealty.com/Login.asmx/Login

As you'll see when you examine my existing code, I was using the sysid name for the database fields rather than the long names for the fields (which were being used for SoCal MLS). I did this because I had a lot of difficulty with the RETS Connector software that I was using to download the daily data files. To help translate between the two, I've attached a spreadsheet provided to me by MIBOR (which stands for Metropolitan Indianapolis Board of Realtors) that cross-references the long field names with their numeric IDs.

Please let me know if you have any questions.

Thanks.

Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

Forgot to add - you can check the database schema by remoting to the following machine:

IP: 75.103.119.195

User Name: vshah

Password: ViharShah$123

Pls note that this is development DB, and, has almost identical schema to the live DB. There are total five DBs on this server: sierrainteractive-1 to 4, and sierrainteractive-updater. Pls note that currently sierrainteractive-3 is being used by the live sites so pls make sure that you don't change anything in that database. Ideally, you would only be needing to refer to the sierrainteractive-4 and sierrainteractive-updater DBs.

Pls let me know in case of any issues.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben, Vihar,

I completed test of MIBOR updater on development server. Update process was executed without errors. I don't understand how to test results using of comparison of live (www.hometoindy.com) and development (hometoindy.sierrainteractivedev.com) sites. I see that some record is added into database - I can find this record by its MLS number in database sierrainteractive-1. But when I'm trying to find this records on sites (with using of search by MLS number) I could not get any results. Does development site use database sierrainteractive-1 on sql4.sierrainteractivemls.com indeed? Anyway I'm not sure that visual checking will give an opportunity to find out all issues (if these exist, I hope new updater is working without issues). Perhaps it will be more useful to make a small application intended to compare databases?

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

Yes, the specified RETS servers are live. I think it should be ok to connect to those servers for testing purpose.

I understand that you want to test the new updater system - the SoCal updater system to be specific. To do so, you can run the WindowsServiceTest console application. The UpdateProcessor.cs file in this project contains the entry-point function, PerformUpdatesForRegion. This function will execute appropriate updater steps (depending on the steps supported for a region - as defined in the Updater_MLSRegionUpdateSteps table). If you want to test a specific step, pls comment out appropriate code in the PerformUpdatesForRegion function.

Also, you would need to change certain configuration parameters defined in the app.config file. Some of these parameters are:

• Connection-Strings: updaterConnString & connString1 - pls set these to point to your local db server.
• PhotoRootFolder: this is the path where photos would be downloaded from the MLS server
• LogEmails: true / false. If set to true, alert-emails would not be sent to actual email addresses; they would instead be logged in the log file
• TestEmailAddress: if the LogEmails parameter is set to true and this parameter's value is set then emails would be sent to this test email address as well.

Pls let me know if you need additional info.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

In implementing new updater system for region-25, you might want to refer to the existing updater system implementation (especially for field mappings). I've attached a zip file containing the source-code for the same. It contains the following folders:

mibor - contains code specific to updater-system for region 25. The "mls_autoupdates.asp" file contains the entry-point; that is, the programming in this file will initiate the updates for region-25. It will call appropriate functions like UpdateListings, RemoveOldListings, UpdatePhotos etc... for performing various update steps. Some of these functions could be defined in the mls_functions.asp file present under this folder. And, rest of the functions would be defined in the common code.

res/includes - contains code common to all updater-systems. The mls_functions.asp file for example defines InsertUpdateMLSListing function, which is called to add/update listing records.

---

I'm not sure if you had a chance to work with libRETS library. If you haven't, I would recommend first creating a sample project that uses the libRETS library. Pls use the above RETS credentials for connecting to the MIBOR RETS server. Ideally, you should first download the metadata from this server. That would help you in mapping listing fields.

Pls note that the MIBOR region data is stored in the sierrainteractive-1 database. You can copy the latest back up of the database (development) from the following FTP location:

Host: 75.103.116.57 User-name: george_dev_db Password: GeorgeM$123

Since the development DB is has restricted access, pls restore the above back-up on your local DB and configure the updater code to work with your local DB.

Pls let me know if you have any queries.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben,

I've updated dev. version of search-tools to use PhotoServer value in forming photo-url. I've also updated & verified a couple pages of live sites on DEDN173 for the same. I'll complete the remaining changes tomorrow. Several widget pages (\MLS_Tools_Integrated\widgets) have been updated but I'm not sure which pages embed those widgets. Could you pls let me know how I can check the following widgets:

• carousel-listings.asp
• listings.asp
• slideshow-1.asp
• slideshow-2.asp
• slideshow-4.asp
• slideshow-5.asp
• slideshow-6.asp

Moreover, several xml feeds have also been updated. Is there any specific way to check the following feeds?

• google_base_xml_feed.asp
• site_map_xml.asp
• luxuryrealestate.asp
• newpanda.asp
• trulia.asp
• zillow.asp

Pls let me know your inputs.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

RE: #63 - Your alternate approach does sound good and workable. I guess that appending a querystring parameter to the photo filename does not impact the ability of the browser to render the images? (I've tested, and it does not seem to be a problem.) In that case, this does seem like the best and easiest solution.

I have not yet checked when and how the DateAdded field in the MLSPhotos table is updated, but I will do so tomorrow, and will make sure that the old updater is set to update this field appropriately. Can you check the new updater to make sure it is also updating this value appropriately?

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Ben,

Forgot to add one point - I haven't been able to verify the recent changes on the dev. version of weirproperties.com. For some reason, it redirects me to the live site. I commented out the corresponding Rewrite rules in the .htaccess file but it didn't have any effect. Pls let me know if I'm missing something.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

I've merged changes to the master branch and have pushed the same. Pls note that I had created a persistence method, GetAllSourceUniqueIds for SoCal updater. A similar method, GetAllSourceUniqueID was being used in the MIBOR / GLAR updaters. I've removed the GetAllSourceUniqueID method and have changed references appropriately.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar, George,

RE: #54

1 - Yes, please do not use Driving Longitude / Driving Latitude values from the MIBOR feed. Instead, use our Geocoding scripts which will geocode listings directly based on property address.

2 - For ListType 1 (Residential) the field is 511: Res/Cnd for ListTypeDescrip. For ListType 4 (Land) the field is 343: Type for ListTypeDescrip. For ListType 5 (Multi-Faimily), the field is 135: List Type for ListTypeDescrip.

3 - Vihar, I believe you are correct and that our access is restricted to only be able to retrieve Active / Pending listings. However, it would still be nice to be able to track listing status if we can using the Status field in the MLS table. (In our front-end application, we may at some point want to display status to end-users.)

4 - Regarding usage of

, that was an attempt on my part to improve the way the listings are displayed on the front end. However, it was a failed experiment, and so it's fine to format the Features* values in the same way that they are formatted in the SoCal MLS data feed. In fact, it would be preferable to keep it the same as the SoCal format for consistency's sake.

5 - I'm not sure about this. In some cases, an agent my replace existing photos with a new set of photos, but the overall number of photos may not have changed. In such cases, it would definitely be necessary to delete all existing photos and re-download the full set to insure that all new photos are properly received.

Please let me know if you have any questions

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

To further clarify, the existing updater for mibor code will probably be confusing to you. It references the sys IDs of each database field, rather than the long field names, which is what you will be using when you query the RETS server using libRETS (I believe). You can use the T5 Field Info RETS Updated spreadsheet attached to the first comment in this thread to help you see which sys IDs match to which long field names.

In bringing any new data feed in line with the new updater system, the most difficult part will always be to determine how the fields from the host RETS database should be mapped into the fields of our own database.

In certain cases, such as when dealing with City, State, and Zip Code, these are straightforward.

However, in dealing with other types of listing data, such as interior and exterior features, lot features, etc., this can be more complicated. So, in working this out, referring to the existing SoCal MLS updater mappings (region 10) may also be quite helpful for you, because that region will (I believe) contain many of the same field names and will share many similar mappings.

Please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

My apologies for the delay in responding you. I've verified the changes for ListType and SourceUniqueID; they are working fine.

Can you pls let me know if you have begun working on the photo-migration application?

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Hi Vihar, Could you please explain more detailed how I can test the application? As I understand the specified RETS servers are live not "sandbox"? Is it ok to connect to servers for testing purposes? Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Ben,

Thanks. The carousel on RealEstate3000.com is working fine so we are fine. That completes the PhotoServer specific changes on the front-end sites. I'll push changes to the live CAA later today.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben,

I've modified the connection-string on the dev. version of search-tools to connect to dev. DB. The dev. hometoindy site now pulls data from the dev. DB.

Pls note that the contents are still pulled from the live DB as Vihang is working on setting up content-pages for new sites. He is however not able to work with property-search pages. Could you pls let me know if synchronizing the Sites table will fix such issues? Or, it would be good idea to restore the latest sierrainteractive-1 live DB on the dev. DB server.

As for data comparison - the live and dev. DB are accessible from the DEDN173 server so it can be used to perform side-by-side comparisons. I've set up a new user on this machine. When George has completed local testing and is ready to test the new updater on the dev. DB, I'll provide him the details for the same (and discuss possible approach to test the new updater). Pls let me know if this sounds good.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

Yes, I'm sure that SchulerBauer.com is being served from DEDN173. To verify, I just remoted in to that server, opened the IIS Manager, and stopped the SchulerBauer.com (LIVE) site. When I did that and then checked the live URL of the site, it no longer came up, so I'm positive that the site is being served from that location.

I also verified that the virtual directories for the site (/property-search/, /server_constants/, /shared/) are pointing to the write folders on the server, and they do appear to be.

In making your updates, are you making changes within the Inetpub-LIVE/Sites/MLS_Tools_Integrated/ folder? That is where all three of those sites are pulling their MLS functions from.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Vihar,

I see your changes are committed into production branch. Do you plan do commit these into master too? (I'm working with master branch, is it correct?)

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

Just checking in to see how things are coming -- were my last comments helpful, and have you been able to make further progress?

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

I tested the latest changes. The ListTypeDescrip value is correctly populated now but the ListType value doesn't reflect the type of listing. It should be 1 for residential, 4 for land / lots and 5 for multi-family. Currently, it set to 1 for all types of listings. Pls make necessary change.

Also, I think we may need to change the way SourceUniqueID field's value is set. Currently the ListingKey (150) value is assigned to this field. I think this value may not be unique in the MIBOR RETS server. Can you pls look into assigning "sysid" value to the SoruceUniqueID field? The SourceUniqueID field should store the unique value, which can be used to identify record in the MIBOR database (kind of primary-key). This value is used mainly in checking physically removed listings (In the PurgeListing step (18)).

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George & Vihar,

I have spoken with the staff at MIBOR and they are considering granting us access to the requested resources (History, Deleted) but they have not done so yet, and they have not yet committed to do so.

So, for the time being, I think we need to develop an alternative way to remove expired listings from our database.

The only way I can see to do this would be to pull a complete list of all active listings each day -- either by sysID, or MLS #, or both -- and use that list to compare against the listings database and identify the listings which need to be removed.

I'm not sure how timeconsuming it will be to pull that complete list of all listings each day, but hopefully since you are just pulling a single piece of data, it should not be too bad.

Please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Ben,

I believe George hasn't yet run the MIBOR updater on the dev DB so it should be fine. I'll confirm with George and restore the back up based on his response.

George - can you pls confirm if it would be alright to go ahead with the restore of sierrainteractive-1 DB?

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

I've set you up so that you should be receiving updates on this task / thread -- please confirm. This thread contains the initial details you will need to get started on your first task, and Vihar can provide you with the background, technical details, etc. that you will need to get started.

Please let me know if you have any questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Vihar,

Yes, there is some sort of bug / problem with ISAPI Rewrite that changes to an .htaccess file do not always take effect once applied. In such cases, it is necessary to restart IIS services on the server in order to get the changes to take effect. I've gone ahead and done that on DEDN173.

The Weir Properties dev site (and all other sites pointing to development DBs) are no longer working due to changes Crystaltech had to make to usernames / passwords. In completing their work, they said, "I've uninstalled SQL from the OS volume and then reinstalled it to your DATA volume. Please note that you will need to update the passwords for the 'n3wt0n' and 's13rra' users as we did not have a PW reference for them, so I used the password listed for your primary login user that we have on file in the control center." I believe the password they are referring to is the same one we use to RDP into the server, but it seems like these should be reset to what they were previously for consistency's sake.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Ben, Vihar,

I've also tested MIBOR updater one more time (locally) and I found that all is fine.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar, What do you mean by MIBOR meta-data? I'm using T5_field_info_RETS__Updated01-17-09.xls document attached in first post. Or you want to see how I perform this mapping (so I can give my code of "Parse.." function). Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar, Thanks for your help. I will check this listing. Regarding composite fields for Residental Property. Now I'm able to fill next composite fields with same headers as for SoCal:

• FeaturesEnergy
• FeaturesInterior (only data for "Eating Areas" and "Interior")
• FeaturesLot (only data for "Lot Description" and "Lot Size Dimension Description")
• FeaturesFinancial
• FeaturesFoundation

For other composite fields (FeaturesCommunity, FeaturesCondo, FeaturesConstruction, FeaturesDocuments, FeaturesExterior, FeaturesFencing, FeaturesHOA, FeaturesParking, FeaturesRoof, FeaturesUtilities) I can not find MIBOR fields which could be assigned to SoCal headers. Probably some composite fields can be filled with MIBOR data with other headers (for example MIBOR contains "Garage description" field which can be placed into composite field FeaturesParking). But I'm not sure is it legal or not and who should make such decisions. Posted by Molchanov George(unfuddle username: george)

[Battler45]

Ben,

I've checked MIBOR metadata and I don't see there "Past Solds" class. We have access to the next classes:

Residential Lease (not imported)

ResidentialProperty (imported as Residential)

Commercial Retail (not imported)

LotsAndLand (imported as LotsAndLand)

Commercial/Industrial (not imported)

Commercial Office (not imported)

Commercial Multi-Family (not imported)

Farms & Agricultural Land (not imported)

MultiFamily (imported as MultiFamily)

Commercial Land (not imported)

CommonInterest (not imported)

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

Could you please give me git reference to the storage with "schema changes.sql" and "stored procedures.sql"? My change are for database with MLSPhoto table but I have now only updater system's git reference.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

Unfortunately I can not remote into 208.106.250.174 to update database with my scripts - maximum number of allowed connection is exceeded. Also I have again question about git - I've run git fetch command but I don't see any changes (there should be changed ImageHelper.cs and Constants.cs). What I have to do more?

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

For the purpose of testing, I think it would be good to set the LastUpdateTimeStamp value to previous date i.e. 30th Aug. This should download sufficient number of records, which you can then compare by reviewing live and development database side-by-side. In addition, as Ben had mentioned earlier, you can also compare the live (www.hometoindy.com) and development (hometoindy.sierrainteractivedev.com) sites to make sure the correctness of data.

As for testing the MIBOR updater on the dev server, my thought is to do this by executing command-line test application. This will allow you to test the MIBOR region updater in isolation.

The development server (75.103.119.144) has a Test App folder (D:\Updater System\Test App). Pls FTP the latest command-line executable and other associated DLLs to this folder. The Sierra.Updater.Tester.exe.config file already contains server specific settings so you won't need to change it unless you want to test the email alert functionality. In that case, pls change the TestEmailAddress param with appropriate value.

Important points:

1 -- If you need to update the config file, pls make sure that LogEmails is set to true (otherwise emails would be sent to actual leads)

2 -- The connection-strings are updated to point to the dev. DB (sql4.sierrainteractivemls.com) so you can remove the SoCal specific updater steps from the Updater_MLSRegionUpdateSteps table in dev DB; the SoCal MLSRegion_ID value is 10. This will make sure that only MIBOR records are downloaded / updated.

3 -- Pls don't FTP the following two files: librets-dotnet.dll and librets-pinvoke.dll; they are of 64-bit version. Uploading the build version (which is 32-bit version) will result in error.

I've set up a new user, georgem (#G30rg3#) on the development server (75.103.119.144). Pls remote into the development server using these credentials. Also, you can use the following FTP settings to FTP console app to the Test App folder directly: 75.103.116.57 sierraupdaterdev #Med2a$123#

--

As for side-by-side database comparison, you can remote into the following server: 208.106.250.174 using the same credentials i.e. georgem (#G30rg3#). I've already opened an SQL Server session, and have connected to the live DB (sql2.sierrainteractivemls.com) and development DB (sql4.sierrainteractivemls.com).

Just in case you need to connect to either of these DBs, the username and password are s13rra and 1nteractiv3 respectively.

Pls let me know if you have any queries / need additional info.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Attachment: Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

I've fixed issues with ListType and SourceUniqueID.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

1 - Yes, it's fine to geocode listings using our own functionality -- that's what I am also doing in the current updater.

2 - Yes, I agree, I believe that is the case. I will check tomorrow with MIBOR staff to confirm, but for now, please proceed as if that data will not be available.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

You are correct. The dev. site also connects to the live database. That is the reason it didn't display any result for MLS search. Ben and Vihang are working on adding new front-end sites, and, therefore, they need to connect the dev. sites (on sierrainteractive-1) to live DB so we would not be able to perform visual comparisons.

Btw, I quickly checked the latest MLS records on the dev DB. Pls find below my comments:

1 -- It looks like the newly added listings are not geocoded. That is, the Geocode_ID value for such listings is 0. To give you a brief overview of geo-coding - we have a table, Geocodes, which stores details like latitude, longitude, address etc... This info. is used in displaying listing on map, performing search by map area etc... So when a new listing is added to the MLS table, we also associate it with a new Geocodes record. If latitude / longitude info. is available from the RETS server, then those values are used in creating a new Geocodes records.

The updater also runs Geocoding step. This step queries 3rd-party service like Yahoo / Google to get missing geocoding details. And, associates listings with new Geocodes records. So for all those listings, which don't have latitude / longitude info. available, the geocoding step populates missing details for them.

Can you pls verify this in the MIBOR updater?

2 -- None of the listings have open-house details. Can you pls check if the open-house step is executed for the MIBOR region? This step should populate info. like open-house date / time etc...

3 -- I'm not sure if had the photo-downloader step enabled for this specific test. If it was, can you pls check why photos have not been downloaded for newly added listings? Also, no records have been added in the MLSPhotos table.

4 -- When you have chance, can you pls add some logging in the MIBOR steps' programming? This will help us in verifying updater steps, location errors (if any).

I'm currently working on finishing one of the open tasks. Hopefully that would be completed in another day or two. I'll then be able to test the updater thoroughly.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

I completed local testing. As I understand I have to add the appropriate records into table Updater_MLSRegionUpdateSteps. What should be set for field [LastUpdateTimeStamp], i.e. do we need to receive all records from REST server or not? And also I'm ready to perform testing on dev DB as you wrote in note 25.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar, I got RETS meta-data with using of libRETS code samples. Attached are archive with meta-data in text and xml format. In my opinion spreadsheet T5field_info_RETS_Updated01-17-09.xls looks more complete. For example ResidentialProperty object: Field #3: in spreadsheet it has name "Acres", in meta-data isn't any standard name for this field ([] - it means standard name is missed). I thought I can use this value to fill field Acreage of Property object. Is my assumption wrong? Posted by Molchanov George(unfuddle username: george)

[Battler45]

Ben,

RE: #65: Removal of listings in SoCal is performed in two-steps:

1 -- soft-removals - whenever a listing's status is changed, that change is recorded in the "History" resource. The SoCal removal step queries for all listings whose status has been changed to one of the inactive statuses (i.e. Withdrawn, Expired, Closed etc...).

2 -- hard-removals - in certain cases, listing records are physically removed from the RETS database. Details of such listings are stored in the "Deletions" resource. The SoCal removal step queries for all listings, which might have been removed from the RETS server.

Any listing found through one of the above steps is then removed from the sierrainteractive database (along with its associated records).

I verified that the MIBOR RETS meta-data does have corresponding History (PAR) and DeletedListing resources but we don't have access to these resources. Looks like our user-account may have very limited access in that several resources like OpenHouse, Media, History, DeletedListing are not accessible. Can you pls confirm with MIBOR representative about the same?

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

For Reference - The MIBOR updater is modified to only pull Active listings. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar / George:

RE: #52

Unfortunately, the performance of the existing photos server (DEDQ61) is not at an acceptable level, and so we will need to move all of those photos, either to the new cloud server (where the SoCal photos are) or to a 3rd party service like Amazon or Azure.

The DEDQ61 server is a very old server with an under-powered hard drive, and so at peak times the photos are very slow to load. My goal is that, once we are able to covnert all existing regions over to the new updater system, we will be able to retire the DEDQ61 server.

I do agree that the best long-term approach would be to host the photos on a 3rd-party service, either Amazon or Azure. Do you have any preference toward one or the other. I am really not sure of the technical details of using such a service in combination with our service, and I'm also not sure how to determine the cost. Is that something you could help me understand?

Perhaps we could use MIBOR as a test case for moving photos over to one of these services, with the goal of eventually moving all photos over.

The one issue I have had thus far which has made it difficult to use a CDN is photo expiration. Currently, each listing has a set of photos which gets distributed out via a CDN powered by Level 3. However, because agents change photos for listings on a regular basis, and these updated photos often have the same filename as the original, I have not found a good way to "expire" the old photos when they have been updated, other than to set the expire (TTL) value to 1 day for all photos.

This means that all photos are re-loaded into the CDN once per day no matter what, and creates a much heavier load on the server than there needs to be.

Do you have any thoughts on how this might be improved? My only idea is that when new (replacement) photos are received for an existing listing, they should always be given a new filename. This would eliminate the above problem and ensure that the newer photos are always displayed for every listing, and it would also allow us to cache all photos on the CDN for a much longer time (30 days).

If we do implement something like the above, we just need to make sure that the proper order for the photos is preserved using the OrderID field in the MLSPhotos table.

I agree that this is a pressing issue and so if you could continue to split your time between this topic and the clustering issues, that would be great.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

Sorry for the delayed response -- yes, you are correct that for the initial data load, we will need to import all photos new from the server (this process will likely take a couple of days).

I have been talking with Vihar for the past couple of days, and we are going to change the max dimensions of the large size of the imported photos. Up to now, we have allowed a maximum width or height of 550px, and going forward we are going to allow a max width or height of 640px. Vihar may have already made this change in the code, but please confirm with him before you begin the photo download.

Thanks.

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Ben,

I've made changes to the production and dev DBs to add a new column, PhotoServer (bit) to the MLSPhotos table. When 0, it will indicate photos hosted on the old server, and 1 will indicate photos hosted on the cloud server. Currently its value is set to 1 for all records in the sierrainteractive-4 DB and 0 for all other records.

As mentioned in the above comment, the photo-migration app is working fine so we can now begin working on transitioning the MIBOR updater and its photos to the cloud server. I'll test the MIBOR updater on development server tomorrow; will update you on the same afterwards.

Also, we can now update the programming to build photo-urls to use the PhotoServer value. I think it would be a good idea to create a common function, which can then be called from all pages for displaying photos. My understanding is that the following pages will need to be updated:

• list
• map
• detail
• detail2
• search-application
• content widget

Pls let me know your thoughts. I'll also update the new CAA accordingly.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

RE: #79 - I've reviewed the photo-migration application code. It looks very good. I'll test the application on my local machine. I'll let you know if I run into any issues. As for your questions:

1 -- If a photo cannot be processed for some reason, we should let it remain in the database. The application will log exceptions so that should help us in reviewing such photos. Since the PhotoServer value of such photo-records would be 0, they would continue to be served from the old server (assuming image-files exist and are in valid format)

2 -- As for the database changes (new field and stored-procedures) - since those changes are related to the updater system, pls add them in the "schema changes.sql" and "stored procedures.sql" files respectively.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

I see -- to give you a bit of background, there are a number of different data providers around the United States. These are companies that build and maintain the software for individual boards of realtors. The Socal MLS and the Indianapolis MLS (MIBOR) both use a company called MarketLinx.

This means that, although some of the fields and field names may vary between SoCal MLS and MIBOR MLS, most of them will be the same, and the way you query the database for MIBOR will be very similar to the way the database is queried for the existing SoCal MLS updater, and so you should feel free to look at SoCal MLS updater as a reference for MIBOR. (Other boards of realty use different data providers, and so this will not always be the case.)

That said, I am not sure of the answer to your question, but I believe Vihar should be able to answer this for you, since I am sure it is the same in MIBOR as it is in SoCal MLS. (My guess is that "Change Date" is the correct answer, but I am not certain.)

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

I've logged off from the server. Can you pls try after 5 mins and let me know if you can log into the server?

As for the changes to be applied to your local copy - you'll need to merge the remote changes into your local branch. This can be done by executing the following command git merge origin master. I'm not sure if origin is the name of your remote branch. You can check the same by executing git branch -r command; this will list all remote branch maintained by the git repository.

Alternatively, you can run git pull origin master. This will first fetch remote changes and immediately merge them in your local repository. So in a way it does the job of git fetch and git merge.

Pls let me know if you have any queries.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

Yes, it's definitely okay to connect to live servers for testing purposes. Since the data feed only comes in one direction (from them to us), it's fine to query during the testing process.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Ben,

RE: #57 - I had worked on a project that used Amazon S3 service for hosting photos. It is fairly straightforward (from technical viewpoint). And, from what I have read about Amazon CloudFront (CDN), it would be straightforward to use it in conjunction with S3 service.

Even though I don't have experience of using Auzre service, I believe it would be straightforward as well. And, I guess it would be easier to use it in a .NET based project like ours. So it eventually boils down to costing of these two services. Both these services do provide calculators for determining approximate costs

• Amazon (http://calculator.s3.amazonaws.com/calc5.html)
• Azure (http://www.microsoft.com/windowsazure/pricing-calculator/?campaign=vw-calc)

That being said, I think we'll need fairly accurate data to evaluate costing of each service. Would it be possible to get an approximate data for the followings?

• total number of images being served daily (for all front-end sites)

• total number of new images downloaded daily (this would give us total number of images to be uploaded daily)

• total space required to store all images. The DEDQ61 server appeared to be processing updates so I didn't check the total size of the MLS folder. The size of SoCal images is ~30GB so perhaps we would extrapolate it for the other regions.

My guess is that the monthly cost should be within $75-$100 range (or even less). Please note that this cost includes both storage and CDN charges. I'm not sure about our existing costs. If the cost of ramping up Crystaltech cloud server's hard-disk space to say another 100GB is fairly low and monthly cost of Level-3 CDN service is also lower, perhaps we should continue with the current approach of hosting images on the Crystaltech cloud server.

Pls let me know your thoughts.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben,

Assuming we will be continuing with the current setup of hosting images on the Crystaltech cloud server, there are a couple of approaches for moving MIBOR images:

1 -- to re-download all photos through the new updater system. This will require minimum change in that we would need to update the photo-url generation to take into account active photo-server value (as described in comment #52). This will however be fairly time-consuming process as all photos would have to be re-downloaded from the RETS server and re-processed.

2 -- to write a small application, which will copy processed images from the old photo-server and update the database records with new path value. This should be faster compared to the 1st approach in that we'll be using FTP interface for downloading photos from another Crystaltech machine.

Alternatively, all images can be copied manually from the old server to the Crystaltech cloud server, and, then the new application should be run to update db records. This processing would be fairly quick in that the processing would be done locally. However, the time to FTP all images to new server might be high assuming large number of images would need to be transferred.

In either case, the new application will be useful in moving photos of other regions as and when we convert them to new updater system.

My thought is that regardless of the approach followed, we should add a new field, PhotoServer (comment #52), and update the photo-url generation logic. This will ensure that photos are continued to be served correctly until all photos have been moved to the Crystaltech cloud server.

Pls let me know your thoughts / suggestions.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben, Vihar I have some questions regarding common rules of fields' mapping. 1) What should I do if I can not find an appropriate source field for some field of object Property (for example SoCal model has data for ListAgentLastName - p.AgentLastName = resultSet.GetString("ListAgentLastName"), - but I can not find such field in MIBOR's fields). 2) On the other hand there are a lot of MIBOR fields which are not mapped with object Property. 3) Special case is the composite fields (like FeaturesExterior). Such fields in SoCal implementation have fixed headers for specific source fields (for example in composite field FeaturesExterior can be added field PatioFeatures with header Patio). Are these headers fixed or for MIBOR field FeaturesExterior can contain other data with other headers? And if yes - do you have some ideas which MIBOR fields can be included in composite fields of object Property? Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

I'm working now on the photo-migration tool. I hope the first revision will be ready today.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

Re. small application (comment-#71) - I realize that there would thousands of records in the MLSPhotos table for a region so it won't be good idea to select all records in one-go. My thought is that it would better to process photos in batches of x number of records. I'm doing something similar in generating email-alerts. If you can take a look at the EmailAlertService.cs file, the SendAlertsInBatch method retrieves records in batch of 100 records. We can do something similar in moving photos to new folder structure. I think it would be good to process photos in batch of 1000 records.

In addition, it would be good to retrieve only unprocessed records. That is, when a photo is moved to new structure, the corresponding MLSPhotos record's PhotoServer field would be set to 1. So in retrieving batch of MLSPhotos records, we could add a filter to retrieve photos that haven't yet been processed (i.e. having PhotoServer = 0). This will allow us to deal with a situation where the application may have to be restarted / rerun.

Pls let me know if this sounds fine.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

Thank you. I able to connect to 208.106.250.174 now.

Thanks Posted by Molchanov George(unfuddle username: george)