Convert Region 25 (MIBOR / Indy) to new updater

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Ben,

I've tested the MIBOR updater on the development server. It worked fine. I guess we can now transition the MIBOR updater to the new updater. However, we'll first need to migrate existing photos to the cloud server. Pls let me know your inputs on comment#93.

Thanks Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

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 commented 4 years ago

Ben,

The only other open point is re. import of Open House details. As George had mentioned earlier, it looks like our MIBOR RETS account doesn't have access to open-house details. Pls let me know if the open-house step can be ignored for MIBOR region.

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

Battler45 commented 4 years ago

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 commented 4 years ago

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

Battler45 commented 4 years ago

Ben,

I've added a couple of new functions at the bottom of Inetpub_LIVE\Sites\MLS_Tools_Integrated\res\includes\functions.asp. These functions are used for forming photo-urls based on the PhotoServer value. In order to test these functions I had switched the condition to generate photo-urls based on the new format.

That said, I realize that I had overlooked the fact that several regions don't have PhotoPath value, and, therefore, photo-url format would be the same regardless of the PhotoServer value. This would have been the case for schulerbauer as photos for regions 3 / 4 don't have PhotoPath values. I think we are fine in this case. My apologies for the oversight.

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

Battler45 commented 4 years ago

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 commented 4 years ago

Vihar,

Re: #61 - From what I can tell, pricing is fairly similar. Unfortunately, I don't have access to the type of data we would need to accurately assess the monthly costs, but even if it turned out to be double your estimate, that would be do-able for us and worth it if it allows us to provide customers with images that always serve quickly, and reduces the load on our dedicated servers (and thus reduces the need for additional servers).

I'm running a file tree right now which will tell us exactly the total size of the MLS folder on DEDQ61 -- I believe it is 50-60 GB currently, so I think we are still under 100GB total for all photos.

By the way, 30GB does seem like a lot for the SoCal photos -- the overall size of the storage on the server for this region should remain fairly consistent over time, shouldn't it (aside from the increase in the size of the large photos that we introduced recently)? Can you double-check to make sure that when a listing is deleted from the server, all associated photos are also getting deleted?

(Also, can you please restart DEDN173 overnight tonight -- it needs to be restarted following updates.)

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

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 commented 4 years ago

Ben, It is very helpful! Thank you!

Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

We needed to change the data-types of Latitude / Longitude columns (Geocodes table) to numeric(12,9). This led to change in a couple of updater specific store-procedures, and, hence, change in the new updater system. I've made these changes, and, have pushed the same to the updater repository. Pls fetch the latest changes and update your local copy.

Pls note that you'll also need to make a minor change in the way latitude / longitude values are set in the MIBOR import. Until now these values were set as string, but, now they would need to be set as Double. Could you pls update MIBOR import code to set latitude / longitude values as double. You can use the ToDouble string extension I've used in SoCal importer. Pls let me know if you have any queries.

Pls note that I've applied these changes to the dev. sierrainteractive-1 DB. I've attached an SQL script that you can run on your local DB to apply data-type specific changes.

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

Battler45 commented 4 years ago

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 commented 4 years ago

Ben,

Developing a small application for data comparison sounds good. The only concern I've is that in using this application, the old and new updater would have to be synchronized. That is, the tests would have to be carried out only after the old updater has run; otherwise the comparison application won't be much of a use. Could you pls confirm the time when the MIBOR updates are completed (through the old updater)? George can then schedule the new updaters to run around that time, and, then can use comparison application to verify correctness of data. Pls let me know your thoughts on this.

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

Battler45 commented 4 years ago

Please place further comments into Basecamp, here:

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

Battler45 commented 4 years ago

Vihar,

I reviewed the changes, all is fine

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Ben,

I couldn't verify the following widgets for the PhotoServer specific changes:

MLS_Tools_Integrated\widgets\carousel.asp
MLS_Tools_Integrated\widgets\carousel-listings.asp

