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