private static string ParseCoordinatesGeometry(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
switch (mapFeature.ShapeType)
{
case ShapeType.Polygon: return(CreatePolygon(mapFeature, config, declareVariables));
case ShapeType.LineString: return(CreateLineString(mapFeature, config, declareVariables));
case ShapeType.Point: return(CreatePoint(mapFeature, config, declareVariables));
default: throw new Exception("Unsupported shape type!");
}
}
private static string CreatePoint(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
var sb = new StringBuilder();
if (declareVariables)
{
sb.Append(@"DECLARE @validGeom geometry;" + Environment.NewLine);
}
sb.Append("SET @validGeom = geometry::STPointFromText('POINT (");
sb.Append(mapFeature.Coordinates[0].Longitude + " " + mapFeature.Coordinates[0].Latitude);
sb.Append(@")', " + config.Srid + @");" + Environment.NewLine);
return(sb.ToString());
}
internal static SqlCommand CreateCommand(MapFeature mapFeature, Kml2SqlConfig config)
{
var query = CreateCommandQuery(mapFeature, config);
SqlCommand sqlCommand = new SqlCommand(query);
sqlCommand.Parameters.AddWithValue("@Id", mapFeature.Id);
sqlCommand.Parameters.AddWithValue("@Name", GetNameParam(mapFeature));
foreach (KeyValuePair <string, string> simpleData in mapFeature.Data)
{
sqlCommand.Parameters.AddWithValue("@" + config.GetColumnName(simpleData.Key), simpleData.Value);
}
return(sqlCommand);
}
private static string GetInnerRingSql(Vector[] innerCoordinates, Kml2SqlConfig config)
{
var sb = new StringBuilder();
sb.Append("), (");
var coordSql = innerCoordinates.Select(GetVectorSql).ToList();
if (config.FixPolygons && RingInvalid(innerCoordinates))
{
coordSql.Add(GetVectorSql(innerCoordinates[0]));
}
sb.Append(string.Join(", ", coordSql));
return(sb.ToString());
}
internal static string CreateCommandQuery(
MapFeature mapFeature,
Kml2SqlConfig config,
bool useParameters = true,
bool declareVariables = true)
{
var columnNames = mapFeature.Data.Keys.Select(x => config.GetColumnName(x)).ToArray();
string columnText = GetColumnText(columnNames);
string parameters = GetParameters(mapFeature, useParameters, columnNames);
StringBuilder query = new StringBuilder();
query.Append(ParseCoordinates(mapFeature, config, declareVariables));
query.Append(string.Format($"INSERT INTO {config.TableName}({config.IdColumnName}, {config.NameColumnName}, {columnText} {config.PlacemarkColumnName})"));
query.Append(Environment.NewLine);
query.Append($"VALUES({parameters} @placemark);");
return(query.ToString());
}
private static string CreateLineString(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
var sb = new StringBuilder();
if (declareVariables)
{
sb.Append("DECLARE @validGeom geometry;" + Environment.NewLine);
}
sb.Append("SET @validGeom = geometry::STLineFromText('LINESTRING (");
foreach (Vector coordinate in mapFeature.Coordinates)
{
sb.Append(coordinate.Longitude + " " + coordinate.Latitude + ", ");
}
sb.Remove(sb.Length - 2, 2).ToString();
sb.Append(@")', " + config.Srid + @");");
return(sb.ToString());
}
private static string CreatePolygon(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
var sb = new StringBuilder();
if (declareVariables)
{
sb.Append("DECLARE @geom geometry;" + Environment.NewLine);
}
sb.Append("SET @geom = geometry::STPolyFromText('POLYGON((");
sb.Append(GetOuterRingSql(mapFeature.Coordinates, config));
foreach (Vector[] innerCoordinates in mapFeature.InnerCoordinates)
{
sb.Append(GetInnerRingSql(innerCoordinates, config));
}
sb.Append(@"))', " + config.Srid + @").MakeValid();" + Environment.NewLine);
if (declareVariables)
{
sb.Append("DECLARE @validGeom geometry;" + Environment.NewLine);
}
sb.Append("SET @validGeom = @geom.MakeValid().STUnion(@geom.STStartPoint());");
return(sb.ToString());
}
private static string CreateLineString(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
var sb = new StringBuilder();
if (declareVariables)
{
sb.Append("DECLARE @validGeom geometry;" + Environment.NewLine);
}
if (mapFeature.MultiCoordinates.Length == 0)
{
sb.Append("SET @validGeom = geometry::STLineFromText('LINESTRING (");
foreach (Vector coordinate in mapFeature.Coordinates)
{
sb.Append(coordinate.Longitude + " " + coordinate.Latitude + ", ");
}
sb.Remove(sb.Length - 2, 2).ToString();
}
else
{
sb.Append("SET @validGeom = geometry::STMLineFromText('MULTILINESTRING (");
for (int i = 0; i < mapFeature.MultiCoordinates.Length; i++)
{
sb.Append("(");
foreach (Vector coordinate in mapFeature.MultiCoordinates[i])
{
sb.Append(coordinate.Longitude.ToString("G20").Replace(',', '.') + " " + coordinate.Latitude.ToString("G20").Replace(',', '.') + ",");
}
sb.Remove(sb.Length - 1, 1);
sb.Append("),");
}
sb.Remove(sb.Length - 1, 1).ToString();
}
sb.Append(@")', " + config.Srid + @");");
return(sb.ToString());
}
private static string ParseCoordinates(MapFeature mapFeature, Kml2SqlConfig config, bool declareVariables)
{
StringBuilder commandString = new StringBuilder();
if (config.GeoType == PolygonType.Geography)
{
commandString.Append(ParseCoordinatesGeography(mapFeature, config, declareVariables));
if (declareVariables)
{
commandString.Append("DECLARE @placemark geography;" + Environment.NewLine);
}
commandString.Append("SET @placemark = @validGeo;" + Environment.NewLine);
}
else
{
commandString.Append(ParseCoordinatesGeometry(mapFeature, config, declareVariables));
if (declareVariables)
{
commandString.Append("DECLARE @placemark geometry;" + Environment.NewLine);
}
commandString.Append("SET @placemark = @validGeom;" + Environment.NewLine);
}
return(commandString.ToString());
}
