SQLite Forum

geopoly_json rounding effect
Login

geopoly_json rounding effect

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

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);
2
sqlite> select geopoly_contains_point(geopoly_json('[[0,0],[1,0],[1,1.000001],[0,0]]'), 1.0/3, 1.0/3);
0

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 [source]

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] [source]

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]);
    geojson.push([
      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.