public OsmFeature FindBestDistanceMatch([NotNull][ItemNotNull] IReadOnlyCollection <WgsPoint> pointsToLookFor,
[NotNull] out WgsPoint closestPoint,
out double distance)
{
if (pointsToLookFor.Count == 0)
{
throw new Exception("Not a single point to look for");
}
var relevantTiles = GetRelevantTiles(pointsToLookFor, NeighbourTileMode.IncludeBorderingTiles);
var distancesToEachObject = new List <OsmFeatureToPointDistance>();
foreach (var relevantTile in relevantTiles)
{
distancesToEachObject.AddRange(relevantTile.GetDistances(pointsToLookFor));
}
if (distancesToEachObject.Count == 0)
{
throw new Exception("Not a single distance was found");
}
distancesToEachObject.Sort((x, y) => x.Distance.CompareTo(y.Distance));
closestPoint = distancesToEachObject[0].WgsPoint;
distance = distancesToEachObject[0].Distance;
return(distancesToEachObject[0].Feature);
}
protected override void RunActualProcess()
{
SqlConnection.RecreateTable <OsmFeature>(Stage.Raw, Constants.PresentSlice);
var db = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
const string jsonfile = "U:\\SimZukunft\\RawDataForMerging\\roadsBurgdorf.geojson";
var json = File.ReadAllText(jsonfile);
var collection1 = JsonConvert.DeserializeObject <FeatureCollection>(json);
const string jsonfileBuildings = "U:\\SimZukunft\\RawDataForMerging\\buildingsBurgdorf.geojson";
var jsonBuildings = File.ReadAllText(jsonfileBuildings);
var collection2 = JsonConvert.DeserializeObject <FeatureCollection>(jsonBuildings);
collection1.Features.AddRange(collection2.Features);
db.Database.BeginTransaction();
foreach (var feature in collection1.Features)
{
var osmf = new OsmFeature(feature, Guid.NewGuid().ToString());
if (feature.Geometry.Type == GeoJSONObjectType.Polygon)
{
var p = (Polygon)feature.Geometry;
var ls = p.Coordinates[0];
foreach (var coordinate in ls.Coordinates)
{
var wp = new WgsPoint(coordinate.Longitude, coordinate.Latitude);
osmf.WgsPoints.Add(wp);
}
}
db.Database.Save(osmf);
}
db.Database.CompleteTransaction();
}
public bool IsInside([NotNull] WgsPoint point)
{
if (point.Lon <= TopLon && point.Lon >= BottomLon && point.Lat >= LeftLat && point.Lat <= RightLat)
{
return(true);
}
return(false);
}
public bool IsInside([NotNull] WgsPoint point)
{
if (point.Lon < Top && point.Lon > Bottom && point.Lat > Left && point.Lat < Right)
{
return(true);
}
return(false);
}
protected override void RunChartMaking()
{
//var dbRaw = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice).Database;
var dbHouse = SqlConnection.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice).Database;
//var isnLocations = dbRaw.Fetch<HausanschlussImport>();
var houses = dbHouse.Fetch <House>();
var houseanschlusses = dbHouse.Fetch <Hausanschluss>();
ExportAsExcel(houseanschlusses, houses);
RGB HouseFunc(House h)
{
var ha = houseanschlusses.FirstOrDefault(x => x.HouseGuid == h.HouseGuid);
if (ha?.MatchingType == HouseMatchingType.Direct)
{
return(Constants.Blue);
}
if (ha?.MatchingType == HouseMatchingType.Proximity)
{
return(Constants.Green);
}
return(Constants.Orange);
}
var houseEntries = houses.Select(x => x.GetMapColorForHouse(HouseFunc)).ToList();
var filename = MakeAndRegisterFullFilename("DirektZugeordnete.png", Name, "", Constants.PresentSlice);
var labels = new List <MapLegendEntry> {
new MapLegendEntry("Direkt Gemappt", Constants.Blue),
new MapLegendEntry("Näherung", Constants.Green),
new MapLegendEntry("Kein Hausobjekt hinterlegt", Constants.Orange),
new MapLegendEntry("Nicht Teil von Burgdorf", Constants.Red)
};
var lines = new List <LineEntry>();
foreach (var hausanschluss in houseanschlusses)
{
if (hausanschluss.MatchingType == HouseMatchingType.Direct)
{
continue;
}
var house = houses.Single(x => x.HouseGuid == hausanschluss.HouseGuid);
foreach (WgsPoint coord in house.WgsGwrCoords)
{
WgsPoint isn = new WgsPoint(hausanschluss.Lon, hausanschluss.Lat);
//house.ComplexName + " - " + hausanschluss.ObjectID
lines.Add(new LineEntry(coord, isn, ""));
}
}
Services.PlotMaker.MakeOsmMap(Name, filename, houseEntries, new List <WgsPoint>(), labels, lines);
}
public bool IsOutside([NotNull] WgsPoint point)
{
if (point.Lon > Top || point.Lon < Bottom)
{
return(true);
}
if (point.Lat < Left || point.Lat > Right)
{
return(true);
}
return(false);
}
private static void DoProximityAssignments([NotNull][ItemNotNull] List <House> houses, [NotNull][ItemNotNull] List <House> assignedHouses, [NotNull][ItemNotNull] List <HausanschlussImport> isnLocations,
[NotNull][ItemNotNull] List <Hausanschluss> hausanschlusses)
{
foreach (var assignedHouse in assignedHouses)
{
//check if every assigned house is actually assigned.
var r = hausanschlusses.FirstOrDefault(x => x.HouseGuid == assignedHouse.HouseGuid);
if (r == null)
{
throw new FlaException("Assigned house is not actually assigned.");
}
}
foreach (var house in houses)
{
if (!assignedHouses.Contains(house))
{
var locations = new List <Tuple <double, HausanschlussImport> >();
var houseWgsPoints = new List <WgsPoint>();
houseWgsPoints.AddRange(house.LocalWgsPoints);
if (houseWgsPoints.Count == 0)
{
houseWgsPoints.AddRange(house.WgsGwrCoords);
}
if (houseWgsPoints.Count > 0)
{
foreach (var isnLocation in isnLocations)
{
var wgsp = new WgsPoint(isnLocation.Lon, isnLocation.Lat);
var distance = wgsp.GetMinimumDistance(houseWgsPoints);
locations.Add(new Tuple <double, HausanschlussImport>(distance, isnLocation));
}
locations.Sort((x, y) => x.Item1.CompareTo(y.Item1));
var pickedHa = locations[0].Item2;
//if (locations[0].Item1 > 200) {
//throw new Exception("More than 200 m");
//}
hausanschlusses.Add(new Hausanschluss(Guid.NewGuid().ToString(), house.HouseGuid,
pickedHa.ObjectID, pickedHa.Egid, pickedHa.Isn, pickedHa.Lon, pickedHa.Lat,
pickedHa.Trafokreis, HouseMatchingType.Proximity, locations[0].Item1, locations[0].Item2.Adress));
}
}
}
}
private static double FindDistanceToSegment([NotNull] WgsPoint pt, [NotNull] WgsPoint p1,
[NotNull] WgsPoint p2) //, out PointF closest
{
var dx = p2.Lon - p1.Lon;
var dy = p2.Lat - p1.Lat;
if (dx == 0 && dy == 0)
{
// It's a point not a line segment.
dx = pt.Lon - p1.Lon;
dy = pt.Lat - p1.Lat;
return(Math.Sqrt(dx * dx + dy * dy));
}
// Calculate the t that minimizes the distance.
var t = ((pt.Lon - p1.Lon) * dx + (pt.Lat - p1.Lat) * dy) / (dx * dx + dy * dy);
// See if this represents one of the segment's
// end points or a point in the middle.
if (t < 0)
{
dx = pt.Lon - p1.Lon;
dy = pt.Lat - p1.Lat;
}
else if (t > 1)
{
dx = pt.Lon - p2.Lon;
dy = pt.Lat - p2.Lat;
}
else
{
var closest = new WgsPoint(p1.Lon + t * dx, p1.Lat + t * dy);
dx = pt.Lon - closest.Lon;
dy = pt.Lat - closest.Lat;
}
return(Math.Sqrt(dx * dx + dy * dy));
}
private static bool IsPointInPolygon([NotNull][ItemNotNull] List <WgsPoint> polygon, [NotNull] WgsPoint testPoint)
{
var result = false;
var j = polygon.Count - 1;
for (var i = 0; i < polygon.Count; i++)
{
if (polygon[i].Lat < testPoint.Lat && polygon[j].Lat >= testPoint.Lat || polygon[j].Lat < testPoint.Lat && polygon[i].Lat >= testPoint.Lat)
{
if (polygon[i].Lon + (testPoint.Lat - polygon[i].Lat) / (polygon[j].Lat - polygon[i].Lat) * (polygon[j].Lon - polygon[i].Lon) < testPoint.Lon)
{
result = !result;
}
}
j = i;
}
return(result);
}
public OsmFeatureToPointDistance([NotNull] WgsPoint wgsPoint, [NotNull] OsmFeature feature)
{
WgsPoint = wgsPoint;
Feature = feature;
Distance = CalcDistance();
}
protected override void RunActualProcess()
{
SqlConnection.RecreateTable <House>(Stage.Houses, Constants.PresentSlice);
SqlConnection.RecreateTable <PotentialHousehold>(Stage.Houses, Constants.PresentSlice);
var dbComplex = SqlConnection.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice).Database;
//var dbRaw = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice).Database;
var buildingcomplexes = dbComplex.Fetch <BuildingComplex>();
var dbComplexEnergy = SqlConnection.GetDatabaseConnection(Stage.ComplexEnergyData, Constants.PresentSlice).Database;
var complexdata = dbComplexEnergy.Fetch <ComplexBuildingData>();
var dbHouse = SqlConnection.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice).Database;
//var sonnendach = dbRaw.Fetch<SonnenDach>();
dbHouse.BeginTransaction();
List <string> complexesToIgnore = new List <string> {
"EGID191357110", "EGID1306724", "EGID1305755", "Fischermätteliweg nn"
};
//complexesToIgnore.Add("Bernstrasse 113a");
//complexesToIgnore.Add("Finkhubelweg 8");
//complexesToIgnore.Add("Friedhof 4");
var manualCoordinates = ReadManualCoordinatesList();
foreach (var complex in buildingcomplexes)
{
if (complexesToIgnore.Contains(complex.ComplexName))
{
continue;
}
//haus anlegen
var h = new House(complex.ComplexName,
complex.ComplexGuid,
Guid.NewGuid().ToString()
)
{
ComplexID = complex.ComplexID,
};
// ReSharper disable once ConditionIsAlwaysTrueOrFalse
// ReSharper disable once HeuristicUnreachableCode
if (h.HouseGuid == null)
{
// ReSharper disable once HeuristicUnreachableCode
throw new Exception("House guid was null");
}
// ReSharper disable once ConditionIsAlwaysTrueOrFalse
// ReSharper disable once HeuristicUnreachableCode
if (h.ComplexGuid == null)
{
// ReSharper disable once HeuristicUnreachableCode
throw new Exception("Complex guid was null");
}
//gebäudeobjektids zuordnen
foreach (var complexGebäudeObjectID in complex.GebäudeObjectIDs)
{
h.GebäudeObjectIDs.Add(complexGebäudeObjectID);
}
// egids zuordnen
foreach (var eGid in complex.EGids)
{
h.EGIDs.Add((int)eGid);
}
//geo coords zuordnen
foreach (var coord in complex.Coords)
{
h.WgsGwrCoords.Add(WgsPoint.ConvertKoordsToLonLat(coord.X, coord.Y));
}
var manualCoord = manualCoordinates.Where(x => x.Name == h.ComplexName).ToList();
if (manualCoord.Count() > 0)
{
foreach (var manualComplexCoordinatese in manualCoord)
{
h.WgsGwrCoords.Add(new WgsPoint(manualComplexCoordinatese.Lon, manualComplexCoordinatese.Lat));
}
}
foreach (var coord in complex.LocalnetCoords)
{
h.LocalWgsPoints.Add(WgsPoint.ConvertKoordsToLonLat(coord.X, coord.Y));
}
//addressen zuordnen
if (complex.Adresses.Count == 0)
{
h.Adress = "Unknown";
}
else
{
h.Adress = complex.Adresses[0];
}
if (complex.TrafoKreise.Count > 0)
{
foreach (var erzId in complex.ErzeugerIDs)
{
h.ErzeugerIDs.Add(erzId);
}
}
//assign household data
var thiscomplexdata = complexdata.FirstOrDefault(x => x.ComplexName == complex.ComplexName);
if (thiscomplexdata == null)
{
h.NumberOfHouseholds = 0;
h.Area = 0;
h.EnergieBezugsFläche = 0;
}
else
{
h.NumberOfHouseholds = thiscomplexdata.AnzahlWohnungenBern;
h.Area += thiscomplexdata.TotalArea;
h.EnergieBezugsFläche += thiscomplexdata.TotalEnergieBezugsfläche;
h.AverageBuildingAge = thiscomplexdata.BuildingAges.Average();
}
dbHouse.Save(h);
}
dbHouse.CompleteTransaction();
}
public LineEntry([NotNull] WgsPoint startPoint, [NotNull] WgsPoint endPoint, [NotNull] string text)
{
StartPoint = startPoint;
EndPoint = endPoint;
Text = text;
}
protected override void RunActualProcess()
{
var dbComplex = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Complexes, Constants.PresentSlice);
//var dbRaw = SqlConnection.GetDatabaseConnection(Stage.Raw, Constants.PresentSlice);
var buildingcomplexes = dbComplex.Fetch <BuildingComplex>();
var dbComplexEnergy = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.ComplexEnergyData, Constants.PresentSlice);
var complexdata = dbComplexEnergy.Fetch <ComplexBuildingData>();
var dbHouse = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
dbHouse.RecreateTable <House>();
dbHouse.RecreateTable <PotentialHousehold>();
//var sonnendach = dbRaw.Fetch<SonnenDach>();
dbHouse.BeginTransaction();
List <string> complexesToIgnore = new List <string> {
"EGID191357110", "EGID1306724", "EGID1305755", "Fischermätteliweg nn"
};
//complexesToIgnore.Add("Bernstrasse 113a");
//complexesToIgnore.Add("Finkhubelweg 8");
//complexesToIgnore.Add("Friedhof 4");
var manualCoordinates = ReadManualCoordinatesList();
foreach (var complex in buildingcomplexes)
{
if (complexesToIgnore.Contains(complex.ComplexName))
{
continue;
}
//haus anlegen
var h = new House(complex.ComplexName,
complex.ComplexGuid,
Guid.NewGuid().ToString()
)
{
ComplexID = complex.ComplexID,
};
// ReSharper disable once ConditionIsAlwaysTrueOrFalse
// ReSharper disable once HeuristicUnreachableCode
if (h.Guid == null)
{
// ReSharper disable once HeuristicUnreachableCode
throw new Exception("House guid was null");
}
// ReSharper disable once ConditionIsAlwaysTrueOrFalse
// ReSharper disable once HeuristicUnreachableCode
if (h.ComplexGuid == null)
{
// ReSharper disable once HeuristicUnreachableCode
throw new Exception("Complex guid was null");
}
//gebäudeobjektids zuordnen
foreach (var complexGebäudeObjectID in complex.GebäudeObjectIDs)
{
h.GebäudeObjectIDs.Add(complexGebäudeObjectID);
}
// egids zuordnen
foreach (var eGid in complex.EGids)
{
h.EGIDs.Add((int)eGid);
}
//geo coords zuordnen
foreach (var coord in complex.Coords)
{
h.WgsGwrCoords.Add(WgsPoint.ConvertKoordsToLonLat(coord.X, coord.Y));
}
var manualCoord = manualCoordinates.Where(x => x.Name == h.ComplexName).ToList();
if (manualCoord.Any())
{
foreach (var manualComplexCoordinatese in manualCoord)
{
h.WgsGwrCoords.Add(new WgsPoint(manualComplexCoordinatese.Lon, manualComplexCoordinatese.Lat));
}
}
foreach (var coord in complex.LocalnetCoords)
{
h.LocalWgsPoints.Add(WgsPoint.ConvertKoordsToLonLat(coord.X, coord.Y));
}
//addressen zuordnen
if (complex.Adresses.Count == 0)
{
h.Adress = "Unknown";
}
else
{
h.Adress = complex.Adresses[0];
}
if (complex.TrafoKreise.Count > 0)
{
foreach (var erzId in complex.ErzeugerIDs)
{
h.ErzeugerIDs.Add(erzId);
}
}
//assign household data
var thiscomplexdata = complexdata.FirstOrDefault(x => x.ComplexName == complex.ComplexName);
if (thiscomplexdata == null)
{
h.Area = 0;
h.Appartments.Add(new AppartmentEntry(Guid.NewGuid().ToString(), 0, false, Constants.PresentSlice.DstYear));
}
else
{
if (thiscomplexdata.AnzahlWohnungenBern > 0)
{
double avgArea = thiscomplexdata.TotalEnergieBezugsfläche / thiscomplexdata.AnzahlWohnungenBern;
for (int i = 0; i < thiscomplexdata.AnzahlWohnungenBern; i++)
{
h.Appartments.Add(new AppartmentEntry(Guid.NewGuid().ToString(), avgArea, true, Constants.PresentSlice.DstYear));
}
}
else
{
h.Appartments.Add(new AppartmentEntry(Guid.NewGuid().ToString(), thiscomplexdata.TotalEnergieBezugsfläche, false, Constants.PresentSlice.DstYear));
}
h.Area += thiscomplexdata.TotalArea;
h.AverageBuildingAge = thiscomplexdata.BuildingAges.Average();
}
dbHouse.Save(h);
}
dbHouse.CompleteTransaction();
var allHouses = dbHouse.FetchAsRepo <House>();
RowCollection rc = new RowCollection("houses", "houses");
foreach (var house in allHouses)
{
RowBuilder rb = RowBuilder.Start("age", house.AverageBuildingAge);
rc.Add(rb);
}
var fn = MakeAndRegisterFullFilename("HouseAges.xlsx", Constants.PresentSlice);
XlsxDumper.WriteToXlsx(fn, rc);
}
protected override void RunChartMaking()
{
var dbHouse = Services.SqlConnectionPreparer.GetDatabaseConnection(Stage.Houses, Constants.PresentSlice);
var houses = dbHouse.Fetch <House>();
var housesWithoutAnschluss = houses.Where(x => x.Hausanschluss.Count == 0).ToList();
if (housesWithoutAnschluss.Count > 0)
{
throw new FlaException("Houses without anschluss");
}
var houseanschlusses = dbHouse.Fetch <Hausanschluss>();
ExportAsExcel(houseanschlusses, houses);
RGB HouseFunc(House h)
{
var ha = houseanschlusses.FirstOrDefault(x => x.HouseGuid == h.Guid);
switch (ha?.MatchingType)
{
case HouseMatchingType.DirectByEgid:
case HouseMatchingType.DirectByAdress:
case HouseMatchingType.DirectByIsn:
case HouseMatchingType.DirectByIsnSupplemental:
case HouseMatchingType.DirectByAdressSupplemental:
return(Constants.Blue);
case HouseMatchingType.Proximity:
return(Constants.Green);
case HouseMatchingType.Leftover:
return(Constants.Orange);
default:
return(Constants.Orange);
}
}
var houseEntries = houses.Select(x => x.GetMapColorForHouse(HouseFunc)).ToList();
var filename = MakeAndRegisterFullFilename("DirektZugeordnete.png", Constants.PresentSlice);
var labels = new List <MapLegendEntry> {
new MapLegendEntry("Direkt Gemappt", Constants.Blue),
new MapLegendEntry("Näherung", Constants.Green),
new MapLegendEntry("Kein Hausobjekt hinterlegt", Constants.Orange),
new MapLegendEntry("Nicht Teil von Burgdorf", Constants.Red)
};
var lines = new List <LineEntry>();
foreach (var hausanschluss in houseanschlusses)
{
if (hausanschluss.MatchingType == HouseMatchingType.DirectByEgid || hausanschluss.MatchingType == HouseMatchingType.DirectByIsn ||
hausanschluss.MatchingType == HouseMatchingType.DirectByAdress)
{
continue;
}
var house = houses.Single(x => x.Guid == hausanschluss.HouseGuid);
foreach (WgsPoint coord in house.WgsGwrCoords)
{
WgsPoint isn = new WgsPoint(hausanschluss.Lon, hausanschluss.Lat);
//house.ComplexName + " - " + hausanschluss.ObjectID
lines.Add(new LineEntry(coord, isn, ""));
}
}
Services.PlotMaker.MakeOsmMap(Name, filename, houseEntries, new List <WgsPoint>(), labels, lines);
}
private void DoProximityAssignments([NotNull][ItemNotNull] IReadOnlyCollection <House> houses,
[NotNull][ItemNotNull] List <House> assignedHouses,
[NotNull][ItemNotNull] List <HausanschlussImport> allIsnLocations,
[NotNull][ItemNotNull] List <Hausanschluss> hausanschlusses)
{
var isnLocations = allIsnLocations.Where(x => !x.ObjectID.ToLower().Contains("leuchte")).ToList();
foreach (var isnLocation in isnLocations)
{
if (isnLocation.ObjectID.ToLower().Contains("leuchte"))
{
throw new FlaException("Leuchte gefunden");
}
}
foreach (var assignedHouse in assignedHouses)
{
//check if every assigned house is actually assigned.
var r = hausanschlusses.FirstOrDefault(x => x.HouseGuid == assignedHouse.Guid);
if (r == null)
{
throw new FlaException("Assigned house is not actually assigned.");
}
}
//warning: multiple houses can share a hausanschluss
houses = houses.Distinct().ToList();
foreach (var house in houses)
{
if (assignedHouses.Contains(house))
{
continue;
}
var locations = new List <Tuple <double, HausanschlussImport> >();
var houseWgsPoints = new List <WgsPoint>();
houseWgsPoints.AddRange(house.LocalWgsPoints);
if (houseWgsPoints.Count == 0)
{
houseWgsPoints.AddRange(house.WgsGwrCoords);
}
if (houseWgsPoints.Count > 0)
{
foreach (var isnLocation in isnLocations)
{
var wgsp = new WgsPoint(isnLocation.Lon, isnLocation.Lat);
var distance = wgsp.GetMinimumDistance(houseWgsPoints);
locations.Add(new Tuple <double, HausanschlussImport>(distance, isnLocation));
}
locations.Sort((x, y) => x.Item1.CompareTo(y.Item1));
var pickedImported = locations[0].Item2;
//if (locations[0].Item1 > 200) {
//throw new Exception("More than 200 m");
//}
hausanschlusses.Add(new Hausanschluss(Guid.NewGuid().ToString(),
house.Guid,
pickedImported.ObjectID,
pickedImported.Egid,
pickedImported.Isn,
pickedImported.Lon,
pickedImported.Lat,
pickedImported.Trafokreis,
HouseMatchingType.Proximity,
locations[0].Item1,
locations[0].Item2.Adress,
pickedImported.Standort));
}
}
var allAssignedHa = hausanschlusses.Select(y => y.ObjectID);
var allUnassignedHa = allIsnLocations.Where(x => !allAssignedHa.Contains(x.ObjectID)).ToList();
foreach (var import in allUnassignedHa)
{
var houseDistances = new List <Tuple <double, House> >();
var hawgs = new WgsPoint(import.Lon, import.Lat);
foreach (var house in houses)
{
var houseCoords = house.WgsGwrCoords.ToList();
houseCoords.AddRange(house.LocalWgsPoints);
var distance = hawgs.GetMinimumDistance(houseCoords);
houseDistances.Add(new Tuple <double, House>(distance, house));
}
houseDistances.Sort((x, y) => x.Item1.CompareTo(y.Item1));
var pickedHouse = houseDistances[0].Item2;
hausanschlusses.Add(new Hausanschluss(Guid.NewGuid().ToString(),
pickedHouse.Guid,
import.ObjectID,
import.Egid,
import.Isn,
import.Lon,
import.Lat,
import.Trafokreis,
HouseMatchingType.Leftover,
houseDistances[0].Item1,
import.Adress,
import.Standort));
}
Info("Number of unassigned hausanschlüsse:" + allUnassignedHa.Count);
}
