postgresql geometry

[znyet]

dear!

var a = new A
 {
          id=1,
          name = "lina",
          geom = "LINESTRING(0 0,1 1,2 1,2 2)"
 };
 postgresql13

ado.net or dapper I can use string type【geom = "LINESTRING(0 0,1 1,2 1,2 2)"】to insert geo field to the table
but use z.dapper.plus is error
Could not find PostgreSQL type geometry!
can you switch geometry to string,then it will work fine!

[JonathanMagnan]

Hello @znyet ,

Please see the following answer for this kind of custom type: #88 (comment)

Let me know if you successfully make your scenario work.

Best Regards,

Jon

[znyet]

Hi JonathanMagnan !
geometry is postgis type;

1、
CREATE TABLE geometries (name varchar, geom geometry);

2、
INSERT INTO geometries VALUES
('Point', 'POINT(0 0)'),
('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

you can see we can insert string type to geometry field;
i use dapper is successfully!

but use z.dapper.plus, it is error
Could not find PostgreSQL type geometry!
so i think geometry in c# we can using string to insert it

[JonathanMagnan]

Hello @znyet ,

Here is the main part of how to add some type not yet supported by our library:

DapperPlusManager.AddCustomSupportedType(typeof(NetTopologySuite.Geometries.Point));

Here is a full example:

using System;
using System.Collections.Generic;
using System.Text;
using Z.Dapper.Plus;
using Dapper;
using NetTopologySuite.Geometries;
using Npgsql;
using System.Linq;

namespace Lab.PostgreSQLCore
{
	class Request_SpatialType
	{ 
		public static void Execute()
		{  
			using (var connectionMaster = new Npgsql.NpgsqlConnection(My.ConnectionMaster))
			{

				var commandText = connectionMaster.Query<string>(@"SELECT 1 FROM pg_database WHERE datname = 'dapperpluslab'").FirstOrDefault();

				if (!string.IsNullOrEmpty(commandText))
				{
					using (var command = connectionMaster.CreateCommand())
					{
						connectionMaster.Open();
						command.CommandText = @"DROP DATABASE dapperpluslab";
						command.ExecuteNonQuery();
						connectionMaster.Close();
					}
				}

				using (var command = connectionMaster.CreateCommand())
				{
					connectionMaster.Open();
					command.CommandText = @"CREATE DATABASE dapperpluslab";
					command.ExecuteNonQuery();
					connectionMaster.Close();
				}


			}
			using (var connectionTable = new Npgsql.NpgsqlConnection(My.Connection))
			{
				using (var command = connectionTable.CreateCommand())
				{
					connectionTable.Open();
					command.CommandText =
				@"CREATE SEQUENCE entitysimples_id_seq;  ";
					command.ExecuteNonQuery();
					connectionTable.Close();
				}
				using (var command = connectionTable.CreateCommand())
				{
					connectionTable.Open();
					command.CommandText =
				@"CREATE EXTENSION postgis;";
					command.ExecuteNonQuery();
					connectionTable.Close();
				}
				using (var command = connectionTable.CreateCommand())
				{
					connectionTable.Open();
					command.CommandText =
				@" 

CREATE TABLE public.""geometries""
(
	""ID"" integer NOT NULL DEFAULT nextval('""entitysimples_id_seq""'),
 geom geometry,
	CONSTRAINT ""PK_EntitySimples"" PRIMARY KEY(""ID"")
)
WITH(
	OIDS = FALSE
)
TABLESPACE pg_default;

				ALTER TABLE public.""geometries""
	OWNER to postgres;";
					command.ExecuteNonQuery();
					connectionTable.Close();
				}
			}

			var connection = new Npgsql.NpgsqlConnection(My.Connection);
			connection.Open(); 
			connection.TypeMapper.UseNetTopologySuite();

			DapperPlusManager.AddCustomSupportedType(typeof(NetTopologySuite.Geometries.Point));
			DapperPlusManager.Entity<EntitySimple>().Table("geometries").Identity(x => x.ID);
			var list = new List<EntitySimple>();
			for (int i = 0; i < 3; i++)
			{
				list.Add(new EntitySimple()
				{
					geom = new Point(14, 49)
				});
			}
			
			connection.BulkInsert(list);
			connection.Close();
		}

		public class EntitySimple
		{
			public int ID { get; set; }
			public NetTopologySuite.Geometries.Point geom { get; set; }

		}
	}
}

If you still have a problem, just provide us a runnable project with the minimum code to reproduce it and we will try to update it to let you know what modification is required.

Best Regards,

Jon

[znyet]

public class EntitySimple
{
public int ID { get; set; }
public string geom { get; set; }=“POINT(0 0)”
}

thank you JonathanMagnan!
in my project c# i use string for geometry
because string can insert into geometry field；
i don't use NetTopologySuite.Geometries.Point
geometry like json and jsonb;
@geo::geometry @jsonText::json @jsonbText::jsonb
then in c# we can use string to insert

use GeoJSON.Net can insert it too;
Position position = new Position(51.899523, -2.124156);
Point point = new Point(position);
string json = JsonConvert.SerializeObject(point);
EntitySimple.geom = json;

so in my project string is good for geometry
EntitySimple.geom = “POINT(0 0)”;
or
EntitySimple.geom = '{"type":"Point","coordinates":[0,0]}';
or
EntitySimple.geom = '{"type":"LineString","coordinates":[[0,0],[1,1],[2,1],[2,2]]}';
or
EntitySimple.geom = '{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}'
and more......

postgresql geometry field support “POINT(0 0)” or jsontext like '{"type":"Point","coordinates":[0,0]}' to insert

i will switch string to NetTopologySuite.Geometries.Point;
thank you！

[JonathanMagnan]

Awesome ;) Let us know if you need more help

[znyet]

thank you JonathanMagnan!

because i use Npgsql.dll only!

var connection = new Npgsql.NpgsqlConnection(My.Connection);
connection.Open();
//connection.TypeMapper.UseNetTopologySuite(); //don't use it

i don't use Npgsql.NetTopologySuite.dll

if only use Npgsql.dll and if we want to insert geometry value

public class EntitySimple
{
public int ID { get; set; }
public string geom { get; set; }=“POINT(0 0)” or '{"type":"Point","coordinates":[0,0]}'
}

as you see string type is ok, and it can work, and insert success.

i am wrong, i use string replace of Point、LineString、Polygon....

default is public.geometry but z.dapper.plus is nettopologysuite.geometries.geometry

and i must use Npgsql.dll and Npgsql.NetTopologySuite.dll to insert geometry field.

thank you for your help.