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]

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]

George,

First, there are no fixed rules regarding which RETS fields get mapped to which fields in our database, meaning there are

no requirements mandated by the data providers and we can set our own rules.

That said, we do have general guidelines regarding which RETS fields get mapped to which fields in our database, and I

will try to outline those for you here.

First, it will probably be helpful for you to review the schema and data in our MLS table if you have not already done

so. As you'll see, there are essentially three types of fields:

1 - Those that reflect a single value from the RETS feed, such as MLS, SqFt, Price, etc. These are relatively self-

explanatory.

2 - Composite fields, which draw from multiple fields in the RETS feed. These fields in the MLS table primarily begin

with "Features..." as in "FeaturesInterior," "FeaturesExterior," etc.

3 - Bit or TinyInt fields which typically contain boolean values of 1 or 0, such as Pool, Spa, REO, Foreclosure,

ShortSale, Equestrian, etc. These fields are used in our application to make the search process quicker. Each of these

fields represents a search field on one of the search forms so that when a visitor searches for a home with a pool, for

example, we can easily search against the Pool field in the MLS table to find matching results. Determining whether each

of these fields is true for a given property record can often involve a number of steps.

It might also be helpful for you to review the MLS data as it is presented on a client's website. Here are a few

examples for you:

http://www.hometoindy.com/property-search/detail/25/21125020/15630-shining-spring-drive-westfield-in-46074/ http://www.hometoindy.com/property-search/detail/25/21044899/1708-west-161st-street-westfield-in-46074/ http://www.hometoindy.com/property-search/detail/25/21136101/136-harrowgate-drive-carmel-in-46033/

---

Regarding the composite fields for MIBOR, I've outlined them below. Please refer to the mls_functions.asp file for an

example of how these fields were assembled using this data in the Classic ASP application (starting at line 460).

1 - FeaturesBasement / Describes the basement of the property

- "Base Desc(21)" as "Basement:"

2 - FeaturesConstruction / Describes the construction of the property (is it new construction? what stage of

construction is it in? what type of construction is the building? etc.)

- "Const.Stg(513)" AS "Construction Stage:"
- "Cnt Type(523)" AS "Construction Type:"
- "New Const(529)" AS "Construction Status:"

3 - FeaturesFinancial / Includes information on such things as property taxes, the last year in which taxes were paid on

the property, whether the property is in Foreclosure, financial disclosures, etc.

- "Semi Tax(284)" AS "Semi-Annual Property Tax:"
- "Tax Yr(292)" AS "Tax Year:"
- "Discltwo(1406)" AS "Financial Disclosures:"

4 - FeaturesDocuments / Similar to FeaturesFinancial, includes other types of disclosures and any other types of

documents available for examination in the sale of the property.

- "Discl(77)" AS "Primary Disclosures:"
- "DisclOther(1408)" AS "Other Disclosures:"

5 - FeaturesEnergy / Describes items related to the heating, cooling and electricity of the property

- "Wtr Heat(305)" AS "Water Heater:"
- "Heat(100)" AS "Heat:"
- "Cool(72)" AS "Cool:"
- "Fuel(99)" AS "Fuel:"
- "FP Desc(92)" AND "FP(93)" AS "Fireplace(s):" . This one is tricky, because you need to combine two values.  

Please see mls_functions.asp line 645 for an example of how this is done.

6 - FeaturesExterior / Describes items related to the exterior of the home, such as patio, porch, pool, spa, etc.

- "Ext Amen(11)" AS "Exterior Features:"
- "Porch(528)" AS "Porch / Patio:"

7 - FeaturesFarm / Describes items related to the farm, if the property is a farm

- None

8 - FeaturesFencing / Describes items related to the fencing around the property, if present. In this case the Fencing

description is included in "Ext Amen(11)" field under Exterior Features in the RETS data feed.

- None

9 - FeaturesFoundation / Describes the foundation of the property, if present

- None

10 - FeaturesGarage / Describes the garage of the property, if present. This field often overlaps with FeaturesParking,

and eventually we will probably combine those two fields into one.

- "Garage(1309)" AS "Garage Type:" 
- "1409" (not sure the name of this field) AS "Garage Features:"

