Postgresql with postGIS and entity structure, CHECK constraint problem

Question

I have a postgresql database with postGIS, and I am using the framework entity 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 execute 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(); } } 

in the database

The following restriction does not apply:

 CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 4326) 

Curious what value the database registered, I tried to have the following two limitations

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

None of them worked. Since they are compared with integer values, at least one of the last three queries must be true.

After some time, I found that the following restriction allows me to insert something with srid = 4326 into the table

 st_srid(the_geom) <= 4326) 

but apparently, for some reason, it accepts everything, both large and smaller srids.

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

Edit: Request

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

returns srid 0. Thus, no matter what srid parameter I specify in the entity infrastructure, it is displayed as 0 in the database. What's happening?

Answer 1

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

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

You are using Entity Developer (Devart Entity model element, * .edml), ​​right?

After installing dotConnect for PostgreSQL v 6.7.287 (or higher), go to Visual Studio> Tools> Entity Developer> Options> Server Settings> PostgreSql and click the Reset button. This is necessary so that new matching rules are added to the list of type matching rules:

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

Now remove the Geo object from your model and drag and drop the geo-table from the menu "Tools"> "Entity Developer"> "Database Explorer" onto the chart surface. Go to Tools> Entity Developer> Model Explorer and make sure the property type is geomentry:

• 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 database activity tracking: http://www.devart.com/dotconnect/postgresql/docs/?dbmonitor.html .

Additional Information:

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

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

Does it help?

Answer 2

If the problem is related to these limitations, I would suggest you recreate the table. In Postgis 2, you can use typed geometry. Try using 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 in order to provide a better response.

I have no experience using the structure used.