public void EnableIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
{
indexName = "countries_geom_gist";
}
else
{
return;
}
}
string dropIndexCommandText = @"UPDATE pg_index SET indislive = true, indisvalid = true where indexrelid = '" + indexName + @"'::regclass;";
int rowsChanged;
using (var command = new NpgsqlCommand(dropIndexCommandText, _conn))
rowsChanged = command.ExecuteNonQuery();
if (rowsChanged != 1)
{
throw new Exception("Nieudana kwerenda - " + dropIndexCommandText);
}
}
public void CreateIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
indexName = "countries_geom_gist";
else return;
}
string createIndexCommandText =
@"DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '" + indexName + @"'
AND n.nspname = 'public'
) THEN
CREATE INDEX " + indexName + @"
ON random_points_" + (int)dataSize + @"
USING " + index + @" (geom);
END IF;
END$$;";
using (var command = new NpgsqlCommand(createIndexCommandText, _conn))
command.ExecuteNonQuery();
}
public static Query Get(QueryEnum queryEnum, IndexEnum index, DataEnum data, DataSizeEnum size, Random random)
{
switch (queryEnum)
{
case QueryEnum.FindPointsNearRandomPoints:
return new FindPointsNearRandomPointsQuery(index, data, size, random);
case QueryEnum.FindNearestNeighbours:
return new FindNearestNeighboursQuery(index, data, size, random);
case QueryEnum.TouchesAllToAll:
return new TouchesAllToAllQuery(index, data, size, random);
}
return null;
}
public static Query Get(QueryEnum queryEnum, IndexEnum index, DataEnum data, DataSizeEnum size, Random random)
{
switch (queryEnum)
{
case QueryEnum.FindPointsNearRandomPoints:
return(new FindPointsNearRandomPointsQuery(index, data, size, random));
case QueryEnum.FindNearestNeighbours:
return(new FindNearestNeighboursQuery(index, data, size, random));
case QueryEnum.TouchesAllToAll:
return(new TouchesAllToAllQuery(index, data, size, random));
}
return(null);
}
public FindPointsNearRandomPointsQuery(
IndexEnum index,
DataEnum data,
DataSizeEnum dataSize,
Random random,
int numberOfPoints = 1,
double distance = 1,
bool andOr = false
)
: base(index, dataSize)
{
this._random = random;
this._distance = distance;
this._numberOfPoints = numberOfPoints;
this._andOr = andOr;
this._data = data;
this._qgisPathPrefix = ConfigurationManager.AppSettings["QgisPathPrefix"];
}
public void CreateTable(DataSizeEnum dataSize, DataEnum data)
{
string tableName = data.ToString();
tableName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
string createTableCommandText =
@"DO
$BODY$
DECLARE
i INT := 0;
BEGIN
IF NOT EXISTS
(
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '" + tableName + @"'
AND n.nspname = 'public'
)
THEN
CREATE TABLE " + tableName + @"
(
id serial NOT NULL,
geom geometry,
CONSTRAINT " + tableName + @"_pk PRIMARY KEY (id),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
) WITH (OIDS=FALSE);
ALTER TABLE " + tableName + @" OWNER TO postgres;
" + (data != DataEnum.countries ? @"
FOR i IN 1.." + (int)dataSize + @"
LOOP
INSERT INTO " + tableName + @"(geom)
VALUES (ST_SetSRID(ST_MakePoint((random()*360)-180, (random()*180-90)), 4326));
END LOOP;" : string.Empty) + @"
END IF;
END
$BODY$;";
using (var command = new NpgsqlCommand(createTableCommandText, _conn))
command.ExecuteNonQuery();
}
public void CreateTable(DataSizeEnum dataSize, DataEnum data)
{
string tableName = data.ToString();
tableName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
string createTableCommandText =
@"DO
$BODY$
DECLARE
i INT := 0;
BEGIN
IF NOT EXISTS
(
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '"+tableName+@"'
AND n.nspname = 'public'
)
THEN
CREATE TABLE " + tableName + @"
(
id serial NOT NULL,
geom geometry,
CONSTRAINT " + tableName + @"_pk PRIMARY KEY (id),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL),
CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 4326)
) WITH (OIDS=FALSE);
ALTER TABLE " + tableName + @" OWNER TO postgres;
" + (data != DataEnum.countries ? @"
FOR i IN 1.." + (int)dataSize + @"
LOOP
INSERT INTO " + tableName + @"(geom)
VALUES (ST_SetSRID(ST_MakePoint((random()*360)-180, (random()*180-90)), 4326));
END LOOP;" : string.Empty) + @"
END IF;
END
$BODY$;";
using (var command = new NpgsqlCommand(createTableCommandText, _conn))
command.ExecuteNonQuery();
}
public FindPointsNearRandomPointsQuery
(
IndexEnum index,
DataEnum data,
DataSizeEnum dataSize,
Random random,
int numberOfPoints = 1,
double distance = 1,
bool andOr = false
)
: base(index, dataSize)
{
this._random = random;
this._distance = distance;
this._numberOfPoints = numberOfPoints;
this._andOr = andOr;
this._data = data;
this._qgisPathPrefix = ConfigurationManager.AppSettings["QgisPathPrefix"];
}
public TouchesAllToAllQuery(
IndexEnum index,
DataEnum data,
DataSizeEnum dataSize,
Random random,
int numberOfPoints = 1,
double distance = 1,
bool andOr = false,
int nearestNeighboursCount = 5
)
: base(index, dataSize)
{
this._data = data;
this._random = random;
this._distance = distance;
this._numberOfPoints = numberOfPoints;
this._andOr = andOr;
this._nearestNeighboursCount = nearestNeighboursCount;
this._qgisPathPrefix = ConfigurationManager.AppSettings["QgisPathPrefix"];
}
public TouchesAllToAllQuery
(
IndexEnum index,
DataEnum data,
DataSizeEnum dataSize,
Random random,
int numberOfPoints = 1,
double distance = 1,
bool andOr = false,
int nearestNeighboursCount = 5
)
: base(index, dataSize)
{
this._data = data;
this._random = random;
this._distance = distance;
this._numberOfPoints = numberOfPoints;
this._andOr = andOr;
this._nearestNeighboursCount = nearestNeighboursCount;
this._qgisPathPrefix = ConfigurationManager.AppSettings["QgisPathPrefix"];
}
public void CreateIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
{
indexName = "countries_geom_gist";
}
else
{
return;
}
}
string createIndexCommandText =
@"DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = '" + indexName + @"'
AND n.nspname = 'public'
) THEN
CREATE INDEX " + indexName + @"
ON random_points_" + (int)dataSize + @"
USING " + index + @" (geom);
END IF;
END$$;";
using (var command = new NpgsqlCommand(createIndexCommandText, _conn))
command.ExecuteNonQuery();
}
public void RemoveIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
{
indexName = "countries_geom_gist";
}
else
{
return;
}
}
string dropIndexCommandText = @"DROP INDEX IF EXISTS " + indexName;
using (var command = new NpgsqlCommand(dropIndexCommandText, _conn))
command.ExecuteNonQuery();
}
public void RemoveIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
indexName = "countries_geom_gist";
else return;
}
string dropIndexCommandText = @"DROP INDEX IF EXISTS " + indexName;
using (var command = new NpgsqlCommand(dropIndexCommandText, _conn))
command.ExecuteNonQuery();
}
public void EnableIndex(IndexEnum index, DataSizeEnum dataSize, DataEnum data)
{
string indexName = data.ToString();
indexName += (dataSize == DataSizeEnum.None ? string.Empty : "_" + (int)dataSize);
indexName += "_" + index + @"_idx";
if (data == DataEnum.countries)
{
if (index == IndexEnum.gist)
indexName = "countries_geom_gist";
else return;
}
string dropIndexCommandText = @"UPDATE pg_index SET indislive = true, indisvalid = true where indexrelid = '" + indexName + @"'::regclass;";
int rowsChanged;
using (var command = new NpgsqlCommand(dropIndexCommandText, _conn))
rowsChanged = command.ExecuteNonQuery();
if (rowsChanged != 1) throw new Exception("Nieudana kwerenda - " + dropIndexCommandText);
}
protected Query(IndexEnum index, DataSizeEnum dataSize)
{
this.Index = index;
this._dataSize = dataSize;
}
protected Query(IndexEnum index, DataSizeEnum dataSize)
{
this.Index = index;
this._dataSize = dataSize;
}