11 - FeaturesHOA / HOA stands for "Home-Owners Association". Some properties are located in an area where they are

forced to belong to a local home-owners association. They usually get some benefits from this, such as belonging to a

community swimming pool, but they also must pay a fee to the association on a monthly or annual basis.

- "Mand Fee(137)" AS "Mandatory HOA Fee:"
- "MandFeeInclude(741)" AS "HOA Fee Includes:"
- "Mnd Fee Pd(145)" AS "HOA Fee Paid:" 

12 - FeaturesInterior / Describes the interior of the house, such things as interior areas, eating areas, appliances, etc.

- "Interior(520)" AS "Interior Features:"
- "Areas (Interior)(527)" AS "Interior Area(s):"
- "Eat Area(193)" AS "Eating Area(s):"
- "Appl(315)" AS "Appliances:"
- "Equip(316)" AS "Equipment:"

13 - FeaturesGeneral / Catch-all for items that do not fit in any of the other areas.

- "SF (M+U)(255)" AS "Above Ground Sq. Ft.:"
- "Mn SF(254)" AS "Main Level Sq. Ft.:"
- "Up SF(272)" AS "Upper Level Sq. Ft.:"
- "Tot SF(270)" AS "Total Sq. Ft.:"
- "SF Src(269)" AS "Sq. Ft. Source:" 

14 - FeaturesLot / Describes features of the lot surrounding the property, such as lake on the property, or barn, or wooded land, etc.

- "Lot Info(141)" AS "Lot Features:"

15 - FeaturesLotAccess / Describes ways in which the lot can be accessed (what type of road, etc.)

 - None

16 - FeaturesParking / Describes parking availability for the property. This overlaps with FeaturesGarage.

- None

17 - FeaturesRoof / Describes the roof of the property. This sometimes overlaps with FeaturesConstruction or FeaturesRoof.

- None

18 - FeaturesUtilities / Describes the utilities available to the property, such as electricity, water, etc.

- "Util Opt(302)" AS "Utility Options:"
- "Wtr Src(306)" AS "Water Source:"

---

One last thing regarding the data in the MIBOR feed. Unfortunately, the data as it comes from the feed is full of abbreviations which are hard to read. For example, in the data feed, "FinCeling" is used in place of "Finished Ceiling."

To make the data more readable, in the current updater I created a series of arrays which can be used to decode the data from the RETS feed into readable values. This file (mibor_features.asp) works in connection with mls_functions.asp (using the DecodeAbbrevs as you will see) and is also attached.

The SoCal MLS does not do this type of abbreviating and the functionality to decode abbreviations is not present in the SoCal MLS updater you are working from, and so you will need to integrate this into the MIBOR updater in some way.

---

That covers all of the composite fields. Once those are set, we will still need to work on setting the bit fields (REO, Foreclosure, Pool, Spa, etc.) I will send you notes on that tomorrow.

I hope this has been helpful -- please let me know if you have any questions.

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

[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]

Ben,

Can you pls confirm if SchulerBauer site is served from the DEDN173 server (along with hometoindy and joehaydenrealtors sites)? Actually I wanted to verify the changes I've made for building photo-urls. The schulerbauer site doesn't appear to be affected by those changes.

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

[Battler45]

George,

Per Ben's feedback,

1 -- the current format of Features* value is fine. Pls ignore my comment.

2 -- the approach to remove all photos and re-download them sounds fine for MIBOR region. In SoCal RETS server, we do have access to Media resource, and, therefore, we could make use of the modified photos' details. Unfortunately, we don't have access to Media resource in MIBOR RETS server so pls continue you with your approach of deleting and re-downloading all imagescomment).

3 -- since we have access to only Active / Pending listings no need to make any changes for the same. The MIBOR importer is already populating the Status field (MLS table) with appropriate value from the RETS field, 134 (Status) so no further change needs to be made in this regard.

Pls make changes for the other points discussed above. Pls let me know if you have any queries.

Also, in reviewing the photo-processing code I realized that it is the same for both regions. Therefore, I've renamed one of the PhotoProcessor classes to DefaultPhotoProcessor and removed the other. As with DefaultGeocoding, we can continue using this class for other regions unless there is any change in the approach.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[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]

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]

