/// <summary>
/// Returns all objects with the given bounding box and valid for the given filter;
/// </summary>
/// <param name="box"></param>
/// <param name="filter"></param>
/// <returns></returns>
public override IList<OsmGeo> Get(GeoCoordinateBox box, OsmSharp.Osm.Filters.Filter filter)
{
// initialize connection.
SQLiteConnection con = this.CreateConnection();
List<OsmGeo> res = new List<OsmGeo>();
// calculate bounding box parameters to query db.
long latitude_min = (long)(box.MinLat * 10000000.0);
long longitude_min = (long)(box.MinLon * 10000000.0);
long latitude_max = (long)(box.MaxLat * 10000000.0);
long longitude_max = (long)(box.MaxLon * 10000000.0);
// TODO: improve this to allow loading of bigger bb's.
uint x_min = lon2x(box.MinLon);
uint x_max = lon2x(box.MaxLon);
uint y_min = lat2y(box.MinLat);
uint y_max = lat2y(box.MaxLat);
IList<long> boxes = new List<long>();
for (uint x = x_min; x <= x_max; x++)
{
for (uint y = y_min; y <= y_max; y++)
{
boxes.Add(this.xy2tile(x, y));
}
}
// STEP 1: query nodes table.
//id latitude longitude changeset_id visible timestamp tile version
//string sql
// = "SELECT node.id, node.latitude, node.longitude, node.changeset_id, node.timestamp, node.version, " +
// "node.usr, node.usr_id, node.visible FROM node WHERE (tile IN ({4})) AND (visible = 1) AND (latitude BETWEEN {0} AND {1} AND longitude BETWEEN {2} AND {3})";
// remove this nasty BETWEEN operation because it depends on the database (!) what results are returned (including or excluding bounds!!!)
string sql
= "SELECT node.id, node.latitude, node.longitude, node.changeset_id, node.timestamp, node.version, " +
"node.usr, node.usr_id, node.visible FROM node WHERE (tile IN ({4})) AND (visible = 1) AND (latitude >= {0} AND latitude < {1} AND longitude >= {2} AND longitude < {3})";
sql = string.Format(sql,
latitude_min.ToString(System.Globalization.CultureInfo.InvariantCulture),
latitude_max.ToString(System.Globalization.CultureInfo.InvariantCulture),
longitude_min.ToString(System.Globalization.CultureInfo.InvariantCulture),
longitude_max.ToString(System.Globalization.CultureInfo.InvariantCulture),
this.ConstructIdList(boxes));
// TODO: parameters.
var com = new SQLiteCommand(sql);
com.Connection = con;
SQLiteDataReader reader = ExecuteReader(com);
Node node = null;
var nodes = new Dictionary<long, Node>();
var nodeIds = new List<long>();
while (reader.Read())
{
node = new Node();
node.Id = reader.GetInt64(0);
int latitude_int = reader.GetInt32(1);
int longitude_int = reader.GetInt32(2);
node.ChangeSetId = reader.IsDBNull(3) ? null : (long?)reader.GetInt64(3);
node.TimeStamp = reader.IsDBNull(4) ? null : (DateTime?)this.ConvertDateTime(reader.GetInt64(4));
node.Version = reader.IsDBNull(5) ? null : (ulong?)reader.GetInt64(5);
node.Latitude = latitude_int / 10000000.0;
node.Longitude = longitude_int / 10000000.0;
node.UserName = reader.IsDBNull(6) ? null : reader.GetString(6);
node.UserId = reader.IsDBNull(7) ? null : (long?)reader.GetInt64(7);
node.Visible = reader.IsDBNull(8) ? null : (bool?)reader.GetBoolean(8);
nodeIds.Add(node.Id.Value);
nodes.Add(node.Id.Value, node);
}
reader.Close();
// STEP2: Load all node tags.
this.LoadNodeTags(nodes);
res.AddRange(nodes.Values);
// load all ways that contain the nodes that have been found.
res.AddRange(this.GetWaysFor(nodeIds));
// get relations containing any of the nodes or ways in the current results-list.
List<Relation> relations = new List<Relation>();
HashSet<long> relationIds = new HashSet<long>();
foreach (OsmGeo osmGeo in res)
{
IList<Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
relations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
// recursively add all relations containing other relations as a member.
do
{
res.AddRange(relations); // add previous relations-list.
List<Relation> newRelations = new List<Relation>();
foreach (OsmGeo osmGeo in relations)
{
IList<Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
newRelations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
relations = newRelations;
} while (relations.Count > 0);
if (filter != null)
{
List<OsmGeo> filtered = new List<OsmGeo>();
foreach (OsmGeo geo in res)
{
if (filter.Evaluate(geo))
{
filtered.Add(geo);
}
}
}
return res;
}
/// <summary>
/// Returns all data within the given bounding box and filtered by the given filter.
/// </summary>
/// <param name="box"></param>
/// <param name="filter"></param>
/// <returns></returns>
public override IList<OsmGeo> Get(GeoCoordinateBox box, OsmSharp.Osm.Filters.Filter filter)
{
// initialize connection.
NpgsqlConnection con = this.CreateConnection();
List<OsmGeo> res = new List<OsmGeo>();
// calculate bounding box parameters to query db.
long latitude_min = (long)(box.MinLat * 10000000.0);
long longitude_min = (long)(box.MinLon * 10000000.0);
long latitude_max = (long)(box.MaxLat * 10000000.0);
long longitude_max = (long)(box.MaxLon * 10000000.0);
// calculate bounding box parameters to query db.
TileRange range = TileRange.CreateAroundBoundingBox(box, 14);
IList<long> boxes = new List<long>();
foreach (Tile tile in range)
{
boxes.Add((long)tile.Id);
}
// STEP 1: query nodes table.
//id latitude longitude changeset_id visible timestamp tile version
string sql
= "SELECT id, latitude, longitude, changeset_id, visible, timestamp, tile, version, usr, usr_id FROM node WHERE (visible = true) AND (tile IN ({4})) AND (latitude >= {0} AND latitude < {1} AND longitude >= {2} AND longitude < {3})";
sql = string.Format(sql,
latitude_min.ToString(),
latitude_max.ToString(),
longitude_min.ToString(),
longitude_max.ToString(),
this.ConstructIdList(boxes));
// TODO: parameters.
NpgsqlCommand com = new NpgsqlCommand(sql);
com.Connection = con;
NpgsqlDataReader reader = com.ExecuteReader();
Node node = null;
Dictionary<long, Node> nodes = new Dictionary<long, Node>();
List<long> nodeIds = new List<long>();
while (reader.Read())
{
// load/parse data.
long returned_id = reader.GetInt64(0);
int latitude_int = reader.GetInt32(1);
int longitude_int = reader.GetInt32(2);
long? changeset_id = reader.IsDBNull(3) ? null : (long?)reader.GetInt64(3);
bool? visible = reader.IsDBNull(4) ? null : (bool?)reader.GetBoolean(4);
DateTime? timestamp = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
long tile = reader.GetInt64(6);
ulong? version = reader.IsDBNull(7) ? null : (ulong?)reader.GetInt32(7);
string usr = reader.IsDBNull(8) ? null : reader.GetString(8);
long? usr_id = reader.IsDBNull(9) ? null : (long?)reader.GetInt32(9);
if (!nodes.ContainsKey(returned_id))
{
// create node.
node = new Node();
node.Id = returned_id;
node.Version = version;
node.UserId = usr_id;
node.UserName = usr;
node.TimeStamp = timestamp;
node.ChangeSetId = changeset_id;
node.Latitude = ((double)latitude_int) / 10000000.0;
node.Longitude = ((double)longitude_int) / 10000000.0;
node.Visible = visible;
nodes.Add(node.Id.Value, node);
nodeIds.Add(node.Id.Value);
}
}
reader.Close();
// STEP2: Load all node tags.
this.LoadNodeTags(nodes);
res.AddRange(nodes.Values);
// load all ways that contain the nodes that have been found.
res.AddRange(this.GetWaysFor(nodeIds));
// get relations containing any of the nodes or ways in the current results-list.
List<Relation> relations = new List<Relation>();
HashSet<long> relationIds = new HashSet<long>();
foreach (OsmGeo osmGeo in res)
{
IList<Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
relations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
// recursively add all relations containing other relations as a member.
do
{
res.AddRange(relations); // add previous relations-list.
List<Relation> newRelations = new List<Relation>();
foreach (OsmGeo osmGeo in relations)
{
IList<Relation> relationsFor = this.GetRelationsFor(osmGeo);
foreach (Relation relation in relationsFor)
{
if (!relationIds.Contains(relation.Id.Value))
{
newRelations.Add(relation);
relationIds.Add(relation.Id.Value);
}
}
}
relations = newRelations;
} while (relations.Count > 0);
if (filter != null)
{
List<OsmGeo> filtered = new List<OsmGeo>();
foreach (OsmGeo geo in res)
{
if (filter.Evaluate(geo))
{
filtered.Add(geo);
}
}
}
return res;
}
