Postgresql with postGIS and entity framework, CHECK constraint issue

Question

I have a postgresql database with postGIS and I'm using entity framework with dotconnect 6.7 for postgreSQL.

With the following table in my database:

CREATE TABLE geo 
(
  the_geom geometry,
  id integer NOT NULL,
  CONSTRAINT primary_key PRIMARY KEY (id),
  CONSTRAINT enforce_srid_geometry CHECK (st_srid(the_geom) = 4326)
)

and running the following code

class Program {
    static void Main(string[] args) {
        using (test_Model.test_Entities ctx = new test_Model.test_Entities()) {
            var geom = new test_Model.geo();
            geom.id = 0;
            geom.the_geom = DbGeometry.PointFromText("POINT (1 1)", 4326).AsBinary();
            ctx.geos.AddObject(geom);
            ctx.SaveChanges();
    }
}

the following constraint fails in the database

CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326)

Curious for what value the database registered, I tried having the following two constraints

CONSTRAINT enforce_srid_the_geom CHECK(st_srid(the_geom) > 4326)
CONSTRAINT enforce_srid_the_geom CHECK(st_srid(the_geom) < 4326)

Neither worked. Since these are integer values being compared, atleast one of the last three queries should have been true.

After a while I found that the following constraint lets me insert something with srid=4326 into the table

st_srid(the_geom) <= 4326)

but it seems to accept everything, both larger and smaller srids, for some reason.

Is this a bug in postgresql, entity framework or dotconnect?

Edit: The query

SELECT st_srid(the_geom) FROM geo WHERE geo.id == 0

returns the srid 0. So, no matter what srid I give specify in entity framework, it appears as 0 in the database. What is going on?

Answer 1

There is the corresponding geometry type which should be used instead of byte[] on .NET side:

1. .NET 4.0 -> System.Data.Entity.Spatial.DbGeometry in EntityFramework.dll v6
2. .NET 4.5 -> System.Data.Spatial.DbGeometry in System.Data.Entity.dll

You are using Entity Developer (the Devart Entity Model item, *.edml), aren't you?

After installing dotConnect for PostgreSQL v 6.7.287 (or higher), navigate to Visual Studio > Tools > Entity Developer > Options > Servers Options > PostgreSql and press the Reset button. This is necessary so that the new mapping rules were added to your Type Mapping Rules list:

• geography (Server Type) -> Data.Spatial.DbGeography (.NET Type)
• geometry (Server Type) -> Data.Spatial.DbGeometry (.NET Type)

Now remove the Geo entity from your model and drag&drop the geo table from Tools > Entity Developer > Database Explorer to diagram surface. Open Tools > Entity Developer > Model Explorer and make sure that the type of geomentry property is:

• spatial_geometry in SSDL
• Geometry in CSDL

Save the model.

Add this entry to your app.config:

  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="GeoAPI" publicKeyToken="a1a0da7def465678" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.7.1.0" newVersion="1.7.1.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>

Run the following code:

class Program {
    static void Main(string[] args) {

        // new Devart.Data.PostgreSql.PgSqlMonitor() { IsActive = true };

        var config = Devart.Data.PostgreSql.Entity.Configuration.PgSqlEntityProviderConfig.Instance;
        config.SpatialOptions.SpatialServiceType = Devart.Data.PostgreSql.Entity.Configuration.SpatialServiceType.NetTopologySuite;

        using (test_Model.test_Entities ctx = new test_Model.test_Entities()) {
            var geom = new test_Model.geo();
            geom.id = 0;
            geom.the_geom = DbGeometry.PointFromText("POINT (1 1)", 4326);
            ctx.geos.AddObject(geom);
            ctx.SaveChanges();
    }
}

We recommend using the dbMonitor tool to enable tracing of the database activity: http://www.devart.com/dotconnect/postgresql/docs/?dbmonitor.html.

Additional information:

1. The version of SharpMap in your project should be 1.0 RC3 (http://sharpmap.codeplex.com/releases/view/106717). The 1.0 Final version will be supported in dotConnect for PostgreSQL soon
2. Please employ Postgis of the 2.0 (or higher) version. You can check the version by executing "select postgis_version()" in the database

The corresponding Devart documentation is available at http://blogs.devart.com/dotconnect/enhanced-entity-framework-spatials-support-for-oracle-mysql-and-postgresql.html.

Does this help?

Answer 2

If the problem are those constraints I would suggest you to recreate the table. In Postgis 2 you can use the typed geometry. Try use a table like this

CREATE TABLE geo 
(
  the_geom geometry(POINT,4326), -- the constraints are here --
  id integer NOT NULL
);

You should put the database SQLSTATE code returned from the failed query to allow a better response.

I do not have experience on used framework.