I'm not sure which pages make use of these widgets. I did check http://realestate3000.sierrainteractivedev.com for carousel-listings.asp, but it appears to be including the site-specific carousel page ([Site-Folder]/res/includes/listing-carousel-2.asp). In fact, similar pages are being used in various other sites like hometoindy, weirproperties and so on... Pls let me know how to verify the above two widgets.

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

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Ben, Vihar,

I have some questions regarding outstanding issues (geocodes and open houses details).

Geocodes. It looks like old updater doesn't get latitude and longtitude from MIBOR response. Isn't? I found in meta data some fields which probably can be used for this - DrivingLatitude and DrivingLongitude but I'm not sure. Anyway in my tests all MIBOR records don't return values for these fields. Also I've added functionality of geocode processing (similar to SoCal functionality) and it works.
Open-house updating. Can it be that MIBOR's user doesn't have some rights to get open house data? I've tried to get such data and I received error "User does not have access to Resource named OpenHouse".

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

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 commented 4 years ago

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 commented 4 years ago

George / Vihar,

RE: #53

You are correct -- Open Houses are not available for MIBOR and so can be excluded from the update process.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Ben,

Re. #68 - old and new updaters call the same stored-procedure, spUpdateMLSPhoto for updating a photo-record. So perhaps that sp can be updated to set changed-date in the DateAdded field. Pls let me know if this sound fine. I'll update the sp.

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

Battler45 commented 4 years ago

George & Vihar,

I do think it might be worthwhile to develop a small application which would compare data rows between dev and live DBs for the same listing to make sure that the data is the same -- mainly because we do have a number of additional regions to convert from the old updater to the new updater, and so this application would be used for all of those regions and would therefore save us quite a bit of time in testing.

Unless, Vihar, you have already written some scripts that will do this type of comparison or have something else in mind?

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

Battler45 commented 4 years ago

Vihar,

I've fixed issues with ListType and SourceUniqueID.

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

I'll merge those changes to the master branch later. There may be some conflict in merging those changes so I'll work on it after finishing my current task. I believe your current work shouldn't be affected by the merge. Pls continue to work / push changes to the master branch.

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

Battler45 commented 4 years ago

Ben, Vihar,

I've completed latest minor changes (Driving Longitude / Driving Latitude, ListTypeDescrip).

Also I've added IndianapolisListingsPurger class to delete records but there is again known issue - User does not have access to Resource named DeletedListing.... Resource name is correct (according meta data), but it doesn't work. And we have not other ways to delete records as we getting only active - we can not get records with other statuses.

What we will do?

Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

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

Battler45 commented 4 years ago

Ben,

I've checked MIBOR's RETS metadata and below is list of available classes:

Residential Lease (is not used in updater)
ResidentialProperty (data is importing as Residential type)
Commercial Retail (is not used in updater)
LotsAndLand (data is importing as LotsAndLand type)
Commercial/Industrial (is not used in updater)
Commercial Office (is not used in updater)
Commercial Multi-Family (is not used in updater)
Farms & Agricultural Land (is not used in updater)
MultiFamily (data is importing as MultiFamily type)
Commercial Land (is not used in updater)

Also I found field "Sold Trigger" which probably can be used in data query. But I don't know if it is correct and how to use it. In data feed this field is unavailable and I don't know which values are acceptable. I've tried to use Y, N, 1 and 0 and everytime I got the same set of listings.

Thanks

Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

Vihang,

Since we need to test content for the sites in development and we'll be entering that content in the live DB, could you restore the development sites to pull content from the live DB rather than the development DB?

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

Battler45 commented 4 years ago

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 commented 4 years ago

George,

Yes, it appears I made a mistake. I thought that we need to pull both Active and Pending listings for the MIBOR feed.

We only need to pull the Active listings.

Can you please update the MIBOR updater accordingly, and then run an update that will remove all Pending listings currently in the database?

Thanks,

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 / SierraMigrationToGitHub

Convert Region 25 (MIBOR / Indy) to new updater #38