Inserting a PostGIS point using Npgsql

Question

I'm trying to insert points using C# into a PostGIS / PostgreSQL database. I'm using the Npgsql driver. I suspect this is less a driver issue and more a syntax issue due to me being unfamiliar with ST_GEOMETRY.

Here's the code:

NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;Port=5432;User Id=XXX;Password=XXX;Database=XXX;");

NpgsqlCommand command = new NpgsqlCommand("INSERT INTO points(name, point) VALUES(2, ST_GeomFromText('POINT(:longitude :latitude)', 4326));", conn);

var longitude = command.CreateParameter();
longitude.Direction = ParameterDirection.Input;
longitude.DbType = DbType.Double;
longitude.ParameterName = "longitude";
longitude.Value = fix.Longitude;
command.Parameters.Add(longitude);

var latitude = command.CreateParameter();
latitude.Direction = ParameterDirection.Input;
latitude.DbType = DbType.Double;
latitude.ParameterName = "latitude";
latitude.Value = fix.Latitude;
command.Parameters.Add(latitude);

conn.Open();

var dr = command.ExecuteNonQuery();

conn.Close();

When I run the code, I get a Npgsql.NpgsqlException with an error message ERROR: XX000: parse error - invalid geometry" The Hint property has a message "POINT(:l" <-- parse error at position 8 within geometry.

How do I construct a proper SQL statement to insert ST_GEOMETRY using parameters?

Answer 1

ST_GeomFromText is used to read Well-known text, so you would need to format a string to pass as a parameter. However, this is one of the slowest way to make a point, and is subject to parse errors of the WKT string (which is the error shown in your question).

Use ST_MakePoint, which can directly use numeric parameters.

NpgsqlCommand command = new NpgsqlCommand(
    "INSERT INTO points(name, point) VALUES "
    "(2, ST_SetSRID(ST_MakePoint(:longitude, :latitude), 4326));",
    conn);

Answer 2

NpGSQL supports a direct mapping for Points to the PostGIS Geometry type through the class PostgisGeometry.

With that you should be able

NpgsqlCommand command = new NpgsqlCommand(
  "INSERT INTO points(name, point) VALUES (:name :point);",
   conn
);
command.Add( "name" );
command.Add( myPostgisGeometry );