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: https://github.com/zzzprojects/Dapper-Plus/issues/88#issuecomment-808301121

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.