SQLite Forum

using geopoly to find rows within a buffer around an input point

using geopoly to find rows within a buffer around an input point

(1.3) By punkish on 2020-08-10 09:23:52 edited from 1.2 [source]

I have geographic points in lat,lng and I want to be able to SELECT rows within a buffer radius of say, 10 kms, of a given lat,lng. Here is how I am going about it:

  1. points table looks like so, and has a couple of hundred thousand rows

    CREATE TABLE foo (fooId INTEGER PRIMARY KEY, latitude REAL, longitude REAL);
  2. create a virtual geopoly table

    CREATE VIRTUAL TABLE fooLoc USING geopoly(fooId);
    INSERT INTO fooLoc(fooId, _shape) SELECT fooId, '[[' || longitude || ',' || latitude || '],[' || longitude || ',' || latitude || '],[' || longitude || ',' || latitude || '],[' || longitude || ',' || latitude || ']]' AS _shape FROM foo;
  3. given an input lat,lng pair, compute a geojson buffer (I am using nodejs to do all this work)

    const geojson = require('geojson')
    const buffer = require('@turf/buffer')
    const Database = require('better-sqlite3')
    const db = new Database('mydata.sqlite')
    const point = {
        latitude: -4.5077777,
        longitude: -56.289722
    const gj = geojson.parse(point, { Point: ['latitude', 'longitude'] })
    const buffered = buffer(gj, 10, {units: 'kilometers'})
    const p = JSON.stringify(buffered.geometry.coordinates)
    // Now I use the buffer string in my `SELECT` statement
    const sql = "SELECT fooId, latitude, longitude FROM foo WHERE geopoly_contains_point(@p, longitude, latitude) IS NOT NULL"
    const records = db.prepare(sql).all({ p: p })

    But I get nothing. Just an empty array. Though I have shown the js code above, I've tried this directly in the SQLite console and the same result… an empty array.

What am I doing wrong?

(2) By punkish on 2020-08-09 19:00:25 in reply to 1.2 [link] [source]

never mind, I figured it out. The buffer produces a multipolygon (even though it is a simple polygon, so it is represented by an array of array of coordinates. I have to use the one and only poly in that array. The following does the trick

const p = JSON.stringify(buffered.geometry.coordinates[0])