joshswan / bookshelf-postgis Goto Github PK
View Code? Open in Web Editor NEWBookshelf plugin for parsing & serializing PostGIS geography/geometry columns
License: MIT License
Bookshelf plugin for parsing & serializing PostGIS geography/geometry columns
License: MIT License
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!
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?
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.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.