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?

Diophantus

postgresql 6.7? Doesn't even exist. Or is that dotconnect 6.7? Then what are the Postgres and PostGis versions? – Pugliese

Fixed the wording. PostgreSQL 9.2.4 and PostGis 2.0.3 r11132. – Diophantus

Well, at least on postgresql 9.1 and postgis 1.5, we are seeing st_srid = 4326; you'd want to use query logging and see whatever is being spit out by dotconnect – Hammers

I don't know much about postgis, but the 3 constraints all failing suggests st_srid could be returning null – Exhibitionist

Not sure why st_srid(the_geom) <= 4326) would evaluate to true if st_srid evaluates to null? – Diophantus

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?

Sissified

Hm, I'm getting "function st_geomfromewkt(unknown) does not exist" at context.SaveChanges(). I have postgis 2.0 and 1.0 RC3 SharpMap. – Diophantus

Have you followed all the steps from our reply? If this doesn't help, please send us a small test project for reproducing the error: devart.com/company/contactform.html . – Sissified

I did follow all the steps to the best of my ability. Test project has been sent. – Diophantus

It works in our environment. Could you please specify the exact text of the error message you are getting? If the error text is malformed due to encodings mismatch, add the "Unicode=true;" entry to your connection string (via Tools > Entity Developer > Database Explorer > Edit Connection Properties) and save the model to update your App.config. Also please turn on the dbMonitor tool to find out the SQL statement which fails to execute. – Sissified

According to dbMonitor, the error is "Function st_geomfromwkt(unknown) does not exist. The query that fails is INSERT INTO x.y(x, y, start_datetime, end_datetime, the_geom) VALUES (:p0, :p1, :p2, :p3, ST_GeomFromEWKT(:p4)) RETURNING idadapter) Where :p4 is p4 Input VarChar SRID=4326;POINT(1 1) – Diophantus

Have you created the postgis extension in your current database? CREATE EXTENSION postgis; postgis.net/docs/manual-2.0/… – Sissified

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.

Maraud