public override double Execute(PostgreDbService service)
{
foreach (var value in Enum.GetValues(typeof(IndexEnum)))
{
var index = (IndexEnum)value;
if (index == IndexEnum.rtree || index == IndexEnum.noindex) continue;
service.DisableIndex(index, this._dataSize, this._data);
if (index == this.Index)
service.EnableIndex(this.Index, this._dataSize, this._data);
}
string tableName = this._data.ToString();
tableName += (this._dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)this._dataSize);
string commandText =
@"SELECT 1
FROM " + tableName + @" AS A
WHERE A.geom IN
(
SELECT B.geom
FROM " + tableName + @" B
WHERE ST_TOUCHES(A.geom, B.geom)
)";
return service.ExecuteSqlCommand(commandText);
}
public override double Execute(PostgreDbService service)
{
foreach (var value in Enum.GetValues(typeof(IndexEnum)))
{
var index = (IndexEnum)value;
if (index == IndexEnum.rtree || index == IndexEnum.noindex) continue;
service.DisableIndex(index, this._dataSize, this._data);
if (index == this.Index)
service.EnableIndex(this.Index, this._dataSize, this._data);
}
string tableName = this._data.ToString();
tableName += (this._dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)this._dataSize);
string commandText = @"SELECT 1 FROM " + tableName;
if (_numberOfPoints > 0)
commandText += " WHERE ";
for (int i = 0; i < _numberOfPoints; i++)
{
var lon = _random.NextDouble() * 360 - 180;
var lat = _random.NextDouble() * 180 - 90;
if (i != 0) commandText += _andOr ? " AND " : " OR ";
commandText +=
"ST_DWithin(geom, ST_SetSRID(ST_MakePoint(" +
lon.ToString(CultureInfo.GetCultureInfo("en-GB")) +
", " +
lat.ToString(CultureInfo.GetCultureInfo("en-GB")) +
"), 4326), " +
_distance.ToString(CultureInfo.GetCultureInfo("en-GB")) +
")";
}
return service.ExecuteSqlCommand(commandText);
}
public override double Execute(PostgreDbService service)
{
foreach (var value in Enum.GetValues(typeof(IndexEnum)))
{
var index = (IndexEnum)value;
if (index == IndexEnum.rtree || index == IndexEnum.noindex)
{
continue;
}
service.DisableIndex(index, this._dataSize, this._data);
if (index == this.Index)
{
service.EnableIndex(this.Index, this._dataSize, this._data);
}
}
string tableName = this._data.ToString();
tableName += (this._dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)this._dataSize);
string commandText =
@"SELECT 1
FROM " + tableName + @" AS A
WHERE A.geom IN
(
SELECT B.geom
FROM " + tableName + @" AS B
ORDER BY A.geom <-> B.geom
LIMIT " + _nearestNeighboursCount + @"
)";
return(service.ExecuteSqlCommand(commandText));
}
protected void Application_Start()
{
AreaRegistration.RegisterAllAreas();
WebApiConfig.Register(GlobalConfiguration.Configuration);
FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
RouteConfig.RegisterRoutes(RouteTable.Routes);
BundleConfig.RegisterBundles(BundleTable.Bundles);
AuthConfig.RegisterAuth();
using (var dbservice = new PostgreDbService(300))
{
dbservice.InitializeDb();
}
}
public override double Execute(PostgreDbService service)
{
foreach (var value in Enum.GetValues(typeof(IndexEnum)))
{
var index = (IndexEnum)value;
if (index == IndexEnum.rtree || index == IndexEnum.noindex)
{
continue;
}
service.DisableIndex(index, this._dataSize, this._data);
if (index == this.Index)
{
service.EnableIndex(this.Index, this._dataSize, this._data);
}
}
string tableName = this._data.ToString();
tableName += (this._dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)this._dataSize);
string commandText = @"SELECT 1 FROM " + tableName;
if (_numberOfPoints > 0)
{
commandText += " WHERE ";
}
for (int i = 0; i < _numberOfPoints; i++)
{
var lon = _random.NextDouble() * 360 - 180;
var lat = _random.NextDouble() * 180 - 90;
if (i != 0)
{
commandText += _andOr ? " AND " : " OR ";
}
commandText +=
"ST_DWithin(geom, ST_SetSRID(ST_MakePoint(" +
lon.ToString(CultureInfo.GetCultureInfo("en-GB")) +
", " +
lat.ToString(CultureInfo.GetCultureInfo("en-GB")) +
"), 4326), " +
_distance.ToString(CultureInfo.GetCultureInfo("en-GB")) +
")";
}
return(service.ExecuteSqlCommand(commandText));
}
/// <summary> /// Executes query on DB using PostgreDbSecvice /// </summary> /// <returns>Time of query execution</returns> public abstract double Execute(PostgreDbService service);
/// <summary> /// Executes query on DB using PostgreDbSecvice /// </summary> /// <returns>Time of query execution</returns> public abstract double Execute(PostgreDbService service);
private double GetIndexSpeed(Query query, int times)
{
double result = 0.0d;
//if (times < 3) times = 3;
double max = double.MinValue;
double min = double.MaxValue;
if (query.Index == IndexEnum.rtree)
{
using (var service = new QGisService())
{
for (int i = 0; i < times; i++)
{
var speed = query.Execute(service);
if (speed.Equals(-1.0d))
{
return(-1.0d);
}
speed *= 1000; // to miliseconds
if (speed < min)
{
min = speed;
}
if (speed > max)
{
max = speed;
}
result += speed;
}
}
}
else
{
using (var service = new PostgreDbService())
{
for (int i = 0; i < times; i++)
{
var speed = query.Execute(service);
if (speed.Equals(-1.0d))
{
return(-1.0d);
}
if (speed < min)
{
min = speed;
}
if (speed > max)
{
max = speed;
}
result += speed;
}
}
}
if (times >= 3)
{
result -= min;
result -= max;
}
return(result / (times - (times >= 3 ? 2.0d : 0.0d)));
}
