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