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); }