The Sweet Spot
On software, engineering leadership, and anything shiny.

Knex.js and PostGIS cheat sheet

As follows are some code snippets for using Knex.js for executing Postgres and PostGIS queries.

Execute raw SQL in migration

I often find this useful for fancy SQL, like creating views.

1
2
3
exports.up = function(knex, Promise) {
  return knex.raw(`YOUR RAW SQL`);
};

Add a PostGIS Point type to a table in a migration:

1
2
3
return knex.schema.table('events', function(table) {
  table.specificType('point', 'geometry(point, 4326)');
})

Add a foreign key to another table.

1
2
3
return knex.schema.table('events', function(table) {
	table.integer('device_id').references('id').inTable('devices');
});

Add a multi-column unique index

1
2
3
return knex.schema.table('events', function(table) {
  table.unique(['start_time', 'end_time', 'start_location', 'end_location', 'distance_miles']);
});

Find a collection

1
2
3
4
knex.select('*')
.from('participants')
.where({ name: 'Jason' })
.andWhere('age', '>=', 20)

Custom operations in SELECT clause

1
2
3
knex('trips')
.select(knex.raw('miles * passengers as passenger_miles'))
.select(knex.raw("CONCAT('Hello, ', name) as greeting_message"))

Return PostGIS data from a spatial column:

We use knex-postgis to gain access to PostGIS functions in Postgres. Here, we return a ‘point’ column with ST_AsGeoJSON:

1
2
const knexPostgis = require('knex-postgis')(knex);
knex('events').select('*', knexPostgis.asGeoJSON('point'));

See knex-postgis documentation for a list of other PostGIS functions that are supported.

Comments