# 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 [link] [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 [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])
``````