private CityInformation ReadCity(OsmPoint point, string placeTag = null)
{
OpenConnection();
var str = @"SELECT TOP(1) Id, Name, Place, Latitude, Longitude, location.STDistance(geography::STPointFromText('POINT(12.100067 47.772099)',4326)) as Distance
from Info.Cities WITH(INDEX(idxCities))
WHERE location.STDistance(geography::STPointFromText('{0}',4326)) < 100000 {1}
order by location.STDistance(geography::STPointFromText('{0}',4326)) " ;
if (!string.IsNullOrEmpty(placeTag))
{
placeTag = "and Place = '" + placeTag + "'";
}
_command.CommandText = string.Format(str, GeneratePointString(point), placeTag);
using (var reader = _command.ExecuteReader())
{
if (reader.Read())
{
var info = new CityInformation
{
RelationId = reader.GetInt64(0),
Name = reader[1] as string,
Place = reader[2] as string,
Location = new OsmPoint {
Latitude = (double)reader[3],
Longitude = (double)reader[4]
},
Distance = (double)reader[5]
};
return(info);
}
return(null);
}
}
public CityNearby GetCitiesNearby(OsmPoint point)
{
return(new CityNearby
{
Nearest = ReadCity(point),
NearestTown = ReadCity(point, "town"),
NearestCity = ReadCity(point, "city")
});
}
/// <summary>
/// Determines the exact city of a specified OsmPoint and all administrative Borders around it
/// (e.g.: Town: Schäftlarn (Level 8) and Regierungsbezirk Oberbayern (Level 5)
/// http://wiki.openstreetmap.org/wiki/Key:admin_level#admin_level</param>
/// </summary>
/// <param name="point">A geopoint with Latitude/Longitude</param>
/// <param name="adminLevel">If you want to specifie a adminlevel</param>
/// <returns>List of cities - take care of the admin level</returns>
public List <CityInformation> GetCityInformation(OsmPoint point, int?adminLevel = null)
{
OpenConnection();
var str = @"SELECT RelationId,Name,PostalCode,Place, AdminLevel
FROM info.AdminLevels WITH(nolock, index(idx_AdminLevelsSpatial))
WHERE geo.STIntersects(geography::STPointFromText('{0}',4326)) = 1" ;
if (adminLevel.HasValue)
{
str += "and AdminLevel = " + adminLevel.Value;
}
else
{
str += " and AdminLevel between 4 and 10";
}
_command.CommandText = string.Format(str, GeneratePointString(point));
using (var reader = _command.ExecuteReader())
{
var res = new List <CityInformation>();
while (reader.Read())
{
var info = new CityInformation
{
RelationId = reader.GetInt64(0),
Name = reader[1] as string,
PostalCode = reader[2] as string,
Place = reader[3] as string,
AdminLevel = reader.GetInt32(4)
};
res.Add(info);
}
return(res);
}
}
public RoadInformation GetRoadInformation(OsmPoint point)
{
OpenConnection();
var str = @"SELECT TOP(1) Id, HighWayType, Name, MaxSpeed from Info.Roads WITH(INDEX(idxInfoRoad))
WHERE street.STDistance(geography::STPointFromText('{0}',4326)) < 100
order by street.STDistance(geography::STPointFromText('{0}',4326)) " ;
_command.CommandText = string.Format(str, GeneratePointString(point));
using (var reader = _command.ExecuteReader())
{
if (reader.Read())
{
var info = new RoadInformation
{
WayId = reader.GetInt64(0),
HighWayType = reader[1] as string,
Name = reader[2] as string,
MaxSpeed = reader[3] as string
};
return(info);
}
return(null);
}
}
private static string GeneratePointString(OsmPoint p)
{
return(string.Format(CultureInfo.InvariantCulture, "POINT({0} {1})", p.Longitude, p.Latitude));
}