How to effeciently replicate RETS data
A common use when accessing RETS data is to download or “replicate” that data from the remote server into a local database. This approach has several advantages over using RETS data live from the RETS server:
- Response times accessing your local database are significantly faster than connecting to a remote RETS server for each request.
- While the RETS server may limit which fields you can search on, a local database would allow you to search on any field.
- The MLS may limit what time of day you’re allowed to access the RETS server which essentially requires that data be retrieved and stored.
- Your local database has the ability to sort records where a RETS server does not.
- You may be limited to the number of records you can retrieve in a single RETS response where a local database wouldn’t have that limit.
Steps
Replicating data rather than using RETS data live is slightly more complex, however. In order to replicate the RETS data into your own database, a series of processes are needed in order to make sure the data you have is both updated and accurate. Those processes typically include the following actions:
- Grab the entire database (one time only)
- Run incremental updates to find changes to records
- Run a cleaning process to purge out “old” records
Step 1 - Grab the entire database (one time only)
By first retrieving the entire set of records from the RETS server, this gives you a starting point to begin doing incremental updates. This process can also be re-run whenever needed in order to correct inconsistencies or to refresh your local copy of the database in case fields have been added to your feed since the original download.
For ease of use (and, potentially, code re-use), we suggest that you use a query similar to the following for your initial pull:
(dtlastmod=1950-01-01T00:00:00+)(Note: The plus sign above will need to be encoded as %2B if it’s being entered directly into a URL. This encoding is usually only needed when accessing a URL directly as most RETS tools will handle the encoding of characters for you.)
Since dtlastmod is the listing modification datetime field, this instructs the RETS server to return all records that have been modified since 01/01/1950. For an example of how to run a search, see Preforming a query mentioned in the How to make a RETS request tutorial.
Once the resulting data is processed, you should have your starting point for doing incremental updates.
Step 2 - Run incremental updates to find changes to records
Rather than downloading the entire record set, an incremental update process can be run (and, if a query is used such as the one in Step 1, the same process can likely be used with a different query value).
By running an incremental update, the goal is to have the RETS server only return records that have been modified since the specified time. This allows you to process much fewer records compared to downloading and process a full data file each time which reduces system usage for both you and the RETS server.
Similar to the query used in Step 1, the incremental query would be something like:
(dtlastmod=2016-12-22T07:00:00+)Since dtlastmod is the listing modification datetime field, this instructs the RETS server to return only records that have been modified since the given time.
Step 3 - Run a cleaning process to purge out “old” records
Often times, your RETS access will not have access to retrieve all available records. For example, you may only be able to view status active records. Running incremental updates as described in Step 2 will only return records that 1) have changed since the given time, and 2) you’re able to see. If, for example, a property was changed from status active to status pending, the modification timestamp is updated, but, since you’re not allowed to see records of that status, you won’t see that record in your incremental downloads (and don’t know to remove it from your local database).
In order to make sure your local database reflects accurate information (including updates to records you can no longer see), a cleaning process is needed to purge out those “old” records. Rather than trying to gain access to something telling you about the listings you can no longer see, the reverse process will tell you what you need to know using the access you already have: the RETS server tells you about properties you SHOULD have, so if you have a record that’s not in that list from the RETS server, you know it needs to be deleted from your local database.
An efficient way to find out which records you should have involves querying the RETS server every 24 hours or so for all available records while also instructing the RETS server to only return back the IDs of those records rather than all data associated with those records. For example, when trying to clean property records, you can tell the RETS server to only return back the listno value (MLS #) for each property. For an example of how to limit your returned search data, see Preforming a query mentioned in the How to make a RETS request tutorial.
With this list now obtained, you can compare those record IDs with the IDs in your own database. If you have a record in your database that’s NOT included in that list just obtained (likely caused by the status changing), you know it needs to be deleted from your local database.
Using this method means that you don’t need to have access to all statuses or a separate information source that tells you about data changes since this relies entirely on the access you already have.