migration http://stackoverflow.com/questions/21105748/sequelize-js-how-to-use-migrations-and-sync http://stackoverflow.com/questions/18742962/add-data-in-sequelize-migration-script
$ sequelize db:migrate # Run pending migrations. $ sequelize db:migrate:undo # Revert the last migration run. $ sequelize help # Display this help text. $ sequelize init # Initializes the project. $ sequelize migration:create # Generates a new migration file. $ sequelize version # Prints the version number.
tags: Sequelize.ARRAY(Sequelize.STRING)
Do your own geocoding for high volume and avoid paying to 3rd party providers: tiger-geocoderlook at postgres spatial indexing example here. Wow. pretty neat stuff! without index it takes Execution time: 774.878 ms after creating index Execution time: 0.452 ms!!! $ psql -d my_test_db -U postgres CREATE TABLE test ( position POINT ); INSERT INTO test (position) SELECT point( RANDOM() * 1000, RANDOM() * 1000) FROM generate_series(1,1000000); /*one million points*/ explain analyze select *, position <-> point(500,500) as distance from test order by position <-> point(500,500) limit 10; /* TOP-k-nearest neighbors And we can find some rows close to center of the points cloud ~700ms */ create index q on test using gist ( position ); /* try the query again and see the difference! resutls ready in 0.4ms */
===================================================
create table MyTable (lat integer, lon integer, coor point); insert into MyTable (lat, lon, coor) select random() * 1000, random() * 1000, point( random() * 1000, random() * 1000) from generate_series(1,1000000); explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers 80ms explain analyze select * from MyTable where coor <@ box '((495,495),(505,505))' limit 10; -- using point: 24ms
create index MyTable_lat on MyTable (lat);
create index MyTable_lon on MyTable (lon);
create index MyTable_coor on MyTable using gist (coor);
explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers 9ms explain analyze select * from MyTable where coor <@ box '((495,495),(505,505))' limit 10; -- using point: 0.1ms
create index MyTable_latlon on MyTable (lat,lon);
explain analyze select * from MyTable where lat < 505 and lat > 495 and lon < 505 and lon > 495 limit 10; -- using lat/long two numbers -- 0.25ms
CREATE EXTENSION postgis; SELECT * FROM MyTable ORDER BY coor::geometry <-> st_makepoint(500, 500) LIMIT 10; -- postgis operator takes very long time SELECT * FROM MyTable ORDER BY ST_Distance(coor::geometry, ST_GeomFromText('POINT(500 500)')) LIMIT 10;
st_distance as well as st_area are not able to use indices. This is because both functions can not be reduced to questions like "Is a within b?" or "Do a and b overlap?". Even more concrete: GIST-indices can only operate on the bounding boxes of two objects. explain analyze select * from MyTable order by point(lat,lon)::geometry <-> ST_PointFromText('point(500 500)') limit 10; -- top k nearest neighbors 2616.333 ms explain analyze select * from MyTable order by point(lat,lon) <-> point(500, 500) limit 10; -- 1132.618 ms
Postgres+node ORM
regarding postgres orm for nodejs that supports spatial stuff there is not much out there.
Very tricky on either of the approaches/no docs as you might endup being stuck somewhere you dont wanna be and wish you had gone bare-bone
Here are some links on that link, link, link. this guy also said ditch orm.
PLAIN nodejs postgres spatial
here you can see that sequelise spatial index is only for mysql
hybrid
link, link, link, nodejs+sequelize. sublink.
So, all in all looks like postgres is the way to go but 'point' feature is not ready for prime-time in sequelise. link, link, link. So we go for bare postgres queries that supports point and top-k-nearest-points as demonstrated above. here and here, you can use sequelize like normal but then use hooks to alter table to add geometry column after sequelize migration is performed, then use proper getter/setters to keep those new columns up to date properly
Therefore you get the best of both worlds to be able to use top-k-nearest-poitns of postgres. mysql doesn't offer top-k but there are hacks to first look into a region and then sort by lat/long distance then limit to k results. but this is dependent on region size which is fferent from a village to a dense city. I can mitigate this by exponentially increasing radius ===> up to here I can benefit from sequelize ORM! so we go bare sql. but let's let the db take care of this so we go for postgres
To use the same design as the mysqlworkbench, we use sql power architect. connection > Add source connection > New connection : creat a connection to postgres and one to mysql that has the latest schema design
drag from mysqlworkbench hp_db database to the right hand side.
click Auto Layout
Tools > Forward engineer to postgres
===============
sequelize seems to be the most mature of all. but it doesnt support geo. geo and its index is important for us link, link, link
here is on creating index in postgres http://fadeit.dk/blog/post/sqlalchemy-postgresql-earthdistance http://johanndutoit.net/searching-in-a-radius-using-postgres/ http://dba.stackexchange.com/questions/10744/postgresql-vs-mysql-advantages-disadvantages-with-a-spatial-component http://stackoverflow.com/questions/14937742/mysql-vs-postgresql-spatial-application http://stackoverflow.com/questions/3743632/gis-postgis-postgresql-vs-mysql-vs-sql-server
I believe I shoulndt go for ORM as it just makes things over complicated. specially that sequelize doesn't allow you to have your own db but you have to use sequelize modesl.
mysql is not robust in geo. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ http://gis.stackexchange.com/questions/31628/find-points-within-a-distance-using-mysql
============================================================================ A FLOAT is for single-precision, while a DOUBLE is for double-precision numbers. DECIMAL data type to store exact numeric values
Sequelize.STRING // VARCHAR(255) Sequelize.STRING(1234) // VARCHAR(1234) Sequelize.STRING.BINARY // VARCHAR BINARY Sequelize.TEXT // TEXT Sequelize.INTEGER // INTEGER Sequelize.BIGINT // BIGINT Sequelize.BIGINT(11) // BIGINT(11) Sequelize.FLOAT // FLOAT Sequelize.FLOAT(11) // FLOAT(11) Sequelize.FLOAT(11, 12) // FLOAT(11,12) Sequelize.DECIMAL // DECIMAL Sequelize.DECIMAL(10, 2) // DECIMAL(10,2) Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres Sequelize.BOOLEAN // TINYINT(1) Sequelize.ENUM('value 1', 'value 2') // An ENUM with allowed values 'value 1' and 'value 2' Sequelize.ARRAY(Sequelize.TEXT) // Defines an array. PostgreSQL only. Sequelize.BLOB // BLOB (bytea for PostgreSQL) Sequelize.BLOB('tiny') // TINYBLOB (bytea for PostgreSQL. Other options are medium and long) Sequelize.UUID // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)
===========================================================================
sync is to create the table
Here we use sequelize: $ npm install --save sequelize pg pg-hstore
var Sequelize = require("sequelize"); // make sure you have created the database using pg Admin III
//var sequelize = new Sequelize("postgres://postgres:postgres@localhost:5432/hoodpantry");
var sequelize = new Sequelize(
'hoodpantry', // db name
'postgres', // username
'postgres', // password
{
host: 'localhost',
dialect: 'postgres',
port:5432,
dialectOptions: {
ssl: false
},
logging: true, //verbose
pool: {
max: 5,
min: 0,
idle: 10000
}
}
); var Person = sequelize.define('person', {
firstName: {
type: Sequelize.STRING,
},
lastName: {
type: Sequelize.STRING
}
}); Person.sync({force: true}).then(function () {
return Person.create({
firstName: 'KK',
lastName: 'Hancock'
});
}).then(
function(jane) {
console.log(jane.get({
plain: true
})
)} );
mkdir express-example cd express-example npm install express-generator node_modules/.bin/express -f npm install npm install --save sequelize sequelize-cli sqlite3 node_modules/.bin/sequelize init
Created "config/config.json" created migrations folder "./migrations". created models folder "./models". Loaded configuration file "config/config.json".
node_modules/.bin/sequelize model:create --name User --attributes username:string node_modules/.bin/sequelize model:create --name Task --attributes title:string
EDIT FILES
EDIT config file accordingly
associate: function(models) {
Task.belongsTo(models.User);
associate: function(models) {
User.hasMany(models.Task)
//bin/www
//.... var debug = require('debug')('express-example'); var app = require('../app'); var models = require("../models");
app.set('port', process.env.PORT || 3000);
models.sequelize.sync().then(function () { var server = app.listen(app.get('port'), function() { debug('Express server listening on port ' + server.address().port); }); }); //.....
npm install --save sequelize pg pg-hstore
http://docs.sequelizejs.com/en/latest/docs/models-definition/
var ValidateMe = sequelize.define('Foo', {
foo: {
type: Sequelize.STRING,
validate: {
is: ["^[a-z]+$",'i'], // will only allow letters
is: /^[a-z]+$/i, // same as the previous example using real RegExp
not: ["[a-z]",'i'], // will not allow letters
isEmail: true, // checks for email format (foo@bar.com)
isUrl: true, // checks for url format (http://foo.com)
isIP: true, // checks for IPv4 (129.89.23.1) or IPv6 format
isIPv4: true, // checks for IPv4 (129.89.23.1)
isIPv6: true, // checks for IPv6 format
isAlpha: true, // will only allow letters
isAlphanumeric: true // will only allow alphanumeric characters, so "_abc" will fail
isNumeric: true // will only allow numbers
isInt: true, // checks for valid integers
isFloat: true, // checks for valid floating point numbers
isDecimal: true, // checks for any numbers
isLowercase: true, // checks for lowercase
isUppercase: true, // checks for uppercase
notNull: true, // won't allow null
isNull: true, // only allows null
notEmpty: true, // don't allow empty strings
equals: 'specific value', // only allow a specific value
contains: 'foo', // force specific substrings
notIn: [['foo', 'bar']], // check the value is not one of these
isIn: [['foo', 'bar']], // check the value is one of these
notContains: 'bar', // don't allow specific substrings
len: [2,10], // only allow values with length between 2 and 10
isUUID: 4, // only allow uuids
isDate: true, // only allow date strings
isAfter: "2011-11-05", // only allow date strings after a specific date
isBefore: "2011-11-05", // only allow date strings before a specific date
max: 23, // only allow values
min: 23, // only allow values >= 23
isArray: true, // only allow arrays
isCreditCard: true, // check for valid credit card numbers
// custom validations are also possible:
isEven: function(value) {
if(parseInt(value) % 2 != 0) {
throw new Error('Only even values are allowed!')
// we also are in the model's context here, so this.otherField
// would get the value of otherField if it existed
}
}
}
}
})
isInt: {
msg: "Must be an integer number of pennies"
}
or if arguments need to also be passed add anargs property:
isIn: {
args: [['en', 'zh']],
msg: "Must be English or Chinese"
}
var Pub = Sequelize.define('Pub', {
name: { type: Sequelize.STRING },
address: { type: Sequelize.STRING },
latitude: {
type: Sequelize.INTEGER,
allowNull: true,
defaultValue: null,
validate: { min: -90, max: 90 }
},
longitude: {
type: Sequelize.INTEGER,
allowNull: true,
defaultValue: null,
validate: { min: -180, max: 180 }
},
}, {
validate: {
bothCoordsOrNone: function() {
if ((this.latitude === null) !== (this.longitude === null)) {
throw new Error('Require either both latitude and longitude or neither')
}
}
}
})
In this simple case an object fails validation if either latitude or
longitude is given, but not both. If we try to build one with an
out-of-range latitude and nolongitude, raging_bullock_arms.validate() might return
{
'latitude': ['Invalid number: latitude'],
'bothCoordsOrNone': ['Require either both latitude and longitude or neither']
}
|