protected Incident(NpgsqlDataReader reader, Area area)
{
string tableName = GetTableName(area, false);
_id = Convert.ToInt32(reader[tableName + "_" + Columns.Id]);
_location = new PostGIS.Point(Convert.ToDouble(reader[Columns.X(area)]), Convert.ToDouble(reader[Columns.Y(area)]), Convert.ToInt32(reader[Columns.SRID(area)]));
_simulated = Convert.ToBoolean(reader[tableName + "_" + Columns.Simulated]);
_time = Convert.ToDateTime(reader[tableName + "_" + Columns.Time]);
_type = Convert.ToString(reader[tableName + "_" + Columns.Type]);
_nativeId = Convert.ToString(reader[tableName + "_" + Columns.NativeId]);
}
public static List<string> GetUniqueTypes(DateTime start, DateTime end, Area area)
{
List<string> types = new List<string>();
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT DISTINCT " + Columns.Type + " " +
"FROM " + GetTableName(area, true) + " " +
"WHERE " + Columns.Time + " >= @start AND " + Columns.Time + " <= @end",
new Parameter("start", NpgsqlDbType.Timestamp, start),
new Parameter("end", NpgsqlDbType.Timestamp, end));
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
types.Add(Convert.ToString(reader[Columns.Type]));
reader.Close();
DB.Connection.Return(cmd.Connection);
return types;
}
public static string GetTableName(Area area, bool createTableIfNeeded)
{
string tableName = "incident_" + area.Id;
if (createTableIfNeeded)
{
string nativeIdSeqName = tableName + "_native_id_seq";
if (!DB.Connection.TableExists(tableName))
DB.Connection.ExecuteNonQuery(
"CREATE SEQUENCE " + nativeIdSeqName + ";" +
"CREATE TABLE " + tableName + " (" +
Columns.Id + " SERIAL PRIMARY KEY," +
Columns.Location + " GEOMETRY(POINT," + area.Shapefile.SRID + ")," +
Columns.NativeId + " VARCHAR DEFAULT NEXTVAL('" + nativeIdSeqName + "')::VARCHAR," +
Columns.Simulated + " BOOLEAN," +
Columns.Time + " TIMESTAMP," +
Columns.Type + " VARCHAR);" +
"CREATE INDEX ON " + tableName + " USING GIST (" + Columns.Location + ");" +
"CREATE INDEX ON " + tableName + " (" + Columns.NativeId + ");" +
"CREATE INDEX ON " + tableName + " (" + Columns.Simulated + ");" +
"CREATE INDEX ON " + tableName + " (" + Columns.Time + ");" +
"CREATE INDEX ON " + tableName + " (" + Columns.Type + ");" +
"ALTER SEQUENCE " + nativeIdSeqName + " OWNED BY " + tableName + "." + Columns.NativeId + ";");
}
return tableName;
}
internal static void Create(Area area, int pointContainmentBoundingBoxSize)
{
Console.Out.WriteLine("Creating bounding boxes for area \"" + area + "\".");
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT min(st_xmin(" + ShapefileGeometry.Columns.Geometry + "))," +
"max(st_xmax(" + ShapefileGeometry.Columns.Geometry + "))," +
"min(st_ymin(" + ShapefileGeometry.Columns.Geometry + "))," +
"max(st_ymax(" + ShapefileGeometry.Columns.Geometry + ")) " +
"FROM " + area.Shapefile.GeometryTable + " ");
NpgsqlDataReader reader = cmd.ExecuteReader();
reader.Read();
double minX = Convert.ToDouble(reader[0]);
double maxX = Convert.ToDouble(reader[1]);
double minY = Convert.ToDouble(reader[2]);
double maxY = Convert.ToDouble(reader[3]);
reader.Close();
List <PostGIS.Polygon> pointContainmentBoundingBoxes = new List <PostGIS.Polygon>();
for (double x = minX; x <= maxX; x += pointContainmentBoundingBoxSize)
{
for (double y = minY; y <= maxY; y += pointContainmentBoundingBoxSize)
{
pointContainmentBoundingBoxes.Add(new PostGIS.Polygon(new PostGIS.Point[] {
new PostGIS.Point(x, y, area.Shapefile.SRID),
new PostGIS.Point(x, y + pointContainmentBoundingBoxSize, area.Shapefile.SRID),
new PostGIS.Point(x + pointContainmentBoundingBoxSize, y + pointContainmentBoundingBoxSize, area.Shapefile.SRID),
new PostGIS.Point(x + pointContainmentBoundingBoxSize, y, area.Shapefile.SRID),
new PostGIS.Point(x, y, area.Shapefile.SRID)
}, area.Shapefile.SRID));
}
}
string tableName = CreateTable(area);
StringBuilder cmdText = new StringBuilder();
int boxNum = 0;
foreach (PostGIS.Polygon boundingBox in pointContainmentBoundingBoxes)
{
cmdText.Append((cmdText.Length == 0 ? "INSERT INTO " + tableName + " (" + Columns.Insert + ") VALUES " : ",") + "(" + boundingBox.StGeometryFromText + ")");
if (++boxNum >= 1000)
{
cmd.CommandText = cmdText.ToString();
cmd.ExecuteNonQuery();
cmdText.Clear();
boxNum = 0;
}
}
if (boxNum > 0)
{
cmd.CommandText = cmdText.ToString();
cmd.ExecuteNonQuery();
cmdText.Clear();
boxNum = 0;
}
cmd.CommandText = "UPDATE " + tableName + " " +
"SET " + Columns.Relationship + "='" + Relationship.Overlaps + "' " +
"WHERE EXISTS(" +
"SELECT 1 " +
"FROM " + area.Shapefile.GeometryTable + " " +
"WHERE st_overlaps(" + tableName + "." + Columns.BoundingBox + "," + area.Shapefile.GeometryTable + "." + ShapefileGeometry.Columns.Geometry + ")" +
")";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE " + tableName + " " +
"SET " + Columns.Relationship + "='" + Relationship.Within + "' " +
"WHERE EXISTS(" +
"SELECT 1 " +
"FROM " + area.Shapefile.GeometryTable + " " +
"WHERE st_within(" + tableName + "." + Columns.BoundingBox + "," + area.Shapefile.GeometryTable + "." + ShapefileGeometry.Columns.Geometry + ")" +
")";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM " + tableName + " WHERE " + Columns.Relationship + " IS NULL";
cmd.ExecuteNonQuery();
DB.Connection.Return(cmd.Connection);
}
public static string SRID(Area area)
{
return GetTableName(area, false) + "_srid";
}
public static string StY(Area area)
{
return "st_y(" + GetTableName(area, false) + "." + Location + ") as " + Y(area);
}
public static void ClearSimulated(Area area)
{
DB.Connection.ExecuteNonQuery("DELETE FROM " + GetTableName(area, true) + " WHERE " + Columns.Simulated + "=" + true);
VacuumTable(area);
}
internal static void VacuumTable(Area area)
{
DB.Connection.ExecuteNonQuery("VACUUM ANALYZE " + GetTableName(area, true));
}
public static Area Create(Shapefile shapefile, string name, int pointContainmentBoundingBoxSize)
{
Area area = null;
try
{
area = new Area(Convert.ToInt32(DB.Connection.ExecuteScalar("INSERT INTO " + Area.Table + " (" + Columns.Insert + ") VALUES ('" + name + "'," + shapefile.Id + ") RETURNING " + Columns.Id)));
AreaBoundingBoxes.Create(area, pointContainmentBoundingBoxSize);
return area;
}
catch (Exception ex)
{
try { area.Delete(); }
catch (Exception ex2) { Console.Out.WriteLine("Failed to delete area: " + ex2.Message); }
throw ex;
}
}
public static void Clear(Area area)
{
DB.Connection.ExecuteNonQuery("DELETE FROM " + GetTableName(area, true));
VacuumTable(area);
}
internal static void Create(Area area, int pointContainmentBoundingBoxSize)
{
Console.Out.WriteLine("Creating bounding boxes for area \"" + area + "\".");
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT min(st_xmin(" + ShapefileGeometry.Columns.Geometry + "))," +
"max(st_xmax(" + ShapefileGeometry.Columns.Geometry + "))," +
"min(st_ymin(" + ShapefileGeometry.Columns.Geometry + "))," +
"max(st_ymax(" + ShapefileGeometry.Columns.Geometry + ")) " +
"FROM " + area.Shapefile.GeometryTable + " ");
NpgsqlDataReader reader = cmd.ExecuteReader();
reader.Read();
double minX = Convert.ToDouble(reader[0]);
double maxX = Convert.ToDouble(reader[1]);
double minY = Convert.ToDouble(reader[2]);
double maxY = Convert.ToDouble(reader[3]);
reader.Close();
List<PostGIS.Polygon> pointContainmentBoundingBoxes = new List<PostGIS.Polygon>();
for (double x = minX; x <= maxX; x += pointContainmentBoundingBoxSize)
for (double y = minY; y <= maxY; y += pointContainmentBoundingBoxSize)
pointContainmentBoundingBoxes.Add(new PostGIS.Polygon(new PostGIS.Point[]{
new PostGIS.Point(x, y, area.Shapefile.SRID),
new PostGIS.Point(x, y + pointContainmentBoundingBoxSize, area.Shapefile.SRID),
new PostGIS.Point(x + pointContainmentBoundingBoxSize, y + pointContainmentBoundingBoxSize, area.Shapefile.SRID),
new PostGIS.Point(x + pointContainmentBoundingBoxSize, y, area.Shapefile.SRID),
new PostGIS.Point(x, y, area.Shapefile.SRID)}, area.Shapefile.SRID));
string tableName = CreateTable(area);
StringBuilder cmdText = new StringBuilder();
int boxNum = 0;
foreach (PostGIS.Polygon boundingBox in pointContainmentBoundingBoxes)
{
cmdText.Append((cmdText.Length == 0 ? "INSERT INTO " + tableName + " (" + Columns.Insert + ") VALUES " : ",") + "(" + boundingBox.StGeometryFromText + ")");
if (++boxNum >= 1000)
{
cmd.CommandText = cmdText.ToString();
cmd.ExecuteNonQuery();
cmdText.Clear();
boxNum = 0;
}
}
if (boxNum > 0)
{
cmd.CommandText = cmdText.ToString();
cmd.ExecuteNonQuery();
cmdText.Clear();
boxNum = 0;
}
cmd.CommandText = "UPDATE " + tableName + " " +
"SET " + Columns.Relationship + "='" + Relationship.Overlaps + "' " +
"WHERE EXISTS(" +
"SELECT 1 " +
"FROM " + area.Shapefile.GeometryTable + " " +
"WHERE st_overlaps(" + tableName + "." + Columns.BoundingBox + "," + area.Shapefile.GeometryTable + "." + ShapefileGeometry.Columns.Geometry + ")" +
")";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE " + tableName + " " +
"SET " + Columns.Relationship + "='" + Relationship.Within + "' " +
"WHERE EXISTS(" +
"SELECT 1 " +
"FROM " + area.Shapefile.GeometryTable + " " +
"WHERE st_within(" + tableName + "." + Columns.BoundingBox + "," + area.Shapefile.GeometryTable + "." + ShapefileGeometry.Columns.Geometry + ")" +
")";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM " + tableName + " WHERE " + Columns.Relationship + " IS NULL";
cmd.ExecuteNonQuery();
DB.Connection.Return(cmd.Connection);
}
private static string CreateTable(Area area)
{
string tableName = GetTableName(area);
if (!DB.Connection.TableExists(tableName))
DB.Connection.ExecuteNonQuery(
"CREATE TABLE " + tableName + " (" +
Columns.BoundingBox + " GEOMETRY(POLYGON," + area.Shapefile.SRID + ")," +
Columns.Id + " SERIAL PRIMARY KEY," +
Columns.Relationship + " VARCHAR);" +
"CREATE INDEX ON " + tableName + " USING GIST (" + Columns.BoundingBox + ");" +
"CREATE INDEX ON " + tableName + " (" + Columns.Relationship + ");");
return tableName;
}
internal static string GetTableName(Area area)
{
return "area_bounding_boxes_" + area.Id;
}
public static string GetValue(Area area, string nativeId, PostGIS.Point location, bool simulated, string time, string type)
{
return (location.SRID == area.Shapefile.SRID ? location.StGeometryFromText : "st_transform(" + location.StGeometryFromText + "," + area.Shapefile.SRID + ")") + "," + (string.IsNullOrWhiteSpace(nativeId) ? "DEFAULT" : "'" + Util.Escape(nativeId) + "'") + "," + simulated + "," + time + ",'" + Util.Escape(type) + "'";
}
public static void Collapse(Area area, List<string> types, string collapsedType)
{
foreach (string type in types)
DB.Connection.ExecuteNonQuery("UPDATE " + GetTableName(area, true) + " " +
"SET " + Columns.Type + "='" + Util.Escape(collapsedType) + "' " +
"WHERE " + Columns.Type + "='" + Util.Escape(type) + "'");
}
public static void Simulate(Area area, string[] incidentTypes, DateTime startDate, DateTime endDate, int n)
{
double minX = area.BoundingBox.MinX;
double maxX = area.BoundingBox.MaxX;
double minY = area.BoundingBox.MinY;
double maxY = area.BoundingBox.MaxY;
double xRange = maxX - minX;
double yRange = maxY - minY;
int dayRange = ((int)(endDate.Subtract(startDate).TotalDays)) + 1;
NpgsqlCommand cmd = DB.Connection.NewCommand(null);
string tableName = GetTableName(area, true);
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
StringBuilder cmdText = new StringBuilder();
int numInBatch = 0;
int batchSize = 500;
Random r = new Random();
List<Parameter> param = new List<Parameter>(batchSize);
for (int i = 0; i < n; ++i)
{
DateTime date = startDate.AddDays(r.Next(dayRange));
double x = minX + r.NextDouble() * xRange;
double y = minY + r.NextDouble() * yRange;
string type = incidentTypes[r.Next(incidentTypes.Length)];
PostGIS.Point location = new PostGIS.Point(x, y, area.Shapefile.SRID);
cmdText.Append("INSERT INTO " + tableName + " (" + Columns.Insert + ") VALUES (" + GetValue(area, null, location, true, "@date" + numInBatch, type) + ");");
param.Add(new Parameter("date" + numInBatch, NpgsqlDbType.Timestamp, date));
if (++numInBatch >= batchSize)
{
cmd.CommandText = cmdText.ToString();
ConnectionPool.AddParameters(cmd, param);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmdText.Clear();
param.Clear();
numInBatch = 0;
}
}
if (numInBatch > 0)
{
cmd.CommandText = cmdText.ToString();
ConnectionPool.AddParameters(cmd, param);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmdText.Clear();
param.Clear();
numInBatch = 0;
}
cmd.CommandText = "COMMIT";
cmd.ExecuteNonQuery();
DB.Connection.Return(cmd.Connection);
VacuumTable(area);
}
public static int Count(DateTime start, DateTime end, Area area, params string[] types)
{
string typesCondition = null;
if (types != null && types.Length > 0)
{
foreach (string type in types)
typesCondition = (typesCondition == null ? "(" : typesCondition + " OR ") + Columns.Type + "='" + type + "'";
typesCondition += ")";
}
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT COUNT(*) " +
"FROM " + GetTableName(area, true) + " " +
"WHERE " + (typesCondition == null ? "" : typesCondition + " AND ") +
Columns.Time + " >= @start AND " +
Columns.Time + " <= @end", new Parameter("start", NpgsqlDbType.Timestamp, start), new Parameter("end", NpgsqlDbType.Timestamp, end));
int count = Convert.ToInt32(cmd.ExecuteScalar());
DB.Connection.Return(cmd.Connection);
return count;
}
public static string Select(Area area)
{
return Reflector.GetSelectColumns(GetTableName(area, false), new string[] { StX(area), StY(area), StSRID(area) }, typeof(Columns));
}
public static List<Incident> Get(DateTime start, DateTime end, Area area, params string[] types)
{
List<Incident> incidents = new List<Incident>();
string typesCondition = null;
if (types != null && types.Length > 0)
{
foreach (string type in types)
typesCondition = (typesCondition == null ? "(" : typesCondition + " OR ") + Columns.Type + "='" + type + "'";
typesCondition += ")";
}
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT " + Columns.Select(area) + " " +
"FROM " + GetTableName(area, true) + " " +
"WHERE " + (typesCondition == null ? "" : typesCondition + " AND ") +
Columns.Time + " >= @start AND " +
Columns.Time + " <= @end", new Parameter("start", NpgsqlDbType.Timestamp, start), new Parameter("end", NpgsqlDbType.Timestamp, end));
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
incidents.Add(new Incident(reader, area));
reader.Close();
DB.Connection.Return(cmd.Connection);
return incidents;
}
public static string StSRID(Area area)
{
return "st_srid(" + GetTableName(area, false) + "." + Location + ") as " + SRID(area);
}
public static Incident GetLast(Area area)
{
Incident last = null;
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT " + Columns.Select(area) + " FROM " + GetTableName(area, true) + " ORDER BY " + Columns.Time + " DESC LIMIT 1");
NpgsqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
last = new Incident(reader, area);
reader.Close();
DB.Connection.Return(cmd.Connection);
return last;
}
public static string Y(Area area)
{
return GetTableName(area, false) + "_y";
}
public static Set<string> GetNativeIds(Area area)
{
Set<string> nativeIds = new Set<string>();
NpgsqlCommand cmd = DB.Connection.NewCommand("SELECT " + Columns.NativeId + " FROM " + GetTableName(area, true));
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
nativeIds.Add(Convert.ToString(reader[0]));
reader.Close();
}
DB.Connection.Return(cmd.Connection);
return nativeIds;
}
internal static List<int> Insert(NpgsqlConnection connection,
IEnumerable<Tuple<PostGIS.Point, string, DateTime>> points,
Prediction prediction,
Area area,
bool vacuum)
{
NpgsqlCommand cmd = DB.Connection.NewCommand(null, null, connection);
string pointTable = GetTableName(prediction);
List<int> ids = new List<int>();
StringBuilder pointValues = new StringBuilder();
int pointNum = 0;
int pointsPerBatch = 1000;
foreach (Tuple<PostGIS.Point, string, DateTime> pointIncidentTime in points)
{
PostGIS.Point point = pointIncidentTime.Item1;
string incidentType = pointIncidentTime.Item2;
DateTime time = pointIncidentTime.Item3;
if (point.SRID != area.Shapefile.SRID)
throw new Exception("Area SRID (" + area.Shapefile.SRID + ") does not match point SRID (" + point.SRID);
pointValues.Append((pointValues.Length > 0 ? "," : "") + "(DEFAULT,'" + incidentType + "',st_geometryfromtext('POINT(" + point.X + " " + point.Y + ")'," + point.SRID + "),@time_" + pointNum + ")");
ConnectionPool.AddParameters(cmd, new Parameter("time_" + pointNum, NpgsqlDbType.Timestamp, time));
if ((++pointNum % pointsPerBatch) == 0)
{
cmd.CommandText = "INSERT INTO " + pointTable + " (" + Columns.Insert + ") VALUES " + pointValues + " RETURNING " + Columns.Id;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
ids.Add(Convert.ToInt32(reader[0]));
reader.Close();
pointValues.Clear();
cmd.Parameters.Clear();
}
}
if (pointValues.Length > 0)
{
cmd.CommandText = "INSERT INTO " + pointTable + " (" + Columns.Insert + ") VALUES " + pointValues + " RETURNING " + Columns.Id;
NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
ids.Add(Convert.ToInt32(reader[0]));
reader.Close();
pointValues.Clear();
cmd.Parameters.Clear();
}
if (vacuum)
VacuumTable(prediction);
return ids;
}
internal static string GetTableName(Area area)
{
return("area_bounding_boxes_" + area.Id);
}
