Cannot insert Geometry

Cannot insert Geometry

Postby SP4RT4 » Mon 03 Nov 2014 11:13

I'm having problems inserting geometries into a Postgres/Postgis database table. The prepare statement fails with the Exception "Cannot convert value".

I've created a small demo project. All I want to do is following:

Code: Select all
INSERT INTO public.geomtest(description, the_geom)
VALUES ('Insert test', st_geomfromtext('POINT(10 20)', 4326))
RETURNING id


In C# I've done this:

Code: Select all
using (var con = new DevartTestEntities())
{
   var desctest = new Geomtest();
   desctest.Description = "Inserted 1 item without geometry";
   con.Geomtests.AddObject(desctest);
   con.SaveChanges();

   var geomtest = new Geomtest();
   geomtest.Description = "Inserted 1 item with geometry";
   geomtest.TheGeom = DbGeometry.PointFromText("POINT(10 20)", 4326);
   con.Geomtests.AddObject(geomtest);
   con.SaveChanges();
}


The first insert succeeds but the second one throws an exception at the last statement (SaveChanges). I'm tested it in VS2013 with EF6 (and 5) and dotConnect for Postgres 7.3.264 on Postgres 9.3 (and 9.1) with PostGIS 2.1 (and 2.0).

The exception is of type "System.Data.Entity.Core.UpdateException" with the inner exception "System.ArgumentException" and the message "Cannot convert value".

I've compiled some resources for easy reproduction under following dropbox link:
https://dl.dropboxusercontent.com/u/48689748/PostgresGeometryInsertException.zip

This download includes the error log from dbMonitor, the exception details, a demo solution for Visual Studio 2013 and the ddl script for the PostGIS enabled Postgres database.

Many thanks,
Christoph
SP4RT4
 
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Postby MariiaI » Mon 03 Nov 2014 13:50

Thank you for the report on this. We have reproduced this issue. We will investigate it in more details and inform you about the results as soon as possible.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cannot insert Geometry

Postby MariiaI » Tue 04 Nov 2014 06:37

Please add the following entries to the *.config file of your application:
Code: Select all
<section name="Devart.Data.PostgreSql.Entity" type="Devart.Data.PostgreSql.Entity.Configuration.PgSqlEntityProviderConfigurationSection, Devart.Data.PostgreSql.Entity, Version=7.3.264.6, Culture=neutral, PublicKeyToken=09af7300eec23701" />
<Devart.Data.PostgreSql.Entity xmlns="http://devart.com/schemas/Devart.Data.PostgreSql.Entity/1.0">
    <SpatialOptions SpatialServiceType="NetTopologySuite"/>
  </Devart.Data.PostgreSql.Entity>

Or specify it in the code:
Code: Select all
var config = PgSqlEntityProviderConfig.Instance;
config.SpatialOptions.SpatialServiceType = SpatialServiceType.NetTopologySuite;

For more information please refer here.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cannot insert Geometry

Postby SP4RT4 » Tue 04 Nov 2014 08:19

Thank you, this kind of helped :wink: But now I'm stuck with a different problem.

I've added the lines to the app.config (or the code lines, same problem), referenced Devart.Data.PostgreSql.Entity.Spatials and installed NTS via Nuget (including GeoAPI dependency) and now I get the following exception on the first SaveChanges()

