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);
}
