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