CODEWITHSUNDEEP
c#postgresqlpostgisnpgsql
Andy Nichols
Andy Nichols

Reputation: 3002

Parse error in PostgreSQL parametrised query from C# but works in pgAdmin III

I am trying to execute a parametrised query from C# and I get the error:

ERROR: XX000: parse error - invalid geometry

with the hint

"POLYGON((:m" <-- parse error at position 11 within geometry

But when I run the query in pgAdmin III replacing the parameters with their values the query works. The code is

command.CommandText = "SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((:minx :miny, :minx :maxy, :maxx :maxy, :maxx :miny, :minx :miny))'), shape) AND area_type_code = :typecode";
command.CommandType = CommandType.Text;
var typeCodeParameter = new NpgsqlParameter
{
    DbType = DbType.String,
    ParameterName = "typecode",
    Value = _typeCode
};
var minxParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "minx",
    Value = _minX
};
var minyParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "miny",
    Value = _minY
};
var maxxParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "maxx",
    Value = _maxX
};
var maxyParameter = new NpgsqlParameter
{
    DbType = DbType.Double,
    ParameterName = "maxy",
    Value = _maxY
};
command.Parameters.Add(typeCodeParameter);
command.Parameters.Add(maxxParameter);
command.Parameters.Add(maxyParameter);
command.Parameters.Add(minxParameter);
command.Parameters.Add(minyParameter);
using (var reader = command.ExecuteReader())
    while (reader.Read())
        areas.Add((string)reader["area_code"]);

and the working query is

SELECT area_code FROM area WHERE ST_INTERSECTS(ST_GeographyFromText('POLYGON((-1.0042576967558934 50.78431084582985, -1.0042576967558934 51.199216033050647, 1.9400782407441057 51.199216033050647, 1.9400782407441057 50.78431084582985, -1.0042576967558934 50.78431084582985))'), shape) AND area_type_code = 'County'

What am I doing wrong? How should I be setting the minx, miny, maxx, maxy parameters?

Upvotes: 0

Views: 1637

Answers (2)

Mike T
Mike T

Reputation: 43642

The correct way to use this parametrised query is to use a function that takes numeric parameters to build an envelope, such as ST_MakeEnvelope(xmin, ymin, xmax, ymax).

SELECT area_code
FROM area
WHERE ST_Intersects(ST_MakeEnvelope(:minx, :miny, :maxx, :maxy)::geography, shape)
   AND area_type_code = :typecode;

Other attempts to format text (WKT) are slower and lossy, since the numbers are converted to text, then parsed back to numbers.

Upvotes: 1

Luaan
Luaan

Reputation: 63752

The problem is that you're separating the coordinates. The parameters aren't just slapped together inside the SQL (it's not just a template replace), it need to be a syntactically valid place to put a parameter. A good sanity check is to try the same query but instead of replacing the parameters with values directly, use procedural SQL and variables.

You will see that the problem is that the ST_GeographyFromText function doesn't expand parameters inside of the string it gets on input - this is an expected behaviour. If you want to use this function, you can't use parameters inside of the string - you still need to just all the values up and pass them as a string - the same thing you've done when you "removed the parameters". The simplest solution would probably be to pass the whole string as a parameter, or just add the parameters to a string in the query (e.g. ST_GeographyFromText('POLYGON((' || cast(:minx ...) || '), ' || ... || ')') etc.)

It seems like you're converting old "concatenate a bunch of strings" SQL with parametrized queries. Do persevere, but you need to take the syntax into account. Just like you can't just put a subquery in a parameter, you can't just separate a single value into two parameters like this.

So you need to either use a better type, or have some helper conversion (like a function that takes two floats, and returns the type you need).

Upvotes: 2

Related Questions