System.IO.FileLoadException
Message:
Could not load file or assembly 'GeoAPI, Version=1.7.1.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Source: Devart.Data.PostgreSql.Entity.Spatials
StackTrace:
Code: Select all
    at Devart.Common.Entity.EntityNetTopologySuiteSpatialServices.GeometryFromText(String wellKnownText)
   at System.Data.Entity.Spatial.DbGeometry.FromText(String wellKnownText)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.InitializeSpatialTypeDefaultMap()
   at System.Lazy`1.CreateValue()
   at System.Lazy`1.LazyInitValue()
   at System.Lazy`1.get_Value()
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.TryGetDefaultValue(PrimitiveType primitiveType, Object& defaultValue)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.GetPropagatorResultForPrimitiveType(PrimitiveType primitiveType, PropagatorResult& result)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.Visit(EdmMember node)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.CreateMemberPlaceholder(EdmMember member)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.CreateEntitySetPlaceholder(EntitySet entitySet)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.ExtentPlaceholderCreator.CreatePlaceholder(EntitySetBase extent)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.Visit(DbScanExpression node)
   at System.Data.Entity.Core.Common.CommandTrees.DbScanExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateExpressionVisitor`1.Visit(DbExpression expression)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.Visit(DbProjectExpression node)
   at System.Data.Entity.Core.Common.CommandTrees.DbProjectExpression.Accept[TResultType](DbExpressionVisitor`1 visitor)
   at System.Data.Entity.Core.Mapping.Update.Internal.Propagator.Propagate(UpdateTranslator parent, EntitySet table, DbQueryCommandTree umView)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.<ProduceDynamicCommands>d__a.MoveNext()
   at System.Linq.Enumerable.<ConcatIterator>d__71`1.MoveNext()
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateCommandOrderer..ctor(IEnumerable`1 commands, UpdateTranslator translator)
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.ProduceCommands()
   at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.<Update>b__2(UpdateTranslator ut)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update[T](T noChangesResult, Func`2 updateFunction)
   at System.Data.Entity.Core.EntityClient.Internal.EntityAdapter.Update()
   at System.Data.Entity.Core.Objects.ObjectContext.<SaveChangesToStore>b__35()
   at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesToStore(SaveOptions options, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.<>c__DisplayClass2a.<SaveChangesInternal>b__27()
   at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChangesInternal(SaveOptions options, Boolean executeInExistingTransaction)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges(SaveOptions options)
   at System.Data.Entity.Core.Objects.ObjectContext.SaveChanges()
   at GeomTestNTS.Program.Main(String[] args) in c:\Users\pergerch\Documents\Visual Studio 2013\Projects\GeomTestNTS\GeomTestNTS\Program.cs:line 25
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()


Nuget installed NetTopologySuite 1.13.3.1 and GeoAPI 1.7.3.1, which seems to be the latest version.
Even when I manually install older versions of GeoAPI (1.7.1) and NTS (1.13.0) it brings me the same exception.
SP4RT4
 
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Postby MariiaI » Wed 05 Nov 2014 12:40

NTS Topology Suite 1.13.3 currently is not supported due to the compatibility with SharpMap, which is not upgraded yet. As soon as SharpMap is upgraded, NTS Topology Suite 1.13.3 will be supported. We will inform you when any news regarding this are available.

You can try using NetTopologySuite.dll 1.13.2, GeoAPI.dll 1.7.2. We are sending you a sample project with the assemblies to the e-mail address you have provided in your forum profile; please check that the letter is not blocked by your mail filter. Please try it and tell us if this helps.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cannot insert Geometry

Postby SP4RT4 » Wed 05 Nov 2014 13:18

Many thanks. NTS 1.13.2 solved it!

Nothing else to take care of, except Nuget:
Code: Select all
PM> Install-Package NetTopologySuite -Version 1.13.2
SP4RT4
 
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Postby SP4RT4 » Fri 07 Nov 2014 10:50

This works for PostgreSQL 9.3 but doesn't work for 9.1.
No matter what version of PostGIS I use (I've tested 2.0 and 2.1) it throws following error on a Postgres 9.1 server:

Code: Select all
function st_geomfromewkt(unknown) does not exist


This happens in the Prepare statement that looks like following:

Code: Select all
Prepare:
INSERT INTO sample."location"(the_geom, groupid, featureid)
VALUES (ST_GeomFromEWKT(:p0), :p1, NULL)
RETURNING id

Parameters:
p0 Input VarChar SRID=4326;POINT(10 20)
p1 Input Int 3



EDIT: Found the solution!

Connection string has to look like this
Code: Select all
...Initial Schema=public

no matter in what schema the tables are placed, the PostGIS functions are in the public schema...
SP4RT4
 
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Postby Shalex » Fri 09 Dec 2016 16:01

dotConnect for PostgreSQL v7.7 includes the following features:
  • Signed versions of NetTopologySuite GIS library are supported in EF5/EF6
  • NetTopologySuite 1.14 is supported in EF5/EF6
  • GeoAPI 1.7.4 is supported in EF5/EF6
For more information, refer to http://forums.devart.com/viewtopic.php?f=3&t=34690.
Shalex
Devart Team
 
Posts: 7777
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL