Using PgSqlLoader to populate a Postgis geometry

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
gajus
Posts: 4
Joined: Tue 25 Sep 2018 00:12

Using PgSqlLoader to populate a Postgis geometry

Post by gajus » Tue 10 Nov 2020 13:55

Hi,

I'm trying to populate a table in Postgres containing a Postgis geometry datatype, essentially this is the code:

Code: Select all

using System.Data.Entity.Spatial;

public class Table {
	public DbGeometry ProjectedGeom2D
	{
		get;
		set;
	}
} 

public void WriteToTable() {
	Table table = new Table()
	{
		ProjectedGeom2D = DbGeometry.FromText("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))", 4326)
	};

	var conn = new PgSqlConnection("user id=postgres;Password=postgres;host=localhost;database=database;");
	
	if (conn.State == ConnectionState.Closed)
		conn.Open();

	PgSqlLoader modelLoader = new PgSqlLoader();	
	modelLoader.Connection = conn;	
	modelLoader.TableName = "Models";	
	modelLoader.CreateColumns();	
	modelLoader.Open();
	modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
	modelLoader.Close();
}
This will produce the following exception:

Error = {ERROR: XX000: parse error - invalid geometry}

COPY Models, line 1, column projected_geom2_d: "\001\003\000\000\000\001\000\000\000\005\000\000\000\000\000\000\000\000\000>@\000\000\000\000\000\0..."

"\0" <-- parse error at position 2 within geometry

Do you guys have any idea how to solve this?

Kinds regards Gajus.

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

Re: Using PgSqlLoader to populate a Postgis geometry

Post by Shalex » Wed 11 Nov 2020 19:58

Please add this line to your code:

Code: Select all

        modelLoader.Mode = PgSqlLoaderMode.Binary;
Refer to https://www.devart.com/dotconnect/postg ... ~Mode.html.

gajus
Posts: 4
Joined: Tue 25 Sep 2018 00:12

Re: Using PgSqlLoader to populate a Postgis geometry

Post by gajus » Thu 12 Nov 2020 08:50

Hi Shalex, thanks for your reply.

Your solution seems to get me a step further. Although I think it has some ramifications for other properties I'm trying set.

In short my table contains the following datatypes:
  • guids
    strings
    doubles
    jsonb
    geometry
When setting the I receive the following error on my JSONB column: {ERROR: XX000: unsupported jsonb version number 91}

More completely, my code looks something like this:

Code: Select all

using System.Data.Entity.Spatial;
public class Table
{
	public DbGeometry ProjectedGeom2D { get; set; }

	[Column(TypeName = "jsonb")]
	public string JsonbType { get; set; }

	public Guid Id { get; set; }

	public string StringType { get; set; }

	public double DoubleType { get; set; }
}

public void WriteToTable()
{
	Table table = new Table()
	{
		ProjectedGeom2D = DbGeometry.FromText("POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10))", 4326),
		DoubleType = 1.0,
		JsonbType = "[{\"type\":\"Cylinder\",\"radius\": 0.346,\"startPoint\": {\"x\": 762893.708,\"y\": 3352038.011,\"z\": 23.902},\"endPoint\": {\"x\": 762893.544,\"y\": 3352038.335,\"z\": 23.901},\"center\": {\"x\": 762893.626,\"y\": 3352038.173,\"z\": 23.901}}]",
		StringType = "a string",
		Id = Guid.NewGuid()                
	};

	var conn = new PgSqlConnection("user id=postgres;Password=postgres;host=localhost;database=database;");

	if (conn.State == ConnectionState.Closed)
		conn.Open();

	PgSqlLoader modelLoader = new PgSqlLoader();
	modelLoader.Mode = PgSqlLoaderMode.Binary;
	modelLoader.Connection = conn;
	modelLoader.TableName = "Models";
	modelLoader.CreateColumns();
	modelLoader.Open();
	
	// In reality i have a substantial for loop
	for (int i = 0; i < 10; i++) {
		modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
		modelLoader.SetValue("double_type", table.DoubleType);
		modelLoader.SetValue("string_type", table.StringType);
		modelLoader.SetValue("id", table.Id);
		modelLoader.SetValue("jsonb_type", table.JsonbType);
		modelLoader.NextRow();
	}
	modelLoader.Close();
}
I am using:
Devart.Data runtime version: v2.0.50727
Devart.Data.PostgreSql runtime version: v2.0.50727
Devart.Data.PostgreSql.Entity.EF6 runtime version: v4.0.30319
Devart.Data.PostgreSql.Entity.Spatials.EF6 runtime version: v4.0.30319

I have tried toggling the modelLoader.Mode in the for loop between text and binary before setting the jsonb and geometry respectively. This doesnt seem to help. Do you have any suggestions on how to get both a geometry and jsonb persisted in the same loop?

Thanks in advance, Gajus

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

Re: Using PgSqlLoader to populate a Postgis geometry

Post by Shalex » Fri 13 Nov 2020 10:32

We have reproduced the issue with jsonb in the PgSqlLoaderMode.Binary mode:

Code: Select all

modelLoader.Mode = PgSqlLoaderMode.Binary;
...
modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
modelLoader.SetValue("jsonb_type", table.JsonbType);
modelLoader.Close();

->

Devart.Data.PostgreSql.PgSqlException
  Message=unsupported jsonb version number 91
We will investigate it and notify you about the result.

As a workaround, please use PgSqlLoaderMode.Text:

Code: Select all

//modelLoader.Mode = PgSqlLoaderMode.Binary;
modelLoader.Mode = PgSqlLoaderMode.Text;
...
//modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsBinary());
modelLoader.SetValue("projected_geom2_d", table.ProjectedGeom2D.AsText());
modelLoader.SetValue("jsonb_type", table.JsonbType);
modelLoader.Close();

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

Re: Using PgSqlLoader to populate a Postgis geometry

Post by Shalex » Fri 04 Dec 2020 18:38

The bug with loading data into the JSONB column via PgSqlLoader in the PgSqlLoaderMode.Binary mode is fixed in v7.20.1782: viewtopic.php?f=3&t=44279.

Post Reply