Using PostGIS data with Postgres: Column "coordinates" is of type geography but expression is of type text

[ctomichael]

Describe the question

I have an InsightDb instance connected to a PostgreSQL server. I am trying to insert data into a a PostGIS geographic column (a latitude/longitude pair), and I believe I'm using the correct datatypes that npgsql recommends, but I am getting an error:

column "coordinates" is of type geography but expression is of type text

Steps to reproduce (if applicable)

Configure the database connection:

services.AddSingleton<IDatabaseRepository>(sp =>
    {
        NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite();
        var connectionString = sp.GetRequiredService<DatabaseConnectionStringSetting>();
        return new ReliableConnection(new NpgsqlConnection(connectionString.Value)).AsParallel<IDatabaseRepository>();
    }
);

Run the following code:

[Sql(@"
INSERT INTO table (id, coordinates)
VALUES(@id, @coordinates)
")]
Task UpdateTable(Guid id, NetTopologiesSuite.Geometries.Point coordinates)

// elsewhere

UpdateTable(Guid.Empty, new NetTopologySuite.Geometries.Point(new NetTopologySuite.Geometries.Coordinate(1,2)))

• Dotnet version: netcore2
• Database: PostgreSQL
• Library version: 6.2.11

[ctomichael]

Looks like it's being parsed into a WKT format, ie POINT (1 2). I can get it to work by using ST_GeographyFromText(@coordinates) in the SQL text. I think Npgsql is supposed to do that automatically when I do NpgsqlConnection.GlobalTypeMapper.UseNetTopologySuite(), but it seems to be an adequate workaround for the moment.

[jonwagner]

When Insight creates SqlParameters, it needs to select the parametertype field. The library has an understanding of all of the basic types, but not every custom type for every driver. When it doesn't know the type, it defaults to converting the object to a string.

Your workaround is fine for now, but we'll leave this open to add support for the Postgres custom types.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.