SQLite Forum

geopoly_json rounding effect

geopoly_json rounding effect

(1.2) By Boris (boris.gontar) on 2020-04-29 11:25:41 edited from 1.1 [link]

Result of geopoly\_contains\_point may change when geopoly\_json(_shape_) instead of the _shape_ itself it used.

sqlite> select geopoly_contains_point('[[0,0],[1,0],[1,1.000001],[0,0]]', 1.0/3, 1.0/3);
sqlite> select geopoly_contains_point(geopoly_json('[[0,0],[1,0],[1,1.000001],[0,0]]'), 1.0/3, 1.0/3);

No wonder because geopoly\_json in the second query returns '[[0,0],[1,0],[1,1],[0,0]]'. The problem is that rounding of geopoly\_json is inconsistent with rounding used internally (is it geopoly\_blob?).

Is it possible to increase precision of geopoly\_json to avoid loss of precision in conversion?

(2) By Richard Hipp (drh) on 2020-04-30 15:43:36 in reply to 1.2

The current implementation of GeoPoly limits precision to 32-bit floating
point numbers.  There is no work-around.

(3) By Adam Miller (adammiller) on 2021-08-30 01:56:16 in reply to 2 [link]

It appears that the geopoly_json function does round numbers in its output JSON pretty dramatically, and is not the same as the precision loss from the underlying 32-bit float data structure.

FWIW, I was able to work around this by requesting the raw BLOB from the database instead of JSON, and converting it to coordinates in my application. 

It would be wonderful for the JSON output of geopoly_json to match `Math.fround()` instead of (apparently) truncating at ~3-4 decimal points.

I'm in Typescript land, so my conversion script looks like this:

function convert(buff: Buffer): [number, number][] {
  // The first byte of the header is a flag byte. The least significant bit of the flag byte determines
  // whether the coordinate pairs that follow the header are stored big-endian or little-endian.
  const littleEndian = !!(buff[0] % 2);
  const lat = new DataView(new ArrayBuffer(4));
  const lng = new DataView(new ArrayBuffer(4));
  const geojson = [];
  for (let i = 4; i < buff.length; i += 8) {
    lat.setUint8(0, buff[i]);
    lat.setUint8(1, buff[i + 1]);
    lat.setUint8(2, buff[i + 2]);
    lat.setUint8(3, buff[i + 3]);
    lng.setUint8(0, buff[i + 4]);
    lng.setUint8(1, buff[i + 5]);
    lng.setUint8(2, buff[i + 6]);
    lng.setUint8(3, buff[i + 7]);
      lat.getFloat32(0, littleEndian), 
      lng.getFloat32(0, littleEndian),
  // Geopoly does not include the duplicate end point. Add it back in.
  geojson.push([geojson[0][0], geojson[0][1]]);
  return geojson;

Hope this helps someone Googling for answers in the future, and @drh let me know if I can help at all.