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?

This has less to do with ST_GEOMETRY and more to do with improper embedded SQL syntax. Far better to build the string in a variable and send that than hard-code an invalid string that won't be seen as two variables. — Vince, Apr 07 '15 at 04:17
I thin there is a nicer way to do this -- at least now, see my update. — Evan Carroll, Jun 03 '18 at 01:06

score 1 · Accepted Answer · edited Apr 13 '17 at 12:34

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);

Evan Carroll · Answer 2 · 2018-06-03T01:31:27.210

1

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 );

edited Jun 03 '18 at 01:31

answered Jun 03 '18 at 00:58

Evan Carroll

7,071
2
32
58

Inserting a PostGIS point using Npgsql

2 Answers2