/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public SpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
using (SQLiteConnection cn = new SQLiteConnection(connectionStr))
{
cn.Open();
SQLiteCommand cm = new SQLiteCommand(
String.Format(
"SELECT srid, spatial_index_enabled FROM geometry_columns WHERE(f_table_name='{0}' AND f_geometry_column='{1}');",
tablename, geometryColumnName), cn);
SQLiteDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
_srid = dr.GetInt32(0);
switch (dr.GetInt32(1))
{
case 1: //RTree
String indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
String whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
case 2: //MBRCache
indexName = string.Format(@"cache_{0}_{1}", tablename, geometryColumnName);
whereClause = "mbr=FilterMbrIntersects({1}, {3}, {0}, {2})";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT ROWID FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.MbrCache;
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
catch (Exception)
{
_srid = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public SpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
:base(-2)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
var op = UseLike ? "LIKE" : "=";
using (var cn = new SQLiteConnection(connectionStr))
{
cn.Open();
var cm = new SQLiteCommand(
String.Format(
"SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(\"f_table_name\" {2} '{0}' AND \"f_geometry_column\" {2} '{1}');",
tablename, geometryColumnName, op), cn);
var dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
SRID = dr.GetInt32(0);
var coordDim = dr.GetFieldType(1) == typeof(long)
? dr.GetInt64(1).ToString(NumberFormatInfo.InvariantInfo)
: dr.GetString(1);
switch (coordDim)
{
case "2":
case "XY":
_numOrdinateDimensions = 2;
break;
case "3":
case "XYZ":
case "XYM":
_numOrdinateDimensions = 3;
break;
case "4":
case "XYZM":
_numOrdinateDimensions = 4;
break;
default:
throw new Exception("Cannot evaluate number of ordinate dimensions");
}
switch (dr.GetInt32(2))
{
case 1: //RTree
var indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
var whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
case 2: //MBRCache
indexName = string.Format(@"cache_{0}_{1}", tablename, geometryColumnName);
whereClause = "mbr=FilterMbrIntersects({1}, {3}, {0}, {2})";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT ROWID FROM {0} WHERE {1})", indexName, whereClause);
#pragma warning disable 612,618
_spatiaLiteIndex = SpatiaLiteIndex.MbrCache;
#pragma warning restore 612,618
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
catch (Exception)
{
SRID = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public SpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
: base(-2)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
var op = UseLike ? "LIKE" : "=";
using (var cn = new SQLiteConnection(connectionStr))
{
cn.Open();
var cm = new SQLiteCommand(
String.Format(
"SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(\"f_table_name\" {2} '{0}' AND \"f_geometry_column\" {2} '{1}');",
tablename, geometryColumnName, op), cn);
var dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
SRID = dr.GetInt32(0);
var coordDim = dr.GetFieldType(1) == typeof(long)
? dr.GetInt64(1).ToString(NumberFormatInfo.InvariantInfo)
: dr.GetString(1);
switch (coordDim)
{
case "2":
case "XY":
_numOrdinateDimensions = 2;
break;
case "3":
case "XYZ":
case "XYM":
_numOrdinateDimensions = 3;
break;
case "4":
case "XYZM":
_numOrdinateDimensions = 4;
break;
default:
throw new Exception("Cannot evaluate number of ordinate dimensions");
}
switch (dr.GetInt32(2))
{
case 1: //RTree
var indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
var whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
case 2: //MBRCache
indexName = string.Format(@"cache_{0}_{1}", tablename, geometryColumnName);
whereClause = "mbr=FilterMbrIntersects({1}, {3}, {0}, {2})";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT ROWID FROM {0} WHERE {1})", indexName, whereClause);
#pragma warning disable 612,618
_spatiaLiteIndex = SpatiaLiteIndex.MbrCache;
#pragma warning restore 612,618
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
catch (Exception)
{
SRID = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public ManagedSpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
: base(-2)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
var op = UseLike ? "LIKE" : "=";
using (var cn = new SQLiteConnection(connectionStr))
{
cn.Open();
using (var cm = new SQLiteCommand(
String.Format(
"SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(lower(\"f_table_name\") {2} lower('{0}') AND lower(\"f_geometry_column\") {2} lower('{1}'));",
tablename, geometryColumnName, op), cn))
{
using (var dr = cm.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
SRID = dr.GetInt32(0);
var coordDim = dr.GetFieldType(1) == typeof (long)
? dr.GetInt64(1).ToString(NumberFormatInfo.InvariantInfo)
: dr.GetString(1);
switch (coordDim)
{
case "2":
case "XY":
_ordinates = Ordinates.XY;
break;
case "3":
case "XYZ":
_ordinates = Ordinates.XYZ;
break;
case "XYM":
_ordinates = Ordinates.XYM;
break;
case "4":
case "XYZM":
_ordinates = Ordinates.XYZM;
break;
default:
throw new Exception("Cannot evaluate number of ordinate dimensions");
}
switch (dr.GetInt32(2))
{
case 1: //RTree
var indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
const string whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
}
GetNonSpatialColumns();
}
catch (Exception)
{
SRID = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public SpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
using (SQLiteConnection cn = new SQLiteConnection(connectionStr))
{
cn.Open();
SQLiteCommand cm = new SQLiteCommand(
String.Format(
"SELECT srid, spatial_index_enabled FROM geometry_columns WHERE(f_table_name='{0}' AND f_geometry_column='{1}');",
tablename, geometryColumnName), cn);
SQLiteDataReader dr = cm.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
_srid = dr.GetInt32(0);
switch (dr.GetInt32(1))
{
case 1: //RTree
String indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
String whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
case 2: //MBRCache
indexName = string.Format(@"cache_{0}_{1}", tablename, geometryColumnName);
whereClause = "mbr=FilterMbrIntersects({1}, {3}, {0}, {2})";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT ROWID FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.MbrCache;
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
catch (Exception)
{
_srid = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
/// <summary>
/// Creates an instance of SpatiaLite provider
/// </summary>
/// <param name="connectionStr">Connection String to SQLite database file
/// ("http://www.connectionstrings.com/sqlite")</param>
/// <param name="tablename">Name of the table with geometry information</param>
/// <param name="geometryColumnName">Name of the desired geometry column</param>
/// <param name="oidColumnName">Name of the object Id column</param>
public ManagedSpatiaLite(string connectionStr, string tablename, string geometryColumnName, string oidColumnName)
: base(-2)
{
ConnectionString = connectionStr;
Table = tablename;
GeometryColumn = geometryColumnName; //Name of column to store geometry
ObjectIdColumn = oidColumnName; //Name of object ID column
try
{
var op = UseLike ? "LIKE" : "=";
using (var cn = new SQLiteConnection(connectionStr))
{
cn.Open();
using (var cm = new SQLiteCommand(
String.Format(
"SELECT \"srid\", \"coord_dimension\", \"spatial_index_enabled\" FROM \"geometry_columns\" WHERE(lower(\"f_table_name\") {2} lower('{0}') AND lower(\"f_geometry_column\") {2} lower('{1}'));",
tablename, geometryColumnName, op), cn))
{
using (var dr = cm.ExecuteReader())
{
if (dr.HasRows)
{
dr.Read();
SRID = dr.GetInt32(0);
var coordDim = dr.GetFieldType(1) == typeof(long)
? dr.GetInt64(1).ToString(NumberFormatInfo.InvariantInfo)
: dr.GetString(1);
switch (coordDim)
{
case "2":
case "XY":
_ordinates = Ordinates.XY;
break;
case "3":
case "XYZ":
_ordinates = Ordinates.XYZ;
break;
case "XYM":
_ordinates = Ordinates.XYM;
break;
case "4":
case "XYZM":
_ordinates = Ordinates.XYZM;
break;
default:
throw new Exception("Cannot evaluate number of ordinate dimensions");
}
switch (dr.GetInt32(2))
{
case 1: //RTree
var indexName = string.Format(@"idx_{0}_{1}", tablename, geometryColumnName);
const string whereClause = @"xmin < {0} AND xmax > {1} AND ymin < {2} AND ymax > {3}";
_spatiaLiteIndexClause = string.Format(@"ROWID IN (SELECT pkid FROM {0} WHERE {1})", indexName, whereClause);
_spatiaLiteIndex = SpatiaLiteIndex.RTree;
_useSpatialIndex = true;
break;
}
}
dr.Close();
}
}
}
GetNonSpatialColumns();
}
catch (Exception)
{
SRID = -1;
_spatiaLiteIndex = SpatiaLiteIndex.None;
_useSpatialIndex = false;
}
}
