protected virtual void AddSpatialColumn(string geometryColumnName, int desiredSrid, string geometryType)
{
Console.WriteLine("Adding spatial column for " + _tableName + " with SRID " + desiredSrid);
const string AddNewSpatialColumn = @"ALTER TABLE {0} ADD geom_{1} geometry({2}, {1})";
// const string ConvertColumnToSRID = @"SELECT UpdateGeometrySRID('{0}','geom_{1}', {1})";
const string CreateSpatialIndex = @"CREATE INDEX {0}_geom_{1}_gist ON public.{0} USING gist(geom_{1})";
const string UpdateMultilineColumn = @"UPDATE {0} SET geom_{1} = ST_Transform({2}, {3})";
var alterTableScript = String.Format(AddNewSpatialColumn, _tableName, desiredSrid, geometryType);
var alterCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, alterTableScript);
ExecuteShellCommand.ExecuteProcess(alterCommand);
Console.WriteLine("Reprojecting... ");
var force = String.Format(UpdateMultilineColumn, _tableName, desiredSrid, geometryColumnName, desiredSrid);
var forceCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, force);
ExecuteShellCommand.ExecuteProcess(forceCommand);
var spatialIndex = String.Format(CreateSpatialIndex, _tableName, desiredSrid);
var spatialIndexCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""",
_hostName, _userName, _databaseName, spatialIndex);
ExecuteShellCommand.ExecuteProcess(spatialIndexCommand);
}
protected void ApplyNonUniqueIndexToColumn(string columnName)
{
var alterTableScript = String.Format(NonUniqueColumnBstarIndex, _tableName, columnName);
var alterCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, alterTableScript);
ExecuteShellCommand.ExecuteProcess(alterCommand);
}
private void DropDatabase()
{
Console.WriteLine("Dropping database...");
var dropCommand = String.Format(DropDatabaseTemplate, _databaseName);
var dropScript = String.Format(@"psql -q --host={1} --username={2} -d postgres --command ""{0}""",
dropCommand, _hostName, _userName);
ExecuteShellCommand.ExecuteProcess(dropScript);
}
private void AddSpatialReferenceSystem()
{
Console.WriteLine("Adding custom spatial reference systems...");
var files = SpatialReferenceSystemScripts();
foreach (var file in files)
{
if (file == null)
{
return;
}
ExecuteShellCommand.ExecuteSql(file, _databaseName, _hostName, _userName);
}
}
private void CreateDatabase()
{
Console.WriteLine("Creating database...");
var createScript = String.Format(CreateDatabaseTemplate, _databaseName, _userName);
var createCommand = String.Format(@"psql -q --host={0} --username={1} -d postgres --command ""{2}""",
_hostName, _userName, createScript);
ExecuteShellCommand.ExecuteProcess(createCommand);
Console.WriteLine("Enabling GIS extensions...");
var alterTableScript = String.Format(AlterNewTableTemplate, _databaseName);
var alterCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, alterTableScript);
ExecuteShellCommand.ExecuteProcess(alterCommand);
var postGisCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, AddPostGisExtension);
ExecuteShellCommand.ExecuteProcess(postGisCommand);
var topologyCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, AddPostGisTopologyExtension);
ExecuteShellCommand.ExecuteProcess(topologyCommand);
var fuzzyCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, AddPostGisFuzzyStrMatchExtension);
ExecuteShellCommand.ExecuteProcess(fuzzyCommand);
var tigerCommand = String.Format(@"psql -q --host={0} --username={1} -d {2} --command ""{3}""", _hostName,
_userName, _databaseName, AddPostGisTigerGeocoderExtension);
ExecuteShellCommand.ExecuteProcess(tigerCommand);
}