/// <summary>
/// Get the geojson representation of all the drawings at the extent at this zoom level
/// </summary>
/// <param name="xmin"></param>
/// <param name="ymin"></param>
/// <param name="xmax"></param>
/// <param name="ymax"></param>
/// <param name="zoom"></param>
/// <returns></returns>
public static string getDrawingsByExtent(double xmin, double ymin, double xmax, double ymax, int zoom)
{
string outputGeoJson = "{\"type\": \"FeatureCollection\",\"features\": []}";
//Create the geojson object
GeoJSON.GeoJSON geoJSON = new GeoJSON.GeoJSON();
using (SqlConnection conn = new SqlConnection(AppConstants.CONN_STRING_SUPSQL_ARCGIS))
{
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "Select Shape.ToString() as shp, Thumbnail, Path, DrawingID, Version, Lat, Lon, Zoom from DRAWINGS ";
comm.CommandText += "WHERE Lon Between @minLon AND @maxLon AND Lat BETWEEN @minLat AND @maxLat AND Zoom = @zoomLevel;";
comm.Parameters.AddWithValue("@minLon", xmin);
comm.Parameters.AddWithValue("@maxLon", xmax);
comm.Parameters.AddWithValue("@minLat", ymin);
comm.Parameters.AddWithValue("@maxLat", ymax);
comm.Parameters.AddWithValue("@zoomLevel", zoom);
try
{
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
GeoJSON.JSONFeature newFeature = new GeoJSON.JSONFeature();
newFeature.addGeom(reader["shp"].ToString());
newFeature.addProperty("Path", reader["Path"].ToString());
newFeature.addProperty("DrawingID", reader["DrawingID"].ToString());
newFeature.addProperty("Version", (int)reader["Version"]);
newFeature.addProperty("Lat", double.Parse(reader["Lat"].ToString()));
newFeature.addProperty("Lon", double.Parse(reader["Lon"].ToString()));
byte[] thumbnailBuffer = (byte[])reader["Thumbnail"];
string base64String = Convert.ToBase64String(thumbnailBuffer);
newFeature.addProperty("Thumbnail", base64String);
geoJSON.addFeature(newFeature);
}
outputGeoJson = geoJSON.ToString();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
comm.Dispose();
conn.Close();
}
}
return outputGeoJson;
}
/// <summary>
///
/// </summary>
/// <param name="extents"></param>
/// <returns></returns>
public static string getControlPoints(string extents = null)
{
string outputGeoJson = "{\"type\": \"FeatureCollection\",\"features\": []}";
//Create the geojson object
Enbridge.GeoJSON.GeoJSON geoJSON = new Enbridge.GeoJSON.GeoJSON();
using (SqlConnection conn = new SqlConnection(AppConstants.CONN_STRING_SUPSQL_ARCGIS))
{
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT EventID, Name, ApprovedBy, ApprovedDate, LoadedBy, LoadedDate, SHAPE.ToString() as shp from CONTROLPOINTS ";
comm.CommandText += "WHERE ApprovedDate IS NOT NULL";
if (string.IsNullOrEmpty(extents))
{
comm.CommandText += ";";
}
else
{
string[] extentStrings = extents.Split(null);
string polygonString = string.Format("POLYGON(({0} {1}, {0} {3}, {2} {3}, {2} {1}, {0} {1}))",
extentStrings[0], extentStrings[1], extentStrings[2], extentStrings[3]);
comm.CommandText += " AND SHAPE.STIntersects(geometry::STGeomFromText(@geomString, 4326)) = 1;";
comm.Parameters.AddWithValue("@geomString", polygonString);
}
try
{
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
GeoJSON.JSONFeature newFeature = new GeoJSON.JSONFeature();
string geomString = reader["shp"] as string ?? "null";
Console.WriteLine(geomString);
newFeature.addGeom(geomString);
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == "shp" || fieldName.ToLower() == "shape" || fieldName.ToLower() == "thumbnail")
{
continue;
}
newFeature.addProperty(fieldName, reader[fieldName]);
}
geoJSON.addFeature(newFeature);
}
outputGeoJson = geoJSON.ToString();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
comm.Dispose();
conn.Close();
}
}
return outputGeoJson;
}
