public override DbGeographyWellKnownValue CreateWellKnownValue(DbGeography geographyValue)
{
CheckParameterNotNull("geographyValue", geographyValue);
Debug.Assert(geographyValue.AsBinary() != null, "WKB value must not be null.");
Debug.Assert(geographyValue.AsText() != null, "WKT value must not be null.");
return(new DbGeographyWellKnownValue()
{
CoordinateSystemId = geographyValue.CoordinateSystemId,
WellKnownBinary = geographyValue.AsBinary(),
WellKnownText = geographyValue.AsText()
});
}
private static string approach_one(string polygonWkt)
{
var result = "";
try
{
// Create a DbGeography instance from a WKT string
DbGeography polygon = DbGeography.FromText(polygonWkt);
// If the polygon area is larger than an earth hemisphere (510 Trillion m2 / 2), we know it needs to be fixed
if (polygon.Area.HasValue && polygon.Area.Value > 255000000000000L)
{
// Convert our DbGeography polygon into a SqlGeography object for the ReorientObject() call
var sqlPolygon = SqlGeography.STGeomFromWKB(new System.Data.SqlTypes.SqlBytes(polygon.AsBinary()), 4326);
// ReorientObject will flip the polygon so the outside becomes the inside
sqlPolygon = sqlPolygon.ReorientObject();
// Convert the SqlGeography object back into a WKT string
polygon = DbGeography.FromBinary(sqlPolygon.STAsBinary().Value);
result = polygon.AsText();
}
}
catch (Exception ex)
{
result = string.Empty;
}
return(result);
}
public static DbGeography GetCentre(this DbGeography spatial)
{
if (spatial == null)
{
return(null);
}
return(DbGeography.FromText(SqlGeography.Parse(spatial.AsText()).MakeValid().EnvelopeCenter().ToString()));
}
public static Location FromGeography(this DbGeography geography)
{
if (null == geography)
{
return(null);
}
Location location = new Location()
{
Latitude = geography.Latitude,
Longitude = geography.Longitude,
WellKnownTextWGS84 = geography.AsText()
};
return(location);
}
private static bool IsInside(DbGeography polygon, DbGeography point)
{
SqlServerTypes.Utilities.LoadNativeAssemblies(AppDomain.CurrentDomain.BaseDirectory);
var wellKnownText = polygon.AsText();
var sqlGeography =
SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId)
.MakeValid();
var invertedSqlGeography = sqlGeography.ReorientObject();
if (sqlGeography.STArea() > invertedSqlGeography.STArea())
{
sqlGeography = invertedSqlGeography;
}
polygon = DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
return(point.Intersects(polygon));
}
/// <summary>
/// Generates code to specify the default value for a <see cref="DbGeography" /> column.
/// </summary>
/// <param name="defaultValue"> The value to be used as the default. </param>
/// <returns> Code representing the default value. </returns>
protected virtual string Generate(DbGeography defaultValue)
{
return("DbGeography.FromText(\"" + defaultValue.AsText() + "\", " + defaultValue.CoordinateSystemId + ")");
}
public override void Visit(DbConstantExpression e)
{
Check.NotNull <DbConstantExpression>(e, nameof(e));
switch (((PrimitiveType)TypeHelpers.GetPrimitiveTypeUsageForScalar(e.ResultType).EdmType).PrimitiveTypeKind)
{
case PrimitiveTypeKind.Binary:
byte[] numArray = e.Value as byte[];
if (numArray == null)
{
throw new NotSupportedException();
}
this._key.Append("'");
foreach (int num in numArray)
{
this._key.AppendFormat("{0:X2}", (object)(byte)num);
}
this._key.Append("'");
break;
case PrimitiveTypeKind.Boolean:
case PrimitiveTypeKind.Byte:
case PrimitiveTypeKind.Decimal:
case PrimitiveTypeKind.Double:
case PrimitiveTypeKind.Guid:
case PrimitiveTypeKind.Single:
case PrimitiveTypeKind.SByte:
case PrimitiveTypeKind.Int16:
case PrimitiveTypeKind.Int32:
case PrimitiveTypeKind.Int64:
case PrimitiveTypeKind.Time:
this._key.AppendFormat((IFormatProvider)CultureInfo.InvariantCulture, "{0}", e.Value);
break;
case PrimitiveTypeKind.DateTime:
this._key.Append(((DateTime)e.Value).ToString("o", (IFormatProvider)CultureInfo.InvariantCulture));
break;
case PrimitiveTypeKind.String:
string str = e.Value as string;
if (str == null)
{
throw new NotSupportedException();
}
this._key.Append("'");
this._key.Append(str.Replace("'", "''"));
this._key.Append("'");
break;
case PrimitiveTypeKind.DateTimeOffset:
this._key.Append(((DateTimeOffset)e.Value).ToString("o", (IFormatProvider)CultureInfo.InvariantCulture));
break;
case PrimitiveTypeKind.Geometry:
case PrimitiveTypeKind.GeometryPoint:
case PrimitiveTypeKind.GeometryLineString:
case PrimitiveTypeKind.GeometryPolygon:
case PrimitiveTypeKind.GeometryMultiPoint:
case PrimitiveTypeKind.GeometryMultiLineString:
case PrimitiveTypeKind.GeometryMultiPolygon:
case PrimitiveTypeKind.GeometryCollection:
DbGeometry dbGeometry = e.Value as DbGeometry;
if (dbGeometry == null)
{
throw new NotSupportedException();
}
this._key.Append(dbGeometry.AsText());
break;
case PrimitiveTypeKind.Geography:
case PrimitiveTypeKind.GeographyPoint:
case PrimitiveTypeKind.GeographyLineString:
case PrimitiveTypeKind.GeographyPolygon:
case PrimitiveTypeKind.GeographyMultiPoint:
case PrimitiveTypeKind.GeographyMultiLineString:
case PrimitiveTypeKind.GeographyMultiPolygon:
case PrimitiveTypeKind.GeographyCollection:
DbGeography dbGeography = e.Value as DbGeography;
if (dbGeography == null)
{
throw new NotSupportedException();
}
this._key.Append(dbGeography.AsText());
break;
default:
throw new NotSupportedException();
}
this._key.Append(":");
this._key.Append(e.ResultType.Identity);
}
public static DbGeography MakeValid(this DbGeography geom)
{
return(DbGeography.FromText(SqlGeometry.STGeomFromText(new SqlChars(geom.AsText()), 4326).MakeValid().STAsText().ToSqlString().ToString(), 4326));
}
static async Task Main(string[] args)
{
StreamWriter streamWriter = null;
try
{
// Get the current directory and make it a DirectoryInfo object.
// Do not use Environment.CurrentDirectory, vistual studio
// and visual studio code will return different result:
// Visual studio will return @"projectDir\bin\Release\netcoreapp2.0\", yet
// vs code will return @"projectDir\"
var currentDirectory = new DirectoryInfo(AppDomain.CurrentDomain.BaseDirectory);
// On windows, the current directory is the compiled binary sits,
// so string like @"bin\Release\netcoreapp2.0\" will follow the project directory.
// Hense, the project directory is the great grand-father of the current directory.
string projectDirectory = currentDirectory.Parent.Parent.Parent.FullName;
string dataDirectory = Path.Combine(projectDirectory, "Data");
var countriesSourceFilePath = Path.Combine(dataDirectory, "SeedCountry.csv");
var countriesDestinationFilePath = Path.Combine(dataDirectory, "SeedCountryWithBoundary.csv");
if (!File.Exists(countriesSourceFilePath))
{
throw new ArgumentException($"File '{countriesSourceFilePath}' does not exist");
}
string kmlFilePath = Path.Combine(dataDirectory, "UIA_World_Countries_Boundaries.kml");
if (!File.Exists(kmlFilePath))
{
throw new ArgumentException($"File '{kmlFilePath}' does not exist");
}
// Read countries from file
var countries = FileReader.GetCountries(FileReader.GetFileData(dataDirectory, "SeedCountry.csv"));
streamWriter = new StreamWriter(path: countriesDestinationFilePath, append: false);
await streamWriter.WriteLineAsync("CountryId,Name,Alpha2,Alpha3,AffiliationId,CountryBoundaries");
using (var sr = new StreamReader(kmlFilePath))
{
// Read all data from the reader
string kml = sr.ReadToEnd();
kml = kml.Replace("xmlns=\"http://earth.google.com/kml/2.0\"", ""); // HACK
kml = kml.Replace("xmlns='http://earth.google.com/kml/2.0'", ""); // DOUBLE HACK
kml = kml.Replace("xmlns=\"http://earth.google.com/kml/2.1\"", ""); // MULTI HACK!
kml = kml.Replace("xmlns='http://earth.google.com/kml/2.1'", ""); // M-M-M-M-M-M-M-MONSTER HACK!!!!
kml = kml.Replace("xmlns=\"http://www.opengis.net/kml/2.2\"", ""); // HACK
kml = kml.Replace("xmlns='http://www.opengis.net/kml/2.2'", ""); // DOUBLE HACK
// Open the downloaded xml in an System.Xml.XmlDocument to allow for XPath searching
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
doc.LoadXml(kml);
// Try to find some sort of name for this kml from various places
System.Xml.XmlNode node = doc.SelectSingleNode("//Document[name]/name");
// Load Placemarks recursively and put them in folders
System.Xml.XmlNode documentNode = doc.SelectSingleNode("/kml/Document");
if (documentNode == null)
{
documentNode = doc.SelectSingleNode("/kml");
}
if (documentNode != null)
{
// Find Folders and initialize them recursively
System.Xml.XmlNodeList folders = documentNode.SelectNodes("Folder");
foreach (System.Xml.XmlNode folderNode in folders)
{
// Parse all Placemarks that have a name and Polygon
System.Xml.XmlNodeList placemarkNodes = folderNode.SelectNodes("Placemark");
foreach (System.Xml.XmlNode placemarkNode in placemarkNodes)
{
System.Xml.XmlNodeList simpleDataNodes = placemarkNode.SelectNodes("ExtendedData/SchemaData/SimpleData");
var countryCode = simpleDataNodes[2].InnerText;
var countryName = simpleDataNodes[1].InnerText;
Console.WriteLine($"Parsing {countryName}...");
DbGeography geoBorder = null;
System.Xml.XmlNodeList polygonNodes = placemarkNode.SelectNodes("Polygon");
foreach (System.Xml.XmlNode polygonNode in polygonNodes)
{
// Parse Outer Ring
System.Xml.XmlNode outerRingNode = polygonNode.SelectSingleNode("outerBoundaryIs/LinearRing/coordinates");
if (outerRingNode != null)
{
var points = GeographicCoordinate.ParseCoordinates(outerRingNode);
var geoPolygon = GeographicCoordinate.ConvertGeoCoordinatesToPolygon(points);
if (geoBorder == null)
{
geoBorder = geoPolygon;
}
else
{
try
{
geoBorder = geoBorder.Union(geoPolygon);
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine(ex.Message);
Console.ResetColor();
}
}
}
}
polygonNodes = placemarkNode.SelectNodes("MultiGeometry/Polygon");
foreach (System.Xml.XmlNode polygonNode in polygonNodes)
{
// Parse Outer Ring
System.Xml.XmlNode outerRingNode = polygonNode.SelectSingleNode("outerBoundaryIs/LinearRing/coordinates");
if (outerRingNode != null)
{
var points = GeographicCoordinate.ParseCoordinates(outerRingNode);
var geoPolygon = GeographicCoordinate.ConvertGeoCoordinatesToPolygon(points);
if (geoBorder == null)
{
geoBorder = geoPolygon;
}
else
{
try
{
geoBorder = geoBorder.Union(geoPolygon);
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Blue;
Console.WriteLine(ex.Message);
Console.ResetColor();
}
}
}
}
try
{
await SaveCountryInDatabase(countryCode, countryName, geoBorder.AsText());
await SaveCountryInFile(countryCode, countries, geoBorder, streamWriter);
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine($"Country {countryCode} is not added. Error: {ex.Message}");
Console.ResetColor();
}
}
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Done");
Console.ResetColor();
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(ex.Message);
Console.ResetColor();
}
finally
{
if (streamWriter != null)
{
streamWriter.Dispose();
}
}
Console.ReadLine();
}
/// <summary>
/// Calls method ConvertSQLGeographyTextStringToArray() on current geography object
/// </summary>
/// <param name="geography"></param>
/// <returns></returns>
public static decimal[,] AsArray(this DbGeography geography)
{
return(GeoJSONTools.ConvertSQLGeographyTextStringToArray(geography.AsText()));
}