lane-tang/knex-postgis

postgis extension for knex

knex-postgis

Extension for use postgis functions in knex SQL query builder.

Example

This example show the sql generated by the extension.

const knex = require('knex');
const knexPostgis = require('knex-postgis');

const db = knex({
  dialect: 'postgres'
});

// install postgis functions in knex.postgis;
const st = knexPostgis(db);
/* or:
 * knexPostgis(db);
 * const st = db.postgis;
 */

// insert a point
const sql1 = db.insert({
  id: 1,
  geom: st.geomFromText('Point(0 0)', 4326)
}).into('points').toString();
console.log(sql1);
// insert into "points" ("geom", "id") values (ST_geomFromText('Point(0 0)'), '1')

// find all points return point in wkt format
const sql2 = db.select('id', st.asText('geom')).from('points').toString();
console.log(sql2);
// select "id", ST_asText("geom") as "geom" from "points"

// all methods support alias
const sql3 = db.select('id', st.asText(st.centroid('geom')).as('centroid')).from('geometries').toString();
console.log(sql3);
// select "id", ST_asText(ST_centroid("geom")) as "centroid" from "geometries"

Currently supported functions

• area(geom), see postgis documentation
• asText(column), see postgis documentation
• asGeoJSON(column), see postgis documentation
• asEWKT(column), see postgis documentation
• buffer(geom, radius), see postgis documentation
• centroid(geom), see postgis documentation
• distance(geom, geom), see postgis documentation
• distanceSphere(geom, geom), see postgis documentation
• dwithin(geom, geom, distance, /* optional bool spheroid */), see postgis documentation
• intersection(geom1, geom2), see postgis documentation
• intersects(geom1, geom2), see postgis documentation
• geography(geom)
• geometry(geography)
• geomFromText(geom, srid), see postgis documentation
• geomFromGeoJSON(geojson /object, string or column name/), see postgis documentation
• makeEnvelope(minlon, minlat, maxlon, maxlat, /* optional integer SRID */), see postgis documentation
• makePoint(lon, lat, /* optional z /, / optional measure */), see postgis documentation
• makeValid(geom), see postgis documentation
• point(lon, lat), see postgis documentation
• transform(geom, srid), see postgis documentation
• within(geom, geom), see postgis documentation
• setSRID(geom, srid), see postgis documentation
• x, see postgis documentation
• y, see postgis documentation
• boundingBoxIntersects(geom a, geom b), represented as a && b, see postgis documentation
• boundingBoxContained(geom a, geom b), represented as a @ b, see postgis documentation
• boundingBoxContains(geom a, geom b), represented as a ~ b, see postgis documentation

Define extra functions

const knex = require('knex');
const knexPostgis = require('knex-postgis');

const db = knex({
  dialect: 'postgres'
});

knexPostgis(db);

db.postgisDefineExtras((knex, formatter) => ({
  utmzone(geom) {
    return knex.raw('utmzone(?)', [formatter.wrapWKT(geom)]);
  }
}));
//now you can use st.utmzone function in the same way as predefined functions

Changelog

0.8.1

• fix result of .toString() in queries that use geoJSON. Now knex sends to pg the geom in string format instead of object.

0.8.0

• drop support for node 4
• fix st_transform function to avoid srid be interpreted as string
• update dependencies

0.7.0

• add support for st_SetSRID

0.6.0

• add support for st_DistanceSphere

0.5.0

• drop support for node 0.x
• add knex@0.14, node 8 y node 9 to travis

0.4.0

• add support for @ operator as boundingBoxContained
• add support for ~ operator as boundingBoxContains
• rename function boundingBoxIntersection to boundingBoxIntersects. The function boundingBoxIntersection will be removed in the next release.

0.3.0

• add support for && operator as boundingBoxIntersection

0.2.2

• update geojsonhint dependency to its new name (@davidfurlong)

0.2.1

• fix recognition of multi* WKTs (@johnhampton)

0.2.0

• (breaking change) Drop support for knex@0.7
• add support for knex@0.12