George,

I've modified the MIBOR updater to ignore listings with "Public Internet" set to "No". I've pushed those changes and planning to run a full update of all Indy listings in the next 15 minutes. If you happen to check this before that can you pls confirm the changes?

Ben - removing the existing listings with the "Public Internet" set to "No" would be a bit tricky in that those listings are already added to the MLS table, and there is no other way to remove them other than removing those MLS records through the backend. I was thinking that it would be fine to execute the spRemoveListingByMLSNumber stored-procedure manually for all such listings. That is, after the full update is completed today, the DateLastUpdate date of all active listings having "Public Internet" set "Yes" would have changed. The DateLastUpdate field of the other listings, most likely having "Public Internet" set "No", would remain unchanged and so it would be fine to remove such listings by executing the spRemoveListingByMLSNumber stored-procedure manually. Pls let me know if you see any issue with this.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

Ben would be able to provide more details about exact mappings for the above mentioned fields.

Ben, could you pls check the mapping file attached in comment-17 and let us know the mappings for the remaining Features fields?

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

[Battler45]

Vihar, I'm testing now local. So there is no my data in remote DBs. Thanks Posted by Molchanov George(unfuddle username: george)

[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]

Ben,

Vihang has restored the most recent sierrainteractive-1 DB on dev. DB server. Pls note that he has also updated the dev. front-end sites to point to dev DB for displaying content-pages.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben,

I've made changes in the new CAA and new updater systems to use the PhotoServer value in building photo urls (not published yet). I'm now working on making similar changes in the front-end sites programming. I'm looking into updating the dev. version of search-tools / content pages for the same. I'll update the live sites tomorrow (during your overnight period).

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

