internal static bool AddCoordinates(CoordinatesItem item) { // Get matched ROAD_LINK_ID var roadLinkIds = GetRoadLinkId(item.StreetZipId); if (roadLinkIds.Count == 0) { Log.WriteLine($"No ROAD_LINK_ID match for street segment {item.SegmentId}"); return(false); } // Get RdfAddr data var nldRdfAddrData = GetNldRdfAddrData(roadLinkIds); if (nldRdfAddrData.Count == 0) { Log.WriteLine($"No RDF_DATA match for {item.SegmentId}"); return(false); } // Sort the data so the segments with lower housenumbers come first. // This is just so we start approx. at tht right side of the street nldRdfAddrData.Sort(RdfAddr.Compare); // For each segment, get the coordinates from the rdf_seg table List <Segment> segments = new List <Segment>(); foreach (var addr in nldRdfAddrData) { segments.Add(GetSegmentsForAddr(addr)); } if (segments.Count == 0) { Log.WriteLine("Segments array is empty!"); return(false); } // List of all coordinates we have so far var coordinates = new List <SegmentCoordinate>(); // Only add coordinates in between segments. // The segments themselves will be connected by the graph algorithm below. // (And in the best case, there should be no holes between them) foreach (var segment in segments) { segment.Coordinates = Segment.AddCoordinatesInBetween(segment.Coordinates); } // Take the first item, and try to build a segment graph from it // Do it, as long as there are items left // This ensures, that we include every segment in the graph List <Segment> segmentList = new List <Segment>(segments); while (segmentList.Count > 0) { // Take the first item (=> the one with the lowest housenumber), // and start from there var startSegment = segmentList[0]; segmentList.RemoveAt(0); // Let the item take its children startSegment.AddChildren(segmentList); var fullSegmentCoordinates = startSegment.GetCoordinates(); coordinates.AddRange(fullSegmentCoordinates); } coordinates = RemoveDuplicates(coordinates); coordinates = ShrinkToHouseNumberRange(coordinates, item); // TODO: We could actually remove this if (coordinates.Count > 2000) { Log.WriteLine("Segments array too big!"); return(false); } const string latFormat = "00.00000"; const string lngFormat = "00.00000"; for (int i = 0; i < coordinates.Count; i++) { // pos: 1: Begin, 2: middle, 3: end int pos = 2; if (i == 0) { pos = 1; } else if (i == coordinates.Count - 1) { pos = 3; } var currentSegment = coordinates[i]; var latString = Utils.DoubleToStringInvariantCulture(currentSegment.Lat, latFormat); var lngString = Utils.DoubleToStringInvariantCulture(currentSegment.Lng, lngFormat); InsertKoo(item.SegmentId, i, pos, latString, lngString); } var middleSegment = coordinates[coordinates.Count / 2]; UpdateSeg(item.SegmentId, middleSegment.Lat, middleSegment.Lng); InsertKooGroup(item.SegmentId); return(true); }
private static void AddSegment(StreetSegItem item) { _progress.IncrementItemsDone(); Cleanup(item.StreetZipId); var houseNumbers = new List <int>(); var houseNumberReader = DatabaseHelper.ExecuteReader(GlobalLibraryState.ConnectionString, "SELECT HNO " + "FROM building " + "WHERE STREET_ZIP_ID = @1 " + "AND HNO IS NOT NULL AND HNO <> '';", item.StreetZipId); using (houseNumberReader) { while (houseNumberReader.Read()) { var houseNumber = int.Parse(houseNumberReader.GetString("HNO")); houseNumbers.Add(houseNumber); } } int?minHNO = null; int?maxHNO = null; int?scheme = null; if (houseNumbers.Count > 0) { minHNO = houseNumbers.Min(); maxHNO = houseNumbers.Max(); bool odd = false, even = false; foreach (var hno in houseNumbers) { if (hno % 2 == 0) { even = true; } else { odd = true; } } if (odd && even) { scheme = 3; } else if (even) { scheme = 2; } else { scheme = 1; } } long streetSegId = Convert.ToInt64(DatabaseHelper.ExecuteScalar(GlobalLibraryState.ConnectionString, "INSERT INTO street_seg (STREET_ZIP_ID, HN_START, HN_END, SCHEME) " + "VALUES (@1, @2, @3, @4); " + "SELECT LAST_INSERT_ID();", item.StreetZipId, minHNO, maxHNO, scheme)); var coordinatesItem = new CoordinatesItem { HouseNumberStart = minHNO, HouseNumberEnd = maxHNO, SegmentId = streetSegId, StreetZipId = item.StreetZipId }; if (Coordinates.AddCoordinates(coordinatesItem)) { _progress.IncrementItemsSuccessful(); } }
// Shrink the graph to the range between // (1) the point that is nearest to the minimal matched house number // (2) the point that is nearest to the maximal matched house number // If on of them is not matched, return a one-side-open range // If both of them are not matched, return the full street private static List <SegmentCoordinate> ShrinkToHouseNumberRange(List <SegmentCoordinate> coordinates, CoordinatesItem segmentItem) { var minHnoCoordinate = segmentItem.HouseNumberStart == null ? null : GetCoordinateForHouseNumber(segmentItem.StreetZipId, segmentItem.HouseNumberStart.Value); var maxHnoCoordinate = segmentItem.HouseNumberEnd == null ? null : GetCoordinateForHouseNumber(segmentItem.StreetZipId, segmentItem.HouseNumberEnd.Value); var result = new List <SegmentCoordinate>(); long minIndex = 0; double minDistanceMin = double.MaxValue; long maxIndex = coordinates.Count; double minDistanceMax = double.MaxValue; for (int i = 0; i < coordinates.Count; i++) { var currentCoordinate = coordinates[i]; if (minHnoCoordinate != null) { var currentDistanceMin = SegmentCoordinate.DistanceBetweenInKilometers(minHnoCoordinate, currentCoordinate); if (currentDistanceMin < minDistanceMin) { minDistanceMin = currentDistanceMin; minIndex = i; } } if (maxHnoCoordinate != null) { var currentDistanceMax = SegmentCoordinate.DistanceBetweenInKilometers(maxHnoCoordinate, currentCoordinate); if (currentDistanceMax < minDistanceMax) { minDistanceMax = currentDistanceMax; maxIndex = i; } } } // If the minIndex is bigger than the maxIndex // something has gone wrong in the graph building // See if we can get a nice graph by reversing the whole thing if (minIndex > maxIndex) { Utils.Swap(ref minIndex, ref maxIndex); coordinates = new List <SegmentCoordinate>(coordinates); coordinates.Reverse(); } // Add some segments at the beginning and the end, // This is for some countries, where house numbers in streets seem to be pretty random (NOR)... const int addRange = 3; minIndex = Math.Max(0, minIndex - addRange); maxIndex = Math.Min(coordinates.Count, maxIndex + addRange); var startIndex = (int)minIndex; var length = (int)(maxIndex - minIndex); var newCoordinates = coordinates.GetRange(startIndex, length); return(newCoordinates); }