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:
points table looks like so, and has a couple of hundred thousand rows
CREATE TABLE foo (fooId INTEGER PRIMARY KEY, latitude REAL, longitude REAL);
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;
given an input
lat,lng
pair, compute a geojson buffer (I am usingnodejs
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])