joshswan/bookshelf-postgis

Bookshelf plugin for parsing & serializing PostGIS geography/geometry columns

bookshelf-postgis

Bookshelf plugin for PostGIS to automatically parse and serialize geometry/geography columns on fetch and save, respectively. Geography columns are parsed to specified attributes (e.g. [lon, lat] results in a lon and a lat attribute on the model), and geometry columns are parsed to GeoJSON.

NOTE: Geography columns must already be WGS 84 lon lat (SRID:4326)!

Installation

npm install bookshelf-postgis --save

Usage

Apply the plugin:

const postgis = require('bookshelf-postgis');

bookshelf.plugin(postgis);

And add geography or geometry columns to your model:

const User = bookshelf.Model.extend({
  tableName: 'users',
  geography: {
    location: ['lon', 'lat'],
    // Use dot notation to specify deeper nesting
    geo: ['address.lon', 'address.lat'],
    geo2: ['location[0]', 'location[1]'],
  },
  geometry: ['geometry']
});

bookshelf-postgis's Issues

Support same field name in DB and in model

Hi @joshswan !
I was running into trouble because I wanted to use the same field name in the DB and in the model. That is, something like:

  geography: {
    last_location: ['last_location.lng', 'last_location.lat']
  },

I want/need to do this because of how validation works in my model.

The problem is how this library handles the omission of attributes in the format. I did the following change to make it work, using lodash's merge:

    format(attributes) {
      let omitFields = [];
      let newAttributes = {};

      // Convert geography attributes to raw ST_MakePoint calls with [lon, lat] as bindings
      if (this.geography) {
        Object.keys(this.geography).forEach((key) => {
          const fields = Array.isArray(this.geography[key]) ? this.geography[key] : ['lon', 'lat'];
          const values = fields.map(field => get(attributes, field)).filter(value => typeof value === 'number');

          if (values.length === 2) {
            newAttributes[key] = bookshelf.knex.raw('ST_SetSRID(ST_MakePoint(?, ?), 4326)::geography', values);
          }

          omitFields = [...omitFields, ...fields];
        });
      }

      // Convert geometry attributes to raw ST_GeomFromGeoJSON and stringify GeoJSON attributes
      if (this.geometry) {
        this.geometry.forEach((attr) => {
          if (attributes[attr]) newAttributes[attr] = bookshelf.knex.raw('ST_GeomFromGeoJSON(?)', [JSON.stringify(attributes[attr])]);
        });
      }

      // Call parent format method
      return merge(BaseModel.prototype.format.apply(this, [omit(attributes, omitFields)]), newAttributes);
    },

and

    parse(attributes) {
      let omitFields = [];
      let newAttributes = {};

      // Parse geography columns to specified fields
      if (this.geography) {
        Object.keys(this.geography).forEach((key) => {
          if (attributes[key]) {
            const fields = Array.isArray(this.geography[key]) ? this.geography[key] : ['lon', 'lat'];

            wkx.Geometry.parse(Buffer.from(attributes[key], 'hex')).toGeoJSON().coordinates.forEach((coordinate, index) => {
              set(newAttributes, fields[index], coordinate);
            });

            omitFields = [...omitFields, key];
          }
        });
      }

      // Parse geometry columns to GeoJSON
      if (this.geometry) {
        this.geometry.forEach((attr) => {
          if (attributes[attr]) newAttributes[attr] = wkx.Geometry.parse(Buffer.from(attributes[attr], 'hex')).toGeoJSON();
        });
      }

      // Call parent parse method
      return merge(BaseModel.prototype.parse.apply(this, [omit(attributes, omitFields)]), newAttributes);
    },

Are you interested in a pull request or do you want to change it yourself? Thanks!

Incorrect coordinates in database after insertion

I'm adding lat/lon points to my database as the type geography(POINT, 4326) using this plugin for handling converting to/from wkb but I'm finding the points in the database have an incorrect longitude value.

My table definition:

return knex.schema.createTableIfNotExists('upvotes', (table) => {
  table.increments('id').primary();
  table.string('device_id').notNullable();
  table.string('image_url').notNullable();
  table.specificType('location', 'geography(POINT, 4326)').notNullable();
  table.timestamps(true, true);
});

My table model:

const Upvotes = Model.extend({
  tableName: 'upvotes',
  geography: {
    location: ['latitude', 'longitude'],
  },
});

My insertion code:

async function insertUpvote({deviceId, location, imageURL}) {
  console.log(`Inserting upvote\n  deviceId: ${deviceId}\n  longitude: ${location.longitude}\n  latitude: ${location.latitude}\n  imageURL: ${imageURL}`);
  const upvote = await Upvotes.forge({
    latitude: location.latitude,
    longitude: location.longitude,
    device_id: deviceId,
    image_url: imageURL,
  }).save();

  return upvote.toJSON();
}

I've confirmed insertUpvote is receiving latitude 44.047726148147056 and longitude -123.08937754046048. But when I select the column using ST_AsText, the latitude is correct, but the longitude is -56.910622459539525 (SELECT ST_ASText(location) FROM upvotes;). I'm receiving the same result when selecting using the model.

What is happening here?

Geography column not appearing in results

I'm not seeing my geography columns in my models. I must be misunderstanding something about this plugin.

Here is my model definition (db is my knex instance):

const { db } = require('./db');
const bookshelf = require('bookshelf')(db);
const { Model } = bookshelf;

bookshelf.plugin('bookshelf-postgis');

const Upvotes = Model.extend({
  tableName: 'upvotes',
  geography: {
    location: ['lat', 'lon'],
  }
});

And here's how I setup my database/tables:

-- Add Postgis
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION postgis_sfcgal;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;

-- Add upvotes table
CREATE TABLE upvotes (
  id SERIAL PRIMARY KEY,
  device_id VARCHAR(100) NOT NULL,
  image_url VARCHAR(200) NOT NULL,
  location GEOGRAPHY NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

And my test code:

Upvotes.collection().fetchOne().then(v => {
  console.log(v.toJSON());
  process.exit();
});

Which prints:

{ id: 22994,
  device_id: 'test-device-62210',
  image_url: 'https://s3-us-west-2.amazonaws.com/project-media/uppit_test_2.jpg',
  location: '0101000020E6100000D44C535B85D55EC0D7878E52ABF34540',
  created_at: 2017-10-23T23:06:33.834Z }

As you can see, the result contains the raw location column, but it doesn't contain the parsed latitude and longitude values.