wkb: github.com/lib/pq requires binary_parameters=yes

[vcabbage]

I'm using the wkb package to query PostGIS via github.com/lib/pq. In the default configuration, the queries failed with pq: invalid byte sequence for encoding "UTF8": 0x00. I found that it was necessary to add binary_parameters=yes to the connection string. (Documented here https://godoc.org/github.com/lib/pq#hdr-Data_Types.)

Just wanted to open this issue in case someone else runs into the same problem. Maybe worth a note in the documentation?

Thanks!

[twpayne]

Thanks @vcabbage!

Just to check, is your database geometry column defined as some geometry type? I can see that you might get the above error if your geometry column is stored as a VARCHAR or other string type, but my understanding is that geometries should fall into the catch-all of the lib/pq documentation you reference: "All other types are returned directly from the backend as []byte values in text format.".

[vcabbage]

The column is geography. Here's an example of a query I'm running. If there's a better way to accomplish the results I'd be happy to learn it.

SELECT 
    id, ST_X(location::GEOMETRY) as lon, ST_Y(location::GEOMETRY) as lat
FROM my_table 
WHERE 
    ST_Within(location::GEOMETRY, $1::GEOGRAPHY::GEOMETRY);

(the multiple casts are due SRID mismatches, I'll admit that I'm pretty new to GIS stuff)

[twpayne]

I suspect that you should be using ST_DWithin instead of ST_Within, but I'm not sure.

Generally speaking GEOMETRYs are 2D-coordinates on a flat plane, as you might get from using a map projection. GEOGRAPHYs are lat-longs on a sphere. Converting between the two directly with typecasts is likely to cause significant weirdness.

[vcabbage]

Thanks, the intention is to find all the records within a polygon, (location is a point, the $1 parameter is a polygon). I've been trying geometry and geography fields with various functions to see what performs the best (and is accurate/correct).

As to the original problem, I think the error was caused by the driver passing the data as []bytea instead of raw binary:

the bytea type is returned as []byte

When the binary_parameters connection option is enabled, []byte values are sent directly to the backend as data in binary format.

[twpayne]

Could you post the definition of your GEOGRAPHY column from your CREATE TABLE statement? I haven't needed binary_parameters=yes myself, so I would like to verify that it is really needed before adding it to the documentation.

[vcabbage]

Sure, here's the complete migration I'm using:

CREATE EXTENSION postgis;

CREATE TABLE locations (
	id        TEXT PRIMARY KEY,
	location  GEOGRAPHY(POINT),
	timestamp TIMESTAMP
);

CREATE INDEX locations_location_idx ON
	locations USING GIST (location);
CREATE INDEX locations_timestamp_idx ON
	locations (timestamp);

I tried with the location column as GEOMETRY and it had the same result.

For completeness, I've run this on Postgres 9.6.1 and 9.6.2. Both with a locale of UTF8. When I was tracking down the problem I think I saw some posts saying that it's not an issue if the locale is LATIN1. Perhaps that explains why we've seen different behavior?

[twpayne]

I tried to duplicate this problem in #83, but it seems to me that binary_parameters=yes is not needed. My locale is UTF-8:

# SHOW ALL;
...
 lc_collate                          | en_US.UTF-8                                | Shows the collation order locale.
 lc_ctype                            | en_US.UTF-8                                | Shows the character classification and case conversion locale.
 lc_messages                         | en_US.UTF-8                                | Sets the language in which messages are displayed.
 lc_monetary                         | en_US.UTF-8                                | Sets the locale for formatting monetary amounts.
 lc_numeric                          | en_US.UTF-8                                | Sets the locale for formatting numbers.
 lc_time                             | en_US.UTF-8                                | Sets the locale for formatting date and time values.
...

Thanks for reporting, and please re-open this issue if it looks like something might have been missed.

[twpayne]

Thanks @vcabbage for your patience on this issue. You were right :)

[vcabbage]

@twpayne Not a problem! Thanks for fixing and for writing this library!