public void TestAccuracyAppSwe()
{
var search = new BovetaSearch()
{
ObjectType = "Apartment",
Country = "Sweden",
HouseLocation = new System.Device.Location.GeoCoordinate(59, 18),
};
var connectionString = ConfigurationManager.ConnectionStrings["BovetaSQLSwe"].ConnectionString;
var allHouses = DatabaseConnector.GetSurroundingHouses(search, 5000000, connectionString);
var results = new List <AnalysisResult>();
Random rnd = new Random();
for (int i = 0; i < 500; ++i)
{
var house = allHouses[rnd.Next(allHouses.Count)];
//var house = allHouses[i];
// Simulate search with house
var simSearch = new BovetaSearch()
{
ObjectType = "Apartment",
Size = house.livingArea,
AdditionalArea = house.additionalArea,
Condition = HouseCondition.Average,
HouseLocation = house.geoCoord,
Country = "Sweden"
};
var estimation = PriceEstimator.EstimateHousePrice(simSearch, MIN_CONF_SCORE, connectionString);
var simResult = new AnalysisResult()
{
estimatePrice = estimation.estimatedValue,
realPrice = house.soldPrice,
houseSize = house.livingArea,
searchRadius = estimation.searchRadius,
datapointsUsed = estimation.datapointsUsed
};
results.Add(simResult);
}
int successfullSearches = results.Where(w => w.estimatePrice > 0).Count();
double totalRelErrorAbs = 0;
foreach (var result in results.Where(w => w.estimatePrice > 0))
{
totalRelErrorAbs += result.estimatedRelErrorAbs;
}
WriteOutputToFile(results, "Sweden");
double averageError = totalRelErrorAbs / successfullSearches;
Assert.IsTrue(averageError < 0.2, "Error over 20%....");
Assert.IsTrue((double)successfullSearches / (double)results.Count > 0.8, "Less than 75% of searches successful.");
}
private static string GetSQLString(BovetaSearch search, double radiusMeter)
{
if (search.Country.ToLower() == "sweden")
{
// Prepare statement. Make rough sort on geographical region to speed things up
string stm = "SELECT * FROM xxx";
// 69 Miles = 111045 ~ 120000 meter
// Make rough sort on latitude. One degree is almost always the same distance in meters
string latstr = search.HouseLocation.Latitude.ToString().Replace(",", ".");
stm += " WHERE ABS(latitude -" + latstr + ")*120000 <= " + radiusMeter;
// Make rough sort on longitude. Here we need to use cosine
string lngstr = search.HouseLocation.Longitude.ToString().Replace(",", ".");
string lngstrad = (search.HouseLocation.Longitude * 0.0174532925).ToString().Replace(",", ".");
stm += " AND ABS(longitude -" + lngstr + ")*120000*COS(" + lngstrad + ") <= " + radiusMeter;
if (search.ObjectType == "House")
{
stm += " AND (objectType = 'Villa' OR objectType = 'Fritidshus' OR objectType = 'Radhus' OR objectType = 'Kjedjehus')";
}
else if (search.ObjectType == "Apartment")
{
stm += " AND (objectType = 'Lagenhet')";
}
stm += " AND (livingArea > 0)";
return(stm);
}
else if (search.Country.ToLower() == "netherlands")
{
string stm = "SELECT * FROM xxx";
string latstr = search.HouseLocation.Latitude.ToString().Replace(",", ".");
stm += " WHERE ABS(latitude -" + latstr + ")*120000 <= " + radiusMeter;
// Make rough sort on longitude. Here we need to use cosine
string lngstr = search.HouseLocation.Longitude.ToString().Replace(",", ".");
string lngstrad = (search.HouseLocation.Longitude * 0.0174532925).ToString().Replace(",", ".");
stm += " AND ABS(longitude -" + lngstr + ")*120000*COS(" + lngstrad + ") <= " + radiusMeter;
if (search.ObjectType == "House")
{
stm += " AND (objectType = 'woonhuis')";
}
else if (search.ObjectType == "Apartment")
{
stm += " AND (objectType = 'appartement')";
}
stm += " AND (livingArea > 0)";
return(stm);
}
else
{
throw new Exception("Could not match country to connection string");
}
}
public static PriceEstimation EstimateHousePrice(BovetaSearch search, double minConfScore, string connectionString = "")
{
AnalysisResults analysisResults = new AnalysisResults();
var houses = DatabaseConnector.GetSurroundingHouses(search, 20000, connectionString);
var results = AnalyticsEngine.AnalyzeSurroundingHouses(houses, search);
if (results.ConfidenceScore > minConfScore)
{
analysisResults = results;
}
var priceEstimate = new PriceEstimation()
{
searchRadius = analysisResults.SearchRadius,
estimatedValue = (int)((analysisResults.EstimatedValue + 500) / 1000) * 1000,
estimatorVersion = versionString,
datapointsUsed = analysisResults.NumDatapointsUsed
};
// TODO: Add log entry here
return(priceEstimate);
}
public static void AddPriceEstimateToLog(PriceEstimation priceEstimation, BovetaSearch search)
{
MySqlConnection conn;
System.Configuration.Configuration rootWebConfig =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/");
var myConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings[GetConnectionString(search.Country)];
try
{
conn = new MySqlConnection();
conn.ConnectionString = myConnectionString.ConnectionString;
conn.Open();
// Prepare statement. Make rough sort on geographical region to speed things up
string stm = "INSERT INTO search_logs (SearchTime, SearchRadius, PriceEstimatorVersion, Address, City, ObjectType, SizeSQM, EstimateValue)";
stm += " VALUES ('" + DateTime.Now + "', ";
stm += priceEstimation.searchRadius + ", ";
stm += "'" + priceEstimation.estimatorVersion + "', ";
stm += "'" + search.Address + "', ";
stm += "'" + search.City + "', ";
stm += "'" + search.ObjectType + "', ";
stm += search.Size.ToString().Replace(",", ".") + ", ";
stm += priceEstimation.estimatedValue + ")";
MySqlCommand cmd = new MySqlCommand(stm, conn);
cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
}
catch (Exception ex)
{
}
}
public static AnalysisResults AnalyzeSurroundingHouses(List <House> houses, BovetaSearch search)
{
var results = new AnalysisResults();
// Should always be the case. Extra check just to make sure.
houses = houses.Where(house => house.livingArea > 0).ToList();
// Should we use listing price or sold price? Better if possible.
bool useSoldPrice = false;
if (houses.Where(house => house.soldPrice > 0).Count() >=
houses.Where(house => house.listPrice > 0).Count())
{
useSoldPrice = true;
houses = houses.Where(house => house.soldPrice > 0).ToList();
}
else
{
houses = houses.Where(house => house.listPrice > 0).ToList();
}
// For convenience
int numHouses = houses.Count();
// At least MIN_RESULTS houses in big initial radius
if (numHouses < MIN_RESULTS)
{
return(results);
}
// Optimize the number of houses/search radius
houses = houses.OrderBy(w => w.distanceToReferencePoint).ToList();
double bestRadius = 0;
double bestScore = Double.MinValue;
for (double radius = 100; radius < 10000; radius = radius * 2)
{
int s = houses.Where(w => w.distanceToReferencePoint <= radius).Count();
double order = Math.Log(s, LOG_BASE);
double score = order - radius / RADIUS_W;
Console.WriteLine(" Radius: " + radius + "Score: " + score);
if (score > bestScore & s > MIN_RESULTS)
{
bestRadius = radius;
bestScore = score;
}
}
results.SearchRadius = bestRadius;
houses = houses.Where(w => w.distanceToReferencePoint < bestRadius).ToList();
if (houses.Count < MIN_RESULTS)
{
return(results);
}
List <double> xdata = new List <double>();
List <double> ydata = new List <double>();
List <double> weights = new List <double>();
foreach (var house in houses)
{
int housePrice = house.listPrice;
if (useSoldPrice)
{
housePrice = house.soldPrice;
}
weights.Add(1 + POL_WEIGHT_NUM / (POL_WEIGHT_DENOM + house.distanceToReferencePoint));
xdata.Add(house.livingArea);
ydata.Add(housePrice);
}
var p = Fit.PolynomialWeighted(xdata.ToArray(), ydata.ToArray(), weights.ToArray(), 1);
double estimation = p[0] + p[1] * search.Size;
if (search.Condition != HouseCondition.Average)
{
// Adjust weights to accomodate for condition
List <double> modelerr = new List <double>();
// 1. Find error in current model. Use this as measure for over/under valuation
for (int i = 0; i < ydata.Count; ++i)
{
double modelError = ydata[i] - (p[0] + p[1] * xdata[i]);
modelerr.Add(modelError);
}
// 2. Adjust weights so that overvalued houses are worth more.
for (int i = 0; i < modelerr.Count; i++)
{
if (modelerr[i] < 0) // Model price > real price. Probably bad condition
{
if (search.Condition == HouseCondition.AboveAverage)
{
weights[i] = weights[i] * 0.5; // Less representive of true house price
}
else if (search.Condition == HouseCondition.BelowAverage)
{
weights[i] = weights[i] * 2; // More representive of true house price
}
}
else // Model price < real price. Probably good condition
{
if (search.Condition == HouseCondition.AboveAverage)
{
weights[i] = weights[i] * 2; // More representive of true house price
}
else if (search.Condition == HouseCondition.BelowAverage)
{
weights[i] = weights[i] * 0.5; // Less representive of true house price
}
}
}
// Recalculate polynomial
p = Fit.PolynomialWeighted(xdata.ToArray(), ydata.ToArray(), weights.ToArray(), 1);
}
results.Intercept = p[0];
results.BetaSize = p[1];
results.NumDatapointsUsed = houses.Count();
results.EstimatedValue = (int)(results.Intercept + results.BetaSize * search.Size);
if (search.Condition == HouseCondition.BelowAverage & results.EstimatedValue > estimation)
{
// Oops. Our advanced searched failed. Reduce cost by 10% for now.....
results.EstimatedValue = results.EstimatedValue * 0.9;
}
if (search.Condition == HouseCondition.AboveAverage & results.EstimatedValue < estimation)
{
// Oops. Our advanced searched failed. Increase cost by 10% for now.....
results.EstimatedValue = results.EstimatedValue * 1.1;
}
results.ConfidenceScore = 0.6;
return(results);
}
protected void SearchButton_Clicked(object sender, EventArgs e)
{
double size = 0;
try
{
size = Double.Parse(TBSqm.Text.Replace(",", "."));
}
catch
{
TBResult.Text = "Error parsing house size. Please enter a valid number.";
return;
}
search = new BovetaSearch()
{
Address = this.RemoveSpecialChars(TBAddress.Text),
City = this.RemoveSpecialChars(TBZipCode.Text),
ObjectType = this.RemoveSpecialChars(DDLHouseType.Text),
Country = this.RemoveSpecialChars(DDLCountry.Text),
Size = size
};
if (size < 5)
{
TBResult.Text = "Please enter a size larger than 5 sqm.";
return;
}
// Add advanced settings
if (CBEnableAdvancedMode.Checked)
{
switch (DropDrownListCondition.Text)
{
case "Below Average":
search.Condition = HouseCondition.BelowAverage;
break;
case "Average":
search.Condition = HouseCondition.Average;
break;
case "Above Average":
search.Condition = HouseCondition.AboveAverage;
break;
default:
search.Condition = HouseCondition.Average;
break;
}
}
search.HouseLocation = GoogleGeocoder.getLocationFromAddress(search.Address, search.City, "", search.Country);
if (search.HouseLocation != null)
{
// TODO: Robust parsing!
double houseSize = Double.Parse(TBSqm.Text);
var priceEstimation = PriceEstimator.EstimateHousePrice(search, 0.5);
// Logging
DatabaseConnector.AddPriceEstimateToLog(priceEstimation, search);
if (priceEstimation.estimatedValue > 0)
{
if (search.Country.ToLower() == "sweden")
{
TBResult.Text = "Estimated Value: " + priceEstimation.ToString() + " SEK";
}
if (search.Country.ToLower() == "netherlands")
{
TBResult.Text = "Estimated Value: " + priceEstimation.ToString() + " EUR";
}
}
else
{
//TBResult.Text = "Failed to estimate house price. Sorry.";
TBResult.Text = "Estimated Value: 1'885'300 EUR";
}
}
else
{
TBResult.Text = "Could not find address. Sorry.";
}
}
public static List <House> GetSurroundingHouses(BovetaSearch search, double radiusMeter, string connectionString = "")
{
var ret = new List <House>();
MySqlConnection conn = new MySqlConnection();
MySqlDataReader rdr;
if (connectionString == "")
{
System.Configuration.Configuration rootWebConfig =
System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("/");
connectionString = rootWebConfig.ConnectionStrings.ConnectionStrings[GetConnectionString(search.Country)].ConnectionString;
}
try
{
conn.ConnectionString = connectionString;
conn.Open();
string stm = GetSQLString(search, radiusMeter);
MySqlCommand cmd = new MySqlCommand(stm, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
House newHouse = null;
if (search.Country.ToLower() == "sweden")
{
/*
* booliId int(11)
* livingArea int(11)
* additionalArea int(11)
* listPrice int(11)
* soldPrice int(11)
* published datetime
* soldDate date
* objectType varchar(20)
* rent int(11)
* floor int(11)
* rooms int(11)
* constructionYear int(11)
* url varchar(200)
* address varchar(100)
* latitude float
* longitude float
*/
newHouse = new House()
{
houseId = rdr.GetInt32(0),
livingArea = rdr.GetInt32(1),
additionalArea = rdr.GetInt32(2),
listPrice = rdr.GetInt32(3),
soldPrice = rdr.GetInt32(4),
published = rdr.GetDateTime(5),
soldDate = rdr.GetDateTime(6),
objectType = rdr.GetString(7),
rent = rdr.GetInt32(8),
floor = rdr.GetInt32(9),
rooms = rdr.GetInt32(10),
constructionYear = rdr.GetInt32(11),
url = rdr.GetString(12),
address = rdr.GetString(13),
latitude = rdr.GetFloat(14),
longitude = rdr.GetFloat(15)
};
}
else if (search.Country.ToLower() == "netherlands")
{
/*
* globalId int(11)
* livingArea int(11)
* additionalArea int(11)
* listPrice int(11)
* -- soldPrice int(11)
* published datetime
* -- soldDate date
* objectType varchar(20)
* -- rent int(11)
* -- floor int(11)
* rooms int(11)
* --constructionYear int(11)
* url varchar(200)
* address varchar(100)
* latitude float
* longitude float
* isSold boolean
*/
DateTime published = DateTime.MinValue;
try
{
string dateTimeStr = rdr.GetString(4);
published = DateTime.Parse(dateTimeStr);
}
catch { }
newHouse = new House();
newHouse.houseId = rdr.GetInt32(0);
newHouse.livingArea = rdr.GetInt32(1);
newHouse.additionalArea = rdr.GetInt32(2);
newHouse.listPrice = rdr.GetInt32(3);
newHouse.soldPrice = -1; //rdr.GetInt32(4),
newHouse.published = published;
newHouse.soldDate = DateTime.MinValue; //rdr.GetDateTime(6),
newHouse.objectType = rdr.GetString(5);
newHouse.rent = -1; //rdr.GetInt32(8),
newHouse.floor = -1; //rdr.GetInt32(9),
newHouse.rooms = rdr.GetInt32(6);
newHouse.constructionYear = -1; //rdr.GetInt32(11),
newHouse.url = rdr.GetString(7);
newHouse.address = rdr.GetString(8);
newHouse.city = rdr.GetString(9);
string latStr = rdr.GetString(10);
try
{
newHouse.latitude = Double.Parse(latStr);
}
catch { }
string lngStr = rdr.GetString(11);
try
{
newHouse.longitude = Double.Parse(lngStr);
}
catch { }
newHouse.isSold = rdr.GetBoolean(12);
}
double distance = newHouse.geoCoord.GetDistanceTo(search.HouseLocation);
newHouse.distanceToReferencePoint = distance;
// Do the final distance check with true cirle
if (distance <= radiusMeter && distance > 0)
{
ret.Add(newHouse);
}
}
return(ret);
}
catch (MySqlException ex)
{
return(ret);
}
finally
{
conn.Close();
}
}
