public static ArrayList GetExtent(string input)
{
Npgsql_Class npg = new Npgsql_Class();
string s = string.Empty;
s = "SELECT ST_Extent(geometry) FROM " + input + ";";
DataTable dt = Data_Import_Transpose.CreateDataTableSelectStatement(s, npg);
s = dt.Rows[0].ItemArray.GetValue(0).ToString();
ArrayList bb = MaxMinBB(s);
return bb;
}
public string DetectType()
{
Npgsql_Class npg = new Npgsql_Class();
string sqlstring = "select ST_GeometryType(the_geom) from data_geoms limit 1;";
DataTable dt = Data_Import_Transpose.CreateDataTableSelectStatement(sqlstring, npg);
string t = dt.Rows[0].ItemArray.GetValue(0).ToString();
if (t.Contains("Polygon")) { t = "polygon"; }
if (t.Contains("Line")) { t = "line"; }
if (t.Contains("Point")) { t = "point"; }
return t;
}
public static ArrayList GetBBox(string input)
{
Npgsql_Class npg = new Npgsql_Class();
ArrayList bb = new ArrayList();
string s = string.Empty;
//s = "SELECT ST_AsText(ST_Centroid(ST_Transform(the_geom,900913))) FROM " + input + " LIMIT 1;";
s = "SELECT ST_Extent(the_geom) FROM " + input;
DataTable dt = Data_Import_Transpose.CreateDataTableSelectStatement(s, npg);
s = dt.Rows[0].ItemArray.GetValue(0).ToString();
if (s.Length > 0) { bb = XY(s); }
return bb;
}
public static DataTable CreateDataTableSelectStatement(string sqlstring, Npgsql_Class npgsql_class)
{
string db = "geoneer";
try
{
NpgsqlConnection conn = npgsql_class.GetConnection(db);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(sqlstring, conn);
DataSet ds = new DataSet();
da.Fill(ds);
DataTable dt = ds.Tables[0];
conn.Close();
conn.Dispose();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
///
/// </summary>
/// <param name="db"></param>
/// <param name="cn"></param>
/// <param name="com"></param>
private static void ConnectionCreate(string db, out NpgsqlConnection cn, out NpgsqlCommand com)
{
Npgsql_Class npgsql_class = new Npgsql_Class();
String connectionString = npgsql_class.connectionstring(db);
cn = new NpgsqlConnection(connectionString);
if (cn.State.ToString() == "Open")
{
cn.Close();
cn.Dispose();
}
cn.Open();
com = cn.CreateCommand();
}
/// <summary>
///
/// </summary>
/// <param name="import_table"></param>
/// <param name="uid"></param>
/// <param name="sqlstring"></param>
/// <param name="npgsql_class"></param>
/// <returns></returns>
private static string GetAttributesFromTable(string import_table, String uid, Npgsql_Class npgsql_class)
{
try
{
//http://www.postgresql.org/docs/9.0/static/infoschema-element-types.html
string sqlstring = @"SELECT c.column_name, c.data_type, e.data_type AS element_type " +
"FROM information_schema.columns c LEFT JOIN information_schema.element_types e " +
"ON ((c.table_catalog, c.table_schema, c.table_name, 'TABLE', c.dtd_identifier) " +
"= (e.object_catalog, e.object_schema, e.object_name, e.object_type, e.collection_type_identifier)) " +
"WHERE c.table_name = '" + import_table + "' " +
"ORDER BY c.ordinal_position;";
DataTable dt = CreateDataTableSelectStatement(sqlstring, npgsql_class);
StringBuilder sb = new StringBuilder();
//put fieldnames in attributes from the datareader table with id of parent in the attributes table
Int32 r = 0;
foreach (DataRow dr in dt.Rows)
{
string value_name = dt.Rows[r].ItemArray[0].ToString();
string value_type = dt.Rows[r].ItemArray[1].ToString();
if (value_type.Contains("USER")) { value_type = "geometry"; }
string sqltext = "INSERT INTO \"attributes\" (date_start, date_end, parent, uid, value, description, type) " +
"VALUES (now(), null, '"
+ uid + "', '"
+ System.Guid.NewGuid() + "', '"
+ value_name + "', 'Attribute', '"
+ value_type + "');";
sb.AppendLine(sqltext);
r++;
}
//sb.Append(";");
return sb.ToString();
}
catch (Exception ex)
{
throw ex;
}
}