knex-spatial-plugin
v2.4.0
Published
A Knex plugin for simplified geometric & geospatial operations in Postgres.
Downloads
410
Maintainers
Readme
Knex Spatial Plugin
A Knex plugin for easy operations on geometric & geospatial data in Postgres.
Featuring a fluent, expressive and natural API design.
⚠️ Warning: This plugin is still in early development. Sql injection prevention being validated. Please report any issues you find. ⚠️
- Get Started
- Methods
selectDistance
whereDistance
&whereDistanceWithin
selectArea
selectCentroid
selectConvexHull
selectDifference
selectIntersection
selectEnvelope
selectLength
selectSymDifference
selectUnion
whereContains
whereContainsProperly
whereCovers
whereCoveredBy
whereCrosses
whereDisjoint
whereEquals
whereIntersects
whereOverlaps
whereRelate
whereTouches
whereWithin
- Expressive Shape API
- SQL Function Syntax Builder API
- References
- TODO
- Dev Notes
Get Started
npm install knex-spatial-plugin
# OR
yarn add knex-spatial-plugin
Once installed, add the plugin to your Knex instance:
import Knex from 'knex';
import KnexSpatialPlugin from 'knex-spatial-plugin';
export const db = Knex(config);
// Simply call the plugin with your Knex instance
KnexSpatialPlugin(db);
Methods
selectDistance
Add a computed column, distance
(in meters) based on the given lat
& lon
values.
Uses the ST_Distance
function.
Note: Intelligently handles undefined
lat & lon values by returning the query without modification.
import { db } from './knex';
export function findNearbyLocations({ lat, lon }) {
// Get locations within 10Km of input location, **including** the distance in the results
return db('locations')
.select('id', 'name')
.selectDistance('location', { lat, lon })
.orderBy('distance');
}
select "id",
"name",
ST_Distance ("location", ST_Point (-104.128, 39.87)) / 1609.344 AS "distance"
from "locations"
where "distance" < 10000
order by "distance" asc
id name distance
1 Denver 0
2 Boulder 38.5
whereDistance
& whereDistanceWithin
id name distance
1 Denver 0
2 Boulder 38.5
Uses the ST_Distance
& ST_DWithin
function.
Note: Intelligently handles undefined
lat & lon values by returning the query without modification.
export function findNearbyLocations({ lat, lon }) {
// Get locations within 10Km of input location, without including the distance in the results
return db('locations')
.select('id', 'name')
.whereDistanceWithin('location', { lat, lon, radius: 10000 });
}
selectArea
db('world_countries')
.select('country_name')
.selectArea('country_border', 'area_in_meters');
// SELECT "country_name",
// ST_Area("country_border") AS "area_in_meters"
// FROM "world_countries";
| country_name
| area_in_meters
|
| -------------- | ----------------- |
| England | 130,279,000,000 |
| Ireland | 70,278,000,000 |
| South Africa | 1,221,037,630,000 |
| United States | 9,147,420,000,000 |
db('world_countries')
.select('country_name')
.selectArea('country_border', 'area_in_km2', 'kilometers');
// SELECT "country_name",
// ST_Area("country_border") / 1000 AS "area_in_km2"
// FROM "world_countries";
| country_name
| area_in_km2
|
| -------------- | ------------- |
| England | 130,279,000 |
| Ireland | 70,278,000 |
| South Africa | 1,221,037,630 |
| United States | 9,147,420,000 |
db('world_countries')
.select('country_name')
.selectArea('country_border', 'area_in_miles', 'miles')
.orderBy('area_in_miles', 'desc');
// SELECT "country_name",
// ST_Area("country_border") / 1609.344 AS "area_in_miles"
// FROM "world_countries"
// ORDER BY "area_in_miles" DESC;
| country_name
| area_in_miles
|
| -------------- | --------------- |
| United States | 5,650,000 |
| South Africa | 754,000 |
| England | 80,700.8 |
| Ireland | 43,500.5 |
selectCentroid
db('world_countries')
.select('country_name')
.selectCentroid('country_border', 'centroid');
// SELECT "country_name",
// ST_Centroid("country_border") AS "centroid"
// FROM "world_countries";
| country_name
| centroid
|
| -------------- | ------------------------------ |
| England | POINT(-1.474054 52.795479)
|
| Ireland | POINT(-8.137935 53.175503)
|
| South Africa | POINT(25.083901 -29.000341)
|
| United States | POINT(-112.599438 45.705628)
|
selectConvexHull
db('world_countries')
.select('country_name')
.selectConvexHull('country_border', 'convex_hull');
// SELECT "country_name",
// ST_ConvexHull("country_border") AS "convex_hull"
// FROM "world_countries";
| country_name
| convex_hull
|
| -------------- | ------------------------------------------------------------------------------------------------------------------------ |
| England | POLYGON((-5.270157 50.056137,-5.270157 55.811741,1.762726 55.811741,1.762726 50.056137,-5.270157 50.056137))
|
| Ireland | POLYGON((-10.4786 51.4457,-10.4786 55.3878,-5.3319 55.3878,-5.3319 51.4457,-10.4786 51.4457))
|
| South Africa | POLYGON((16.344976 -34.819168,16.344976 -22.125026,32.895474 -22.125026,32.895474 -34.819168,16.344976 -34.819168))
|
| United States | POLYGON((-124.731422 24.955967,-124.731422 49.371735,-66.969849 49.371735,-66.969849 24.955967,-124.731422 24.955967))
|
selectDifference
db('world_countries')
.select('country_name')
.selectDifference(
'country_border',
{ lat: 39.87, lon: -104.128, radius: '10mi' },
'difference',
)
.where({ country_name: 'United States' });
// SELECT "country_name",
// ST_Difference("country_border", ST_Buffer('Point(-104.128, 39.87)'::geography, 10 * 1609.344)) AS "difference"
// FROM "world_countries"
// WHERE "country_name" = 'United States';
| country_name
| difference
|
| -------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| United States | MULTIPOLYGON(((-124.731422 24.955967,-124.731422 49.371735,-66.969849 49.371735,-66.969849 24.955967,-124.731422 24.955967)))
|
selectIntersection
db('world_countries')
.select('country_name')
.selectIntersection(
'country_border',
{ lat: 39.87, lon: -104.128, radius: '10mi' },
'intersection',
)
.where({ country_name: 'United States' });
// SELECT "country_name",
// ST_Intersection("country_border", ST_Buffer('Point(-104.128, 39.87)'::geography, 10 * 1609.344)) AS "intersection"
// FROM "world_countries"
// WHERE "country_name" = 'United States';
| country_name
| intersection
|
| -------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| United States | MULTIPOLYGON(((-124.731422 24.955967,-124.731422 49.371735,-66.969849 49.371735,-66.969849 24.955967,-124.731422 24.955967)))
|
selectEnvelope
db('world_countries')
.select('country_name')
.selectEnvelope('country_border', 'envelope');
// SELECT "country_name",
// ST_Envelope("country_border") AS "envelope"
// FROM "world_countries";
| country_name
| envelope
|
| -------------- | ------------------------------------------------------------------------------------------------------------------------ |
| England | POLYGON((-5.270157 50.056137,-5.270157 55.811741,1.762726 55.811741,1.762726 50.056137,-5.270157 50.056137))
|
| Ireland | POLYGON((-10.4786 51.4457,-10.4786 55.3878,-5.3319 55.3878,-5.3319 51.4457,-10.4786 51.4457))
|
| South Africa | POLYGON((16.344976 -34.819168,16.344976 -22.125026,32.895474 -22.125026,32.895474 -34.819168,16.344976 -34.819168))
|
| United States | POLYGON((-124.731422 24.955967,-124.731422 49.371735,-66.969849 49.371735,-66.969849 24.955967,-124.731422 24.955967))
|
selectLength
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_miles', 'miles');
// SELECT "country_name",
// ST_Length("country_border") / 1609.344 AS "border_in_miles"
// FROM "world_countries";
| country_name
| border_in_miles
|
| -------------- | ----------------- |
| England | 2,795 |
| Ireland | 2,000 |
| South Africa | 4,000 |
| United States | 13,000 |
selectSymDifference
db('world_countries')
.select('country_name')
.selectSymDifference(
'country_border',
{ lat: 39.87, lon: -104.128, radius: '10mi' },
'sym_difference',
)
.where({ country_name: 'United States' });
// SELECT "country_name",
// ST_SymDifference("country_border", ST_Buffer('Point(-104.128, 39.87)'::geography, 10 * 1609.344)) AS "sym_difference"
// FROM "world_countries"
// WHERE "country_name" = 'United States';
| country_name
| sym_difference
|
| -------------- | ------------------------------------------------------------------------------------------------------------------------------- |
| United States | MULTIPOLYGON(((-124.731422 24.955967,-124.731422 49.371735,-66.969849 49.371735,-66.969849 24.955967,-124.731422 24.955967)))
|
selectUnion
db('world_countries')
.select('country_name')
.selectUnion(
'country_border',
[
{ lat: 39.87, lon: -104.128 },
{ lat: 39.17, lon: -104.92 },
{ lat: 39.25, lon: -105.01 },
{ lat: 39.87, lon: -104.128 },
],
'union',
)
.where({ country_name: 'England' });
// SELECT "country_name",
// ST_Union("country_border") AS "union"
// FROM "world_countries"
// WHERE "country_name" = 'England';
whereContains
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_miles', 'miles');
.whereContains('country_border', { lat: 39.87, lon: -104.128 })
// SELECT "country_name",
// ST_Length("country_border") / 1609.344 AS "border_in_miles"
// FROM "world_countries"
// WHERE ST_Contains("country_border", ST_Point(-104.128, 39.87));
| country_name
| border_in_miles
|
| -------------- | ----------------- |
| United States | 13,000 |
whereContainsProperly
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereContainsProperly('country_border', { lat: -1.474054, lon: 52.795479, radius: '1km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_ContainsProperly("country_border", ST_Buffer('Point(-1.474054, 52.795479)'::geography, 1000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| England | 2,795 |
whereCovers
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereCovers('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Covers("country_border", ST_Point(-104.128, 39.87));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereCoveredBy
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereCoveredBy('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_CoveredBy("country_border", ST_Point(-104.128, 39.87));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereCrosses
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereCrosses('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Crosses("country_border", ST_Point(-104.128, 39.87));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereDisjoint
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereDisjoint('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Disjoint("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| England | 2,795 |
| Ireland | 2,000 |
| United States | 13,000 |
whereEquals
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereEquals('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Equals("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereIntersects
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereIntersects('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Intersects("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereOverlaps
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereOverlaps('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Overlaps("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereRelate
db('world_countries')
.select('country_name')
.whereRelate(
'country_border',
{ lat: -26.2041, lon: 28.0473, radius: '10km' },
'nineElementMatrix',
);
// SELECT "country_name",
// FROM "world_countries"
// WHERE ST_Relate("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
|
| -------------- |
| South Africa |
whereTouches
db('world_countries')
.select('country_name')
.selectLength('country_border', 'border_in_km', 'kilometers');
.whereTouches('country_border', { lat: -26.2041, lon: 28.0473, radius: '10km' })
// SELECT "country_name",
// ST_Length("country_border") / 1000 AS "border_in_km"
// FROM "world_countries"
// WHERE ST_Touches("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 10000));
| country_name
| border_in_km
|
| -------------- | -------------- |
| South Africa | 4,000 |
whereWithin
db('world_countries').select('country_name').whereWithin('country_border', {
lat: -26.2041,
lon: 28.0473,
radius: '100km',
});
// SELECT "country_name",
// FROM "world_countries"
// WHERE ST_Within("country_border", ST_Buffer('Point(-26.2041, 28.0473)'::geography, 100 * 1000));
| country_name
|
| -------------- |
| South Africa |
Expressive Shape API
Knex-Spatial
makes it easy to define shapes using plain JS objects.
All methods that accept a shape argument can be passed a JS object, and it will be converted to the correct SQL syntax.
If you want to stand-alone convert a shape to SQL, you can do so using the helper method convertShapeToSql
. (Note: Advanced use-case.)
convertShapeToSql
Usage
import { convertShapeToSql } from 'knex-spatial-plugin';
convertShapeToSql({ lat: 39.87, lon: -104.128 });
// => 'POINT(-104.128 39.87)'::geography
Shape Object Syntax Examples
{ lat: 39.87, lon: -104.128, srid: 4326 }
// => 'SRID=4326;POINT(-104.128 39.87)'::geography
{ x: 39.87, y: -104.128 }
// => 'POINT(-104.128 39.87)'::geometry
{ lat: 39.87, lon: -104.128, radius: 1000 }
// => ST_Buffer('POINT(-104.128 39.87)'::geography, 1000)
{ lat: 39.87, lon: -104.128, radius: '1.5 mile' }
// => ST_Buffer('POINT(-104.128 39.87)'::geography, 1.5 * 1609.344)
[{ lat: 39.87, lon: -104.128 }, { lat: 39.87, lon: -104.128 }]
// => 'LINESTRING(-104.128 39.87, -104.128 39.87)'::geography
[{ lat: 39.87, lon: -104.128 }, { lat: 39.87, lon: -104.128 }, { lat: 39.87, lon: -104.128 }]
// => 'POLYGON((-104.128 39.87, -104.128 39.87, -104.128 39.87))'::geography
Geography Shapes in JSON
Note: For geometry shapes, use the x
& y
properties instead of lat
& lon
(as with geography.)
POINT
:{ lat: number, lon: number }
CIRCLE
:{ lat: number, lon: number, radius: number }
LINE
:[{ lat: number, lon: number }, ...]
(2+ points, cannot begin & end with the same point)POLYGON
:[{ lat: number, lon: number }, ...]
(first & last point must be the same)MULTIPOLYGON
:[ [{ lat: number, lon: number }, ...], ...]
(array of polygons)MULTILINE
:[ [{ lat: number, lon: number }, ...], ...]
(array of lines)
SQL Function Syntax Builder API
Knex-Spatial
provides a powerful SQL syntax builder API. It's designed to keep ORM code as simple & similar to SQL as possible.
sqlFunctionBuilder
- Supports any form of SQL Function.
- Supports aggregate functions like
Count()
,Min()
, andSum()
.builder('COUNT').arg('id').wrap('min')
=>min(COUNT('id'))
- Auto-magically converts natural language measurements into the correct unit.
- Works for both input values (Desired unit -> Base unit) and for output expressions (Base unit -> Desired unit.)
5 miles
=>8046.72
(meters)builder('ST_Length').arg('a_line_string').unit('acres')
=>ST_Length('a_line_string') / 4046.86
- Auto-magically converts JS objects into WKT (Well-Known Text) strings.
{x: 1, y: 2}
=>POINT(1 2)
Example
import { sqlFunctionBuilder } from 'knex-spatial-plugin';
const sqlFn = sqlFunctionBuilder(db);
sqlFn('ST_Distance')
.arg('point')
.arg({ lat: 39.87, lon: -104.128 })
.alias('distance')
.build();
// => ST_Distance("point", 'Point(-104.128 39.87)'::geography) AS "distance"
sqlFn('ST_DWithin')
.arg('polygon_column')
.arg({ lat: 39.87, lon: -104.128 })
.arg('5 miles')
.toString(); // Alias for .build()
// => ST_DWithin("polygon_column", 'Point(-104.128 39.87)'::geography, 5 * 1609.344)
Knex Query Builder Example
Use the sqlFunctionBuilder
's native Knex.Raw
support to easily build complex SQL expressions:
db('locations')
.select('name')
.select(
sqlFn('ST_Distance')
.arg('location')
.arg({ lat: 39.87, lon: -104.128 })
.alias('distance')
.unit('miles')
.toRaw(),
)
.where(
sqlFn('ST_DWithin')
.arg('location')
.arg({ lat: 39.87, lon: -104.128 })
.arg('65 miles')
.toRaw(),
)
.orderBy('distance');
// SELECT "name",
// ST_Distance("location", 'Point(-104.128 39.87)'::geography) / 1609.344 AS "distance"
// FROM "locations"
// WHERE ST_DWithin("location", 'Point(-104.128 39.87)'::geography, 5 * 1609.344)
// ORDER BY "distance" ASC
| name
| distance
|
| ---------------- | ---------- |
| Denver | 0 |
| Boulder | 38.5 |
| Colorado Springs | 60.5 |
db('locations')
.select('name')
.select(sqlFn('ST_Y').arg('location').alias('latY').toRaw())
.select(sqlFn('ST_X').arg('location').alias('lonX').toRaw());
// SELECT "name",
// ST_Y("location") AS "latY",
// ST_X("location") AS "lonX"
// FROM "locations";
| name
| latY
| lonX
|
| ------------ | -------- | -------- |
| Denver | 39.87 | -104.128 |
| Las Vegas | 36.17 | -115.14 |
| Johannesburg | -26.2041 | 28.0473 |
| Dublin | 53.3498 | -6.2603 |
| London | 51.5074 | -0.1278 |
| D.C. | 38.9072 | -77.0369 |
See the tests for more examples.
References
TODO
- [ ] Add Schema Builder methods
- [ ] Convert 'legacy' syntax builder code to use
sqlFunctionBuilder
. - [x] Add tests
- [x] Add more methods.
- [x] Add more docs
- [x] Add more examples
- [x] Intuitive Shape Builder (tried using
knex-postgis
, verbose like SQL syntax.) - [ ] Add 'transformer' wrapper fn support for
ST_AsText
,ST_AsGeoJSON
,ST_AsEWKT
. (IncludeST_AsBinary
?) - [ ] Add global config for default select wrapper (
ST_AsText
,ST_AsGeoJSON
,ST_AsEWKT
.) - [ ] Add global config to treat undefined values as
null
or throw error. - [ ] Add smarts for 2d & 3d unit support.
Dev Notes
This project evolved from a narrowly scoped helper - with these initial goals:
- Find nearby things, sort by distance.
- Follow Knex' API design as closely as possible (builder AKA chainable methods)
- Use natural language for measurements (e.g.
5 miles
instead of8046.72
meters)
After some initial positive feedback, I got asked to support all the PostGIS methods. There were 2 divergent paths I could take:
- Focus on flexible & fluent support for "common" cases.
- For example, a buffered version of a shape column:
.selectBuffer('area', '0.1km')
- Add computed distance to
SELECT
:.selectDistance('location', { lat, lon })
- Filter by distance
WHERE
:.whereDistanceWithin('location', { lat, lon, radius: '10km' })
- Cons: This limits nested or fancy expression patterns.
- For example, a buffered version of a shape column:
- Build one standalone module meant to plug wherever
knex
raw
is supported - somewhat likeknex-postgis
' design.- Then afterward add
.select*()
and.where*()
style-helpers. - A funny thing happened on the way to building #1, I realized I needed a SQL function builder anyway.
- Then afterward add
Turns out my attempt at a shortcut - by building a SQL fragment/template-based approach - was a little shortsighted. I ended up building an SQL builder anyway. Might include knex-postgis
after all... TBD.
| So, why didn't you build on top of knex-postgis
?
I may still do so.
For now, I prioritized a fluent API over 1:1 SQL syntax support.
I really enjoy Knex' API design, and I wanted to extend it in such a way that - dare I say - improved on native SQL OGC syntax. (It appears knex-postgis
primary goal is supporting SQL syntax faithfully 1:1, which they do fabulously.)
I looked into extending their API so you could chain things in a possibly friendlier way. It felt like I was compromising their API design goals.
// To generate: ST_AsText(ST_Centroid('geom'))
// knex-postgis API is very close to the SQL it represents 1:1
st.asText(st.centroid('geom'))
// However, I'd prefer a more 'fluent' API extended into knex.
.selectCentroid('geom', 'ST_AsText');
// Perhaps using `functionBuilder` like so:
builder('ST_Centroid').arg('geom').wrap('ST_asText');
// Compare the knex-postgis example:
let q = db
.select('id', st.asText(st.centroid('geom')).as('centroid'))
.from('geometries');
q = db
.select('id')
.selectCentroid('geom', 'asText')
.from('geometries');
// => SELECT "id", ST_AsText(ST_Centroid("geom")) AS "centroid" FROM "geometries"
st.geomFromText('Point(0 0)', 4326);
Unit Helpers
As for the helpers around units
, some notes:
- Yes, I am aware units is a far more complicated topic than this. I don't personally care about historical or nautical units, I just want to be able to use natural language for common measurements. (PRs welcome of course.)
- I had trouble using the most popular units conversion library on NPM. One thing I was initially trying to handle was European denoted numbers, like
1.234.567,12 km
(swapped comma & decimal points.) I decided locale support was out of scope - at least until v3.