Cause: SDE and pgsql11 were initially used for development, but later the database was upgraded to pgsql14. Because SDE and PgSQL versions did not match, it was unable to import element data and display data. Database upgrading was inevitable, so we had to find another solution.

reference material: Spatial Database for Postgres and ArcGis users: how to choose

Additional: zigGis, GDAL. The legendary zigGis is quite good. It can directly display the GIS type in postgis into the control of ArcGis, but the version is complex, and it may also be designed to be mismatched. I'll take a look at the knowledge system in this regard later.

Pgsql+postgis uses: Use of PostgreSQL+PostGIS (Reprint) - traceless guest - blog Park

PostGIS tutorial 1: introduction to PostGIS - Zhihu

Implementation steps:

postgis provides many functions to convert wkb to its geometry type, as well as functions to convert geometry to binary. Of course, what is more exciting is that there is a conversion for standard wkb in ArcGis. In this way, it can be imagined that this scheme is feasible.

Step 1: read GIS data from postgreSQL through postgis and display it in AxMapControl

1. C# connect to database pg

Note: it should be noted that since the support for wkb in ArcEngine is the conversion between bytes [], the function of postgis is used to convert GIS data into bytes [], which is written asbinary(the_geom)

2. Get IGeometry from the obtained byte [] (i.e. wkb data) through the interface of ArcEngine, as shown below:

 IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
 factory.CreateGeometryFromWkbVariant(wkb, out geom, out countin);

3. Display IGeometry in AxMapControl.

Step 2: modify the GIS data displayed from pg in the previous step and save it back to pg

1. After modifying the specified element, get wkb through geometry

To save the modification operation, you need to turn the geometry in the specified element back to wkb again, and then modify the specified record in pg through SQL statement. The idea is very simple. The problem is to splice the SQL string, because arcengine still gets a byte [] through geometry, which can not be spliced into SQL statement.

2. The ArcEngine interfaces used are as follows:

IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
byte[] geoBytes = factory.CreateWkbVariantFromGeometry(geometry) as byte[];

3. When splicing SQL, transcode the byte [] array above

In fact, what postgis handles and stores in pg is a long string of hexadecimal strings. I believe you can see it when you open the database. If you want to splice the SQL strings, you have to do this: geomfromwkb (decode ('"+ geoByteStr +",'hex'), in which geoByteStr is a byte [] converted to hexadecimal string

The above two steps have been completed and can be realized. In this way, we can also define the data table by ourselves and operate GIS data and attributes more flexibly.

data

Read pgsql database

namespace WHUGIS.Classes
{
    class DAO
    {

        private static string connectionString = "User ID=postgres;Password=admin;Server=localhost;Port=5432;Database=GIS_engine;";
        public DAO()
        {

        }
        public static Byte[] executeRouteQuery(string sqlstr)
        {
            NpgsqlConnection sqlConn = new NpgsqlConnection(connectionString);
            try
            {
                sqlConn.Open();
                NpgsqlCommand objCommand = new NpgsqlCommand(sqlstr, sqlConn);
                Byte[] routeWKB = (byte[])objCommand.ExecuteScalar();
                return routeWKB;
            }
            catch(Exception ee)
            {
                MessageBox.Show(ee.Message);
                return null;
            }
            finally
            {
                sqlConn.Close();
            }
            
        }

    }
}

ArcgisEngine Igeometry and WKB conversion

using System;
using System.Collections.Generic;
using System.Text;
using GisSharpBlog.NetTopologySuite.IO;
using ESRI.ArcGIS.Geometry;

namespace Utils
{
    /// <summary>
    /// This class is used to convert a GeoAPI Geometry to ESRI and vice-versa.
    /// It can also convert a ESRI Geometry to WKB/WKT and vice-versa.
    /// </summary>
    public static class Converters
    {

        public static byte[] ConvertGeometryToWKB(IGeometry geometry)
        {
            IWkb wkb = geometry as IWkb;
            ITopologicalOperator oper = geometry as ITopologicalOperator;
            oper.Simplify();

            IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
            byte[] b = factory.CreateWkbVariantFromGeometry(geometry) as byte[];
            return b;
        }


        public static byte[] ConvertWKTToWKB(string wkt)
        {
            WKBWriter writer = new WKBWriter();
            WKTReader reader = new WKTReader();
            return writer.Write(reader.Read(wkt));
        }

        public static string ConvertWKBToWKT(byte[] wkb)
        {
            WKTWriter writer = new WKTWriter();
            WKBReader reader = new WKBReader();
            return writer.Write(reader.Read(wkb));
        }

        public static string ConvertGeometryToWKT(IGeometry geometry)
        {
            byte[] b = ConvertGeometryToWKB(geometry);
            WKBReader reader = new WKBReader();
            GeoAPI.Geometries.IGeometry g = reader.Read(b);
            WKTWriter writer = new WKTWriter();
            return writer.Write(g);
        }

        public static IGeometry ConvertWKTToGeometry(string wkt)
        {
            byte[] wkb = ConvertWKTToWKB(wkt);
            return ConvertWKBToGeometry(wkb);
        }

        public static IGeometry ConvertWKBToGeometry(byte[] wkb)
        {
            IGeometry geom;
            int countin = wkb.GetLength(0);
            IGeometryFactory3 factory = new GeometryEnvironment() as IGeometryFactory3;
            factory.CreateGeometryFromWkbVariant(wkb, out geom, out countin);
            return geom;
        }


        public static IGeometry ConvertGeoAPIToESRI(GeoAPI.Geometries.IGeometry geometry)
        {
            WKBWriter writer = new WKBWriter();
            byte[] bytes = writer.Write(geometry);
            return ConvertWKBToGeometry(bytes);
        }

        public static GeoAPI.Geometries.IGeometry ConvertESRIToGeoAPI(IGeometry geometry)
        {
            byte[] wkb = ConvertGeometryToWKB(geometry);
            WKBReader reader = new WKBReader();
            return reader.Read(wkb);
        }
    }
}