2

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?

2
  • 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 7 '15 at 4:17
  • I thin there is a nicer way to do this -- at least now, see my update. – Evan Carroll Jun 3 '18 at 1:06
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);
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 );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.