Cannot insert Geometry

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
SP4RT4
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Cannot insert Geometry

Post by 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/486 ... eption.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

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cannot insert Geometry

Post by 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

Post by 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.

SP4RT4
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Post by 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.

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cannot insert Geometry

Post by 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.

SP4RT4
Posts: 4
Joined: Mon 03 Nov 2014 10:14

Re: Cannot insert Geometry

Post by 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

Post by 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...

Shalex
Site Admin
Posts: 8246
Joined: Thu 14 Aug 2008 12:44

Re: Cannot insert Geometry

Post by 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 viewtopic.php?f=3&t=34690.

Post Reply