[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,

Yes, I agree that it makes sense to add a new field, photo-server, to be used in conjunction with the MLSPhotos table for situations such as this.

As far as the best way to copy images, I believe the fastest and easiest way would be to use an FTP program to copy the photos manually to a location on the new (cloud) server from which they could be processed by the new updater into their appropriate folder locations. I'm not able to set up an FTP account right now to connect to the appropriate folder on DEDQ61 to allow this file download due to operations currently taking place on that server, but I will do that tomorrow and forward you the FTP credentials.

If you or George could begin writing the application that will move / process these photos once they are downloaded, that would be great.

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

[Battler45]

Vihar,

I think it is fine idea to host images on 3rd-party service.

Also I want to notice that MIBOR updater is not completed as I didn't test open-houses updating.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar & George,

I just realized that we will, of course, need to re-load all photos because the photos will all be located on a different server, and the folder structure used by the new updater is different than that of the current updater.

Vihar, the problem with this is that we can't just delete the existing region 25 listings from the database, because all of our lead records reference the IDs of these listings in the database. Really, all we need to do is update the MLSPhotos records for each listing -- everything else can stay the same, right?

Do you have any thoughts on what might be the best way to make that transition?

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Vihar,

Populating SourceUniqueID into current listings will not be a problem. I will re-run the MIBOR update for today on the old updater and modify it to include this field for all listings. I will let you and George know as soon as that is done.

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

[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]

George,

I tested the MIBOR updater on local machine. Overall, it appears to be working fine. There are a few points that might require changes:

1 -- Looks like the DrivingLongitude & DrivingLatitude values are not available for majority of the listings so we would have to geo-code all listings through the Geocoder step. In that case, I think, the code, which processes latitude / longitude values (last few lines in the IndianapolisListingsImporterForProperty.cs file) can be removed.

2 -- The ListType and ListTypeDescrip values are not correctly set. The ListType value should reflect the type of listing (i.e. 1 for residential, 4 for land/lot, 5 for multi-family and so on...). The ListTypeDescrip value should reflect sub-type (i.e. condominium, residential, single lot and so on...)

Ben - I checked the meta-data for the MIBOR region but couldn't find any suitable field, which could be holding sub-type description. I've attached a zip file containing meta-data / actual data for your reference. Pls let me know the field, which can be used for populating ListTypeDescrip value.

3 -- Per the current implementation, importer downloads all listings regardless of their status. Ideally, it should only process listings having status Active / Pending. There is a field, Status (134) which can be used for retrieving Active / Pending listings only.

Ben - Strangely though when I queried the RETS server to get listings filtered by change-date, it returned active / pending listings only. Perhaps listings with other status might not have been changed. Per the meta-data, a listing can have one of the following statuses: Active, Expired, Leased, Pending, Released, Sold or Withdrawn. Do you think it would be fine to ignore the listing status?

4 -- In the live database, each section in the Featured* field is enclosed within

. For example,

FeaturesBasement -

Basement: [Actual Values]

FeaturesConstruction -

Construction Stage: [Actual Values]

Construction Type: [Actual Values]

Construction Status: [Actual Values]

Pls change the MIBOR updater to set Features* values accordingly.

5 -- Per the current implementation of photo-downloader, if a listing already has one or more photos in the database then all those photos are removed and re-downloaded. I think this might increase the time to download / process all photos. I guess it should be alright to remove all photos only if the number of photos on the RETS server is less compared to the number of photos in the database. Pls let me know if you see any issue with this.

6 -- The only missing step (other than the OpenHouse step) is removal / purging of old listings. As and when listings are sold / withdrawn, they are made unavailable from the REST server (either physically or by changing their status). We need to take this into account in synchronizing RETS data to our database. So an additional step will need to be implemented (in line with the SoCal listing purger step (18)), which will process such listings and remove those from our database. Can you pls review the SoCalListingListingPurger class and implement similar step for MIBOR region?

Pls let me know if you have any queries.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Ben,

Yes, I made changes within the Inetpub-LIVE folder. To be specific, I wanted to check the old list / detail pages for photos. In doing that, I made changes so that photo urls were invalid. I then checked those pages expecting them to not show photos but photos were displayed correctly. Perhaps it may have something to do with caching; strangely though photos were not rendered in hometoindy list page. I'll spend more time on this later today.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

Through Librets, there is a way to pull the system meta-data, which is an outline of the table structures of all tables -- kind of like the database schema. So, it gives you all of the available fields for the Residential property type, what data type each field is, the length, etc. And it does this for each table in the RETS database.

I'm not sure how you pull the meta-data using librets, but if you search the librets documentation for "metadata" or "meta-data" I am sure there is an easy way to do it.

This is similar to the spreadsheet I attached in the first email, but a bit different in that it is more complete and more up-to-date, and so it would probably be preferable to use this as opposed to the spreadsheet.

RE: #11 above:

1 - These are good questions. Each data feed is, unfortunately, different. In the case of the MIBOR feed, they only use one field for the full agent name ("Listing Agent Name"), rather than separate fields for the first and last name, so you will need to use string functions to parse the full name into a first and last name.

2 - Yes, it will usually be the case that not all available fields in the data feed will be mapped. This is the most difficult part of setting up a new data feed -- determining which fields should be mapped, and which should not. Often there will be multiple fields with similar names, and we will need to determine which one contains the data we are actually looking for.

There are certain fields which all feeds will contain (using different field names) and that we will always want to use, such as agent name, listing price, and MLS #. I will try to prepare a list of these to send over to you.

Then, there are fields available in some feeds but not others, such as different types of interior and exterior features, etc. The difficult part is determining which of these fields need to be included. I will help you with this now, and over time you will get a better sense of this.

3 - Some of our fields (mainly those beginning with "Feature" such as "FeaturesExterior") do contain composites of multiple fields from the data feed. There are no fixed rules here, and so while FeaturesExterior might combine 4 different data fields in SoCal MLS, it might combine 5 different fields in MIBOR. This needs to be worked out on a case-by-case basis, following common-sense guidelines which will become more clear as you gain experience working with these systems.

To help you start, I will try to prepare a list of which fields in the data feed should be mapped to which fields in our database and will send that over to you later today, along with a general explanation of the type of data each composite field should include.

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

[Battler45]

Vihar,

Yes, that does sound like a good plan, please proceed.

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]

Vihar,

I've committed code of photo-migration tool. You can find two new projects Sierra.Updater.PhotoMigrating (classes to perform migrating actions) and Sierra.Updater.PhotoMigratingApp (console application).

Now there is implemented simple writer of images into file system (new folders' structure). It can be easily replaced with any other writer (for example with uploader to some cloud system).

I have two questions

1. What should be behaviour of application when some photo can not be processed? Should we remove such record from database completely or we have to mark it as problem (it means we need one more field in database)?

2. How I have to commit database changes? My changes are

• script to add PhotoServer field in table MLSPhoto;

• two stored procedures in database with table MLSPhoto.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

I thought scripts under Updater\Sierra.Updater.Database folder are intended only for sierrainteractive-updater database. Isn't? And my script has to be run on sierrainteractive-n databases.

Also I attach script here for your testing while it is not committed.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

I implemented the new approach to delete listings of MIBOR region. It works pretty fast (about 28 thousand records from MIBOR RETS server). All is committed excepting sql script with stored procedure for sierrainteractive-n databases (it is attached).

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

Sorry for not being specific. I actually meant the files present under the Updater\Sierra.Updater.Database folder. I believe you've already made changes to the "scheama changes.sql" file to associate steps for MIBOR region. Pls add the new field statement to that file.

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

[Battler45]

George & Vihar,

Vihar, would you be able to modify the connection strings on the dev version of the search tools to pull from the development DB for sierrainteractive-1 sites?

Then, George, you will be able to test your work using the http://hometoindy.sierrainteractivedev.com site -- what you see there will need to match what you see on http://www.hometoindy.com when viewing details for the same listing.

This should allow you to test your implementation until the data in the development database (using your updater) matches exactly with the data in the live database (currently running on the old updater).

Vihar, I don't know if there is a way to allow George to query the region 25 records on the live DB for comparison purposes -- perhaps just viewing the data on the live site would be sufficient?

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

Thanks for the script. I'll run them on sierrainteractive-n DBs and test your changes. I'll let you know my feedback afterward.

As for sql scripts - you are correct in that scripts under Updater\Sierra.Updater.Database folder were created for the updater database but it would be fine to include changes for sierrainteractive-n DB. Anyway, if you are comfortable with attaching your changes on unfuddle, that is fine as well. I'll copy them to the main repository.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Please place further comments into Basecamp, here:

https://basecamp.com/1767592/projects/353414-074-convert-region

[Battler45]

Vihar & George,

Thanks, everything appears to have updated correctly and to be working properly now.

George, I do have one more question for the Indy updater -- the contact there says that the Sold data for this region should now be available through the RETS feed. Can you check the RETS metadata for this reason and see if the Sold data is now available as a new class?

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

[Battler45]

Ben,

Was about to update you on this!! There were some issues in re-running full updates of MIBOR records. It failed thrice with the "Error occurred in logging into inr.rets.interrealty.com server" message. I'm not sure if this had anything to do with the full updates. Moreover, doing a full-update would have consumed a lot of time (approx. 8-10 hours) therefore I aborted the full update and ended up choosing a different approach.

I've modified the Indianapolis listing removal functionality to remove all listings that don't have "Public Internet" set to "Yes". This will not require manual removal of listings. In addition, it will also remove photo files from the server. The modified removal-functionality appears to have removed all listings having "Public Internet" set to "No". Pls check the same and let me know in case of any issues.

As for the point about not displaying address - I verified that we've programming in place to change the address to "000 Confidential Ave." for listings where the "Address yes/no" field is set to "No". There are total 137 listings in the database having this address.

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

[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]

George,

I agree - it should be "Change Date". If we filter records by that date, the server should return records that have been changed since the specified date. Also, can you pls share the MIBOR meta-data? I'll use it as reference for calrifying / confirming other this and mappings.

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]

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]

Vihar,

I've fixed issues with ListType and SourceUniqueID.

Thanks Posted by Molchanov George(unfuddle username: george)

[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]

Ben,

Thanks. The changes to .htaccess have now taken effect. Pls note that I've reset the passwords for the 'n3wt0n' and 's13rra' for consistency.

Pls let me know your inputs on comment #99 when you have chance.

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