public void Can_insert_and_retrieve_SqlGeometry()
{
Db.DropAndCreateTable<GeometryTable>();
// A simple line from (0,0) to (4,4) Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0, 4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);
Db.Insert(new GeometryTable { Id = 1, Shape = shape});
var result = Db.SingleById<GeometryTable>(1).Shape;
var lengths = Db.Column<double>("SELECT Shape.STLength() AS Length FROM GeometryTable");
Assert.AreEqual((double) result.STLength(), lengths.First());
Assert.AreEqual(shape.STStartPoint().STX, result.STStartPoint().STX);
Assert.AreEqual(shape.STStartPoint().STY, result.STStartPoint().STY);
Assert.AreEqual(shape.STEndPoint().STX, result.STEndPoint().STX);
Assert.AreEqual(shape.STEndPoint().STY, result.STEndPoint().STY);
Assert.AreEqual(2, (int) result.STNumPoints());
result.PrintDump();
}
public void Can_insert_and_retrieve_SqlGeometry()
{
Db.DropAndCreateTable <GeometryTable>();
// A simple line from (0,0) to (4,4) Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0, 4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);
Db.Insert(new GeometryTable {
Id = 1, Shape = shape
});
var result = Db.SingleById <GeometryTable>(1).Shape;
var lengths = Db.Column <double>("SELECT Shape.STLength() AS Length FROM GeometryTable");
Assert.AreEqual((double)result.STLength(), lengths.First());
Assert.AreEqual(shape.STStartPoint().STX, result.STStartPoint().STX);
Assert.AreEqual(shape.STStartPoint().STY, result.STStartPoint().STY);
Assert.AreEqual(shape.STEndPoint().STX, result.STEndPoint().STX);
Assert.AreEqual(shape.STEndPoint().STY, result.STEndPoint().STY);
Assert.AreEqual(2, (int)result.STNumPoints());
result.PrintDump();
}
public void Can_insert_and_retrieve_SqlGeometry()
{
using (var db = OpenDbConnection())
{
// A simple line from (0,0) to (4,4) Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0, 4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);
db.Insert(new GeoTestTable()
{
Shape = shape
});
var result = db.Select(db.From <GeoTestTable>()).First().Shape;
var lengths = db.Column <double>("select Shape.STLength() AS Length from GeoTestTable");
Assert.AreEqual((double)result.STLength(), lengths.First());
Assert.AreEqual(shape.STStartPoint().STX, result.STStartPoint().STX);
Assert.AreEqual(shape.STStartPoint().STY, result.STStartPoint().STY);
Assert.AreEqual(shape.STEndPoint().STX, result.STEndPoint().STX);
Assert.AreEqual(shape.STEndPoint().STY, result.STEndPoint().STY);
Assert.AreEqual(2, (int)result.STNumPoints());
}
}
internal static char GetSingleChar(this SqlDataReader reader, int columnIndex)
{
System.Data.SqlTypes.SqlChars val = reader.GetSqlChars(columnIndex);
if (val.Length != 1)
{
throw new ApplicationException(
"Expected value to be 1 char long, but was "
+ val.Length.ToString() + " chars long.");
}
return(val[0]);
}
public void SqlGeometryToJSONTest()
{
string expected = "{\"type\":\"FeatureCollection\", \"features\":[{\"type\":\"Feature\", \"geometry\":{\"type\":\"Polygon\", \"coordinates\":[[[-41.2281722793271, 174.70763047448], [-41.1553878554989, 174.777668316277], [-41.1560745010067, 174.840839702996], [-41.2171859512021, 174.878605205926], [-41.2549514541317, 174.807880718621], [-41.2803573379208, 174.802387554559], [-41.2824172744442, 174.834659893426], [-41.3270492324521, 174.841526348504], [-41.3531417617489, 174.774921734246], [-41.3043899306942, 174.715870220574], [-41.2281722793271, 174.70763047448]]]}, \"properties\":null, \"crs\":{\"type\":\"name\", \"properties\":{\"name\":\"epsg:4326\"}}}]}";
System.Data.SqlTypes.SqlChars wkbGeometry = new System.Data.SqlTypes.SqlChars("POLYGON ((-41.2281722793271 174.70763047448, -41.1553878554989 174.777668316277, -41.1560745010067 174.840839702996, -41.2171859512021 174.878605205926, -41.2549514541317 174.807880718621, -41.2803573379208 174.802387554559, -41.2824172744442 174.834659893426, -41.3270492324521 174.841526348504, -41.3531417617489 174.774921734246, -41.3043899306942 174.715870220574, -41.2281722793271 174.70763047448))");
SqlGeometry sqlGeometry = SqlGeometry.STGeomFromText(wkbGeometry, 4326);
string actual;
actual = GeoJSON.SqlGeometryToJSON(sqlGeometry);
Assert.AreEqual(expected, actual);
}
public OfficeHoursClass(String dayOfTheWeek,
DateTime date,
TimeSpan openTime,
TimeSpan closeTime,
System.Data.SqlTypes.SqlChars status)
{
m_strDayOfWeek = dayOfTheWeek;
m_dtdate = date;
m_dtOpenTime = openTime;
m_dtCloseTime = closeTime;
m_chrStatus = status;
}
public static SqlGeometry JSONToGeometry(string json)
{
SqlGeometry Feature = null;
int srid = 4326;
string wkt = GeoJSONToWkt(json, out srid);
if (wkt != string.Empty)
{
System.Data.SqlTypes.SqlChars wkbGeometry = new System.Data.SqlTypes.SqlChars(wkt.ToString());
Feature = SqlGeometry.STGeomFromText(wkbGeometry, srid);
}
return(Feature);
}
public UserClass(String strUserName,
String strPassword,
String strFirstName,
String strLastName,
System.Data.SqlTypes.SqlChars chrUserType,
System.Data.SqlTypes.SqlChars chrAccessType)
{
m_strUsername = strUserName;
m_strPassword = strPassword;
m_strFirstName = strFirstName;
m_strLastName = strLastName;
m_chrUserType = chrUserType;
m_chrAccessType = chrAccessType;
}
public AppointmentClass(String strPatientName,
String strDentistName,
String strAppointmentType,
String strDescription,
DateTime dtCreatedDate,
DateTime dtDateTime,
System.Data.SqlTypes.SqlChars chrStatus)
{
m_strPatientName = strPatientName;
m_strDentistName = strDentistName;
m_strAppointmentType = strAppointmentType;
m_strDescription = strDescription;
m_dtCreatedDate = dtCreatedDate;
m_dtDateTime = dtDateTime;
m_chrStatus = chrStatus;
}
public void Can_insert_SqlGeography_and_SqlGeometry()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable<GeoTest>();
// Statue of Liberty
var geo = SqlGeography.Point(40.6898329, -74.0452177, 4326);
// A simple line from (0,0) to (4,4) Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0, 4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);
db.Insert(new GeoTest { Id = 1, Location = geo, Shape = shape });
shape = db.SingleById<GeoTest>(1).Shape;
Assert.That(shape, Is.Not.Null);
new { shape.STEndPoint().STX, shape.STEndPoint().STY }.PrintDump();
}
}
/// <summary>
/// Construye un dato del tipo Geography a partir de un string con las coordenadas del poligono, verificando que el sentido de
/// construcción sea el correcto
/// </summary>
/// <param name="rawdata">datos del polígono</param>
/// <returns>el poligo en formato WKB (well known binary)</returns>
private byte[] BuildGeomAsBinary(string rawdata)
{
try
{
var strGeom = new System.Data.SqlTypes.SqlChars(("POLYGON((" + ToPol(rawdata, false) + "))").ToCharArray());
var binGeom = SqlGeography.STGeomFromText(strGeom, 4326).STAsBinary();
return(binGeom.Buffer);
}
catch
{
try
{
var strGeom = new System.Data.SqlTypes.SqlChars(("POLYGON((" + ToPol(rawdata, true) + "))").ToCharArray());
var binGeom = SqlGeography.STGeomFromText(strGeom, 4326).STAsBinary();
return(binGeom.Buffer);
}
catch
{
System.Diagnostics.Debug.WriteLine("Ni al derecho, ni al reves");
}
}
return(null);
}
public void ProcessASPPosition(PositionMessage pos)
{
var spos = new ShipPosition();
spos.Rumbo = pos.Course;
spos.Region = pos.OceanRegion;
spos.Velocidad = pos.Speed;
spos.TimeStamp = pos.TimeOfPosition;
spos.TimeStampInASP = pos.InAsp;
spos.TimeStampOutASP = pos.OutAsp;
Ship ship = null;
if (pos.DNID == "sata")
{
ship = ShipManager.getByISN(pos.Speed);
spos.Velocidad = "-";
}
else
{
ship = ShipManager.getByMemberNum(pos.MemberNumber);
}
if (ship == null)
{
log.Error(string.Format("No hay barco registrado con Member Number {0} para la posicion recibida", pos.MemberNumber));
return;
}
spos.ShipId = ship.Id;
spos.TimeStampInDC = DateTime.UtcNow;
var p = string.Format(CultureInfo.InvariantCulture, "POINT({0} {1})", pos.Longitude, pos.Latitude).ToCharArray();
var sqlp = new System.Data.SqlTypes.SqlChars(p);
var ppp = (SqlGeography.STGeomFromText(sqlp, 4326)).STAsBinary();
spos.Position = (byte[])ppp.ToSqlBinary();
Insert(spos);
ShipManager.ChangeShipStatus(ShipStatus.Ok, ship);
}
public void Can_insert_SqlGeography_and_SqlGeometry()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <GeoTest>();
// Statue of Liberty
var geo = SqlGeography.Point(40.6898329, -74.0452177, 4326);
// A simple line from (0,0) to (4,4) Length = SQRT(2 * 4^2)
var wkt = new System.Data.SqlTypes.SqlChars("LINESTRING(0 0, 4 4)".ToCharArray());
var shape = SqlGeometry.STLineFromText(wkt, 0);
db.Insert(new GeoTest {
Id = 1, Location = geo, Shape = shape
});
shape = db.SingleById <GeoTest>(1).Shape;
Assert.That(shape, Is.Not.Null);
new { shape.STEndPoint().STX, shape.STEndPoint().STY }.PrintDump();
}
}
private string GetMapJson(DataTable data)
{
var connectionCredentials = new bv.common.Configuration.ConnectionCredentials();
string connection = connectionCredentials.ConnectionString;
var column = new DataColumn {
DataType = typeof(SqlGeometry), ColumnName = "geom"
};
data.Columns.Add(column);
var sqlConnection = new SqlConnection(connection); sqlConnection.Open();
string lTableName = CoordinatesUtils.GetWKBTableName(data, connection);
const int srid = 4326;
string ratio = "0"; // Settlement
if (lTableName == "gisWKBRegion")
{
ratio = "50";
}
if ((lTableName == "gisWKBRayon") || (lTableName == "gisWKBDistrict"))
{
ratio = "100";
}
string strIds = string.Empty;
if (data.Rows.Count > 0)
{
strIds = String.Join(",", data.AsEnumerable().Select(x => x.Field <long>("id").ToString()).ToArray());
strIds = string.Format("idfsGeoObject in ({0}) and ", strIds);
if ((data.PrimaryKey == null) || (data.PrimaryKey.Length == 0))
{
var key = new DataColumn[1];
key[0] = data.Columns["id"];
data.PrimaryKey = key;
}
try // Try get it from precached table geomShape_4326
{
string strBatchSql = "SELECT g.idfsGeoObject as id, g.geomShape_4326 as geom FROM " + lTableName + "Ready g WHERE " + strIds + " Ratio = " + ratio;
var cmdBatch = new SqlCommand(strBatchSql, sqlConnection);
using (SqlDataReader dr = cmdBatch.ExecuteReader())
{
var resBatch = new DataTable();
resBatch.Load(dr);
if ((resBatch != null) && resBatch.Rows.Count > 0)
{
if ((resBatch.PrimaryKey == null) || (resBatch.PrimaryKey.Length == 0))
{
var resBatchKey = new DataColumn[1];
resBatchKey[0] = resBatch.Columns["id"];
resBatch.PrimaryKey = resBatchKey;
}
data.Merge(resBatch, false, MissingSchemaAction.Ignore);
}
}
}
catch (Exception /*ex*/) { /*var strErr = ex.Message;*/ }
foreach (var rEmptyGeom in data.Select("geom is null"))
{
SharpMap.Geometries.Geometry feature = null;
long id = 0;
if (rEmptyGeom["id"].ToString() != "")
{
id = Int64.Parse(rEmptyGeom["id"].ToString());
}
// Try get it as usual
feature = Extents.GetGeomById(sqlConnection, lTableName, id);
if (feature != null)
{
feature = GeometryTransform.TransformGeometry(feature, GIS_V4.Common.CoordinateSystems.SphericalMercatorCS, GIS_V4.Common.CoordinateSystems.WGS84);
var wktGeometry = new System.Data.SqlTypes.SqlChars(feature.AsText());
rEmptyGeom["geom"] = SqlGeometry.STGeomFromText(wktGeometry, srid);
}
if (feature != null)
{
SharpMap.Geometries.Point point = feature.GetBoundingBox().GetCentroid();
rEmptyGeom["x"] = point.X;
rEmptyGeom["y"] = point.Y;
}
else
{
double x, y;
if (CoordinatesUtils.GetAdminUnitCoordinates(connection, id, out x, out y))
{
rEmptyGeom["x"] = x;
rEmptyGeom["y"] = y;
}
}
}
}
sqlConnection.Close();
var ds = new DataSet();
ds.Tables.Add(data.Copy());
string json = GeoJSON.DataSetToJSON(ds);
json = json.Replace("\\r\\n", " ");
return(json);
}
