SQLite Forum

Timeline
Login

1 forum post by user adammiller

2021-08-30
01:56 Reply: geopoly_json rounding effect (artifact: 40ebda7d04 user: adammiller)

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.