This project has moved. For the latest updates, please go here.

Sharpmap PostgreSQL Hibernate

May 8, 2010 at 5:34 PM
Edited May 8, 2010 at 5:37 PM


I am trying to read a table with polygons from a postgres database. I am using Hibernate for that, but there are no features in my table when i try to instert the Rows from the Datatable into the FeatureDataTable.

Its following code:


var cmd2 = new NpgsqlCommand("SELECT \"Raster_SAY_2000_ATTR_wgs84_UTM47N\".gid, \"Raster_SAY_2000_ATTR_wgs84_UTM47N\".\"MapNo\", \"Raster_SAY_2000_ATTR_wgs84_UTM47N\".the_geom FROM cadastre.\"Raster_SAY_2000_ATTR_wgs84_UTM47N\", cadastre.parcel WHERE st_Contains(parcel.geom,\"Raster_SAY_2000_ATTR_wgs84_UTM47N\".the_geom) = true Or st_overlaps(parcel.geom,\"Raster_SAY_2000_ATTR_wgs84_UTM47N\".the_geom) = true", Config.DbConnection);

  var ad2 = new NpgsqlDataAdapter(cmd2);
  var ds2 = new DataSet();

  var gridLayer = new SharpMap.Layers.VectorLayer("gridLayer");
  catch { }

  FeatureDataTable fdt = new FeatureDataTable(ds2.Tables[0]);
  gridLayer.DataSource = new SharpMap.Data.Providers.GeometryFeatureProvider(fdt);

When i debug the project i can see that there are 3 rows in my dataset, but nothing in my featuredatatable. There is no statement in my log file in postgres, so i dont think that it is because of the database. When i run the sql statement in pgadmin it leads to a result of three rows, the same like in the dataset.

 Is it possible that there are three rows in the dataset, but no entries? Thats everything i could imagine. Any other ideas, solutions, tips?

May 8, 2010 at 11:52 PM

hello annahockf,

the constructor of FeatureDataTable does not convert postgres/postgis geometries to geometry objects by itself.

If you want to do something like that, you'll have to do these two steps:

- force postgres/postgis to return wkb representation for geometry objects (i. e. ... , AsBinary(the_geom) AS the_geom FROM ...)

- Write function to clone your result DataTable and do the conversion step, perhaps even a constructor for FeatureDataTable, something like


        /// <summary>
        /// Intitalizes a new instance of the FeatureDataTable class with the specified table
        /// </summary>
        /// <param name="table">input table</param>
        /// <param name="oidColumn">name of the object id column</param>
        /// <param name="geomAsBinaryColumn">name of the column that holds wkb representation of geometry</param>
        public FeatureDataTable(DataTable table, String oidColumn, String geomAsBinaryColumn)
            if ( table is FeatureDataTable)
                throw new ArgumentException("Is already FeatureDataTable", "table");

            if (!table.Columns.Contains(oidColumn))
                throw new ArgumentException("oidColumn");
            if (!table.Columns.Contains(geomAsBinaryColumn))
                throw new ArgumentException("geomAsBinaryColumn");

            foreach (DataColumn column in table.Columns)
                if (column.ColumnName != geomAsBinaryColumn)
                    Columns.Add(column.ColumnName, column.DataType);

            //Setting primary key
            PrimaryKey = new DataColumn[] {Columns[oidColumn]};

            foreach (DataRow row in table.Rows)
                FeatureDataRow fdr = NewRow();
                foreach (DataColumn column in Columns)
                    fdr[column.ColumnName] = row[column.ColumnName];
                fdr.Geometry = Converters.WellKnownBinary.GeometryFromWKB.Parse(row[geomAsBinaryColumn] as byte[]);

I tested this with SpatiaLite provider and it works.

Hth FObermaier