/// <summary>
/// Produces from .shp / .dbf /.shx file and .sql file to run in postgis database....
/// </summary>
/// <param name="ShapeFileName"></param>
/// <returns></returns>
public static string ConvertToSql(string ShapeFileName)
{
try
{
string pathToImporter = HttpContext.Current.Server.MapPath("~/Bin/shp2pg/"); //@"E:\PostgreSQL\9.1\bin\";
string srid = "4326";
string pathToShapefilesFolder = HttpContext.Current.Server.MapPath("~/App_Data/uploads");
string result = "";
string sqlfilename = ShapeFileName.ToLower().Replace(".shp", ".sql");
string ShapefilePath = "";
string SqlFilePath = sqlfilename;
if (File.Exists(SqlFilePath))
File.Delete(SqlFilePath);
if (!pathToShapefilesFolder.EndsWith("\\"))
pathToShapefilesFolder += "\\";
ShapefilePath = ShapeFileName;
//use -D in de args to make it a fast 'Dump' type of insert in the database
string args = @"-s {0} -c -W LATIN1 -I {1} {2} > {3} | " + pathToImporter + "psql -d geoneer -U postgres -w -f {3}";
//fill in the arguments & the rest
GeoHouse_Class geohouse_class = new GeoHouse_Class();
ShapeFileName = geohouse_class.split(ShapeFileName, '\\');
string import_table = "public." + ShapeFileName + "_" + DateTime.Now.Ticks.ToString();
args = String.Format(args, srid, ShapefilePath, import_table, SqlFilePath);
string strConverter = "shp2pgsql.exe ";
strConverter = pathToImporter.EndsWith(@"\") ? pathToImporter + strConverter : pathToImporter + @"\" + strConverter;
strConverter = strConverter + args;
Process objProcess = new Process();
ProcessStartInfo objPSI = new ProcessStartInfo(@"c:\windows\system32\cmd.exe", "/C " + strConverter);
objPSI.UseShellExecute = true;
objPSI.WindowStyle = ProcessWindowStyle.Maximized;
bool blnIsReady = false;
objProcess.StartInfo = objPSI;
objProcess.Start();
objProcess.WaitForExit();
blnIsReady = objProcess.HasExited;
if (blnIsReady)
result = "SUCCESS: " + sqlfilename;
else
result = "ERROR: Importing of shapefile did not succeed. No problem details available.";
//delete the files from the upload location
deleteuploadedfiles(ShapeFileName, pathToShapefilesFolder);
return import_table.Split('.')[1].Trim();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="table"></param>
/// <param name="deleteExisting"></param>
/// <returns></returns>
internal string CreateDbTable(DataTable table, bool deleteExisting)
{
//filter the tablename so it is a name usable in the CREATE TABLE statement.
GeoHouse_Class geohouse_class = new GeoHouse_Class();
table.TableName = geohouse_class.split(table.TableName.ToString(), '\\');
StringBuilder sb = new StringBuilder();
if (deleteExisting)
{
//below is for SQL server....
//sb.AppendFormat("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\n", table.TableName);
//sb.AppendFormat("DROP TABLE [dbo].[{0}]\n", table.TableName);
sb.AppendFormat("DROP TABLE {0};\n", table.TableName);
}
sb.AppendFormat("CREATE TABLE {0} (", table.TableName);
for (int i = 0; i < table.Columns.Count; i++)
{
string type = GetDbType(table.Columns[i].DataType, table.Columns[i].MaxLength);
string columnName = table.Columns[i].ColumnName;
if (columnName == "PRIMARY")
{
columnName = "DBF_PRIMARY";
Debug.Assert(false, "Shp2Db: Column PRIMARY renamed to PRIMARY.");
Trace.WriteLine("Shp2Db: Column PRIMARY renamed to PRIMARY.");
}
sb.AppendFormat("{0} {1} ", columnName, type);
// the unique id column cannot be null
if (i == 1)
sb.Append(" NOT NULL ");
if (i + 1 != table.Columns.Count)
sb.Append(", ");
}
sb.Append(")");
// the DataSet update stuff requires a unique column - so give it the row colum that we added
//sb.AppendFormat("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED ([{1}]) ON [PRIMARY]\n",table.TableName, table.Columns[1].ColumnName);
return sb.ToString();
}
