Sequelize: Support for geometry

[milovanderlinden]

All sequelize supported databases can hold geometry:

• Postgresql via postgis: http://postgis.org/
• Sqlite3 via spatialite: https://www.gaia-gis.it/fossil/libspatialite/index
• MySQL: https://dev.mysql.com/doc/refman/5.0/en/spatial-datatypes.html
• MariaDB: https://mariadb.com/kb/en/mariadb/documentation/gis-functionality/geometry-types/
• MSSQL: http://msdn.microsoft.com/en-us/library/bb933790.aspx

Wouldn't it be a nice enhancement to support

• Point
• LineString
• Polygon

geometry datatypes?

[mickhansen]

1445 related.

[mickhansen]

@milovanderlinden Mind summizing how each type is defined in each dialect. (unless 'Point' etc literally works in all of them)

[milovanderlinden]

@mickhansen unfortunatly not :-(
The key concept is of course X and Y, but also a projection identifier for the more advanced databases. If in the first installment, we assume geometry to always be latitude and longitude and 2D, this are the constructors.

POINT constructors:

• postgis:
  ST_PointmFromText('POINT(-71.064544 42.28787)',4326);
• spatialite:
  ST_PointFromText('POINT(-71.064544 42.28787)',4326);
• mysql:*
  Point(-71.064544, 42.28787);
• mariadb:*
  Point(-71.064544, 42.28787);
• mssql:
  geometry::STGeomFromText('POINT (-71.064544 42.28787)', 4326);

Note: mysql and mariadb do not support projections, also mind the , in the constructor!

[milovanderlinden]

I would like to help out myself. Are there any instructions on which files in sequelize need to be touched when constructing a new datatype?

[mickhansen]

@milovanderlinden you can help out by providing the syntax for all of them :)
Unfortuneatly we don't have a good system in place for how data types should work in different dialects (we use some wonky replaces right now)

[ptmt]

+1

Fow now, I use DataTypes.STRING to be able to display the point on the client, though changing is available with simple raw query like this 'UPDATE some_table SET geo = POINT(:lat, :lng)

[Pana]

+1

[rafaelveloso]

+1

[nford]

+1

[johnraber]

+1

[mickhansen]

@milovanderlinden is the projection required or optional? Is it important?

[paulxtiseo]

@mickhansen: projection is critical to accuracy of calculations with geographic data (but not geometry data), but not mandatory and not supported by some databases (ex: MySQL). Thus, it would have to be optional in Sequelize.

[mickhansen]

@paulxtiseo I see so in PG you could store a POINT without a projection for geometric data and a POINT with a projection for gps/geographic data?

Implementing the data types wouldn't be too tricky.
But i don't currently have an API for how values would be inputted.

[mbroadst]

@mickhansen it's worth mentioning that @theverything has written this which might be a good start for a unified geo types module in sequelize

[paulxtiseo]

@mickhansen, not sure of all the nuances in various systems, but in PostGIS2, you can store a POINT with or without projection in geometry(POINT) or in geography(POINT) columns. The differences are that the default projection in the former is -1 and 4326 in the latter. Furthermore, the real functional differences are that the types allowed in a geography column are currently more limited (in practice) than in geometry, and that the geography type is more accurate but also more computationally expensive over large distance on a sphere. For small distances (one country), the right projection can help mitigate errors, but as you get larger, geography becomes necessary.

BTW, here's the list of all geo types that should be implemented, excluding curved geometries (poor ecosystem support would make implementing these deferable, IMO). Note that each one comes in a Z, M and ZM flavor, ex: POINTZM. Z implies a 3D type, M implies an arbitrary measure. For example, a column for 3D points in a CREATE TABLE could be specified as pointSrid geometry(POINTZ,4269).

GEOMETRY
POINT
LINESTRING
POLYGON
MULTIPOINT
MULTILINESTRING
MULTIPOLYGON
GEOMCOLLECTION
CURVE
SURFACE
POLYHEDRALSURFACE

[paulxtiseo]

http://workshops.boundlessgeo.com/postgis-intro/index.html
Chp 8 and 17.

[igorgolovanov]

+1

[scottwojan]

+1

[mshahriarinia]

+1

[komocode]

+1

[no-donut-for-you]

+1

[fanshuai]

+1

[janmeier]

Support for postgis has been added. @mikeingrose is working on support for the other dialects as well

[mshahriarinia]

What is the syntax for sequelize postgres geometry? Is there a HelloWorld example?

[tmandry]

Define the column like this:
location: { type: Sequelize.GEOMETRY('POINT') }
Then set it as a GeoJSON value like this:
record.location = {type: 'Point', coordinates: [-75.343, 39.984]};

You can see the geometry types supported in the source code; here's mysql.

[tmandry]

Just leaving this here for anyone who comes across it.. in order to simplify getting/setting the column for a point, I added custom getters and setters so I can just interact with it as an array of 2 coordinates:

    location: {
      type: Sequelize.GEOMETRY('POINT'),
      get: function() {
        var geoPoint = this.getDataValue('location');
        return (geoPoint === null) ? null : geoPoint.coordinates;
      },
      set: function(coords) {
        if (coords === null) {
          this.setDataValue('location', null);
        } else {
          this.setDataValue('location', { type: 'Point', coordinates: coords });
        }
      },
      validations: {
        isCoordinateArray: function(value) {
          if (!_.isArray(value) || value.length !== 2) {
            throw new Error('Must be an array with 2 elements');
          }
        }
      }
    }

[louy]

Hi,
I mentioned this earlier in #4630 but @janmeier suggested that I discuss it here.
I'm willing to implement some of the query api. I just want to know if the following makes sense:

Model.findAll({
  where: {
    $or: [{
      geom: { $contains: { $point: [0, 1] } },
      // ST_Within( ST_MakePoint(1,0), `table`.`geom` )
    }, {
      geom: { $contains: { $point: [0, 1], $fuzziness: 10 } }, // or maybe `$distance`
      // ST_DWithin( ST_MakePoint(1,0), `table`.`geom`, 10 )
    }, {
      point: { $inside: { $col: 'geom' } },
      // ST_Within( `table`.`point`, `table`.`geom` )
    }, {
      point: { $inside: { $polygon: [/* some coordinates */] } },
      // ST_Within( `table`.`point`, ST_GeomFromGeoJSON({type: 'Polygon'...}) )
    }],
  },
});

Or should I prefix operators with $geo instead?

Model.findAll({
  where: {
    $or: [{
      geom: { $geoContains: { $point: [0, 1] } },
      // ST_Within( ST_MakePoint(1,0), `table`.`geom` )
    }, {
      geom: { $geoContains: { $point: [0, 1], $fuzziness: 10 } }, // or maybe `$distance`
      // ST_DWithin( ST_MakePoint(1,0), `table`.`geom`, 10 )
    }, {
      point: { $geoInside: { $col: 'geom' } },
      // ST_Within( `table`.`point`, `table`.`geom` )
    }, {
      point: { $geoInside: { $polygon: [/* some coordinates */] } },
      // ST_Within( `table`.`point`, ST_GeomFromGeoJSON({type: 'Polygon'...}) )
    }],
  },
});

Could even be...

Model.findAll({
  where: {
    $or: [{
      $geoWithin: [{ $point: [0, 1] }, {$col: 'geom'}],
      // ST_Within( ST_MakePoint(1,0), `table`.`geom` )
    }, {
      $geoWithin: [{ $col: 'point' }, { $polygon: [/* some coordinates */] }, 10],
      // ST_DWithin( `table`.`point`, ST_GeomFromGeoJSON({type: 'Polygon'...}), 10 )
    }],
  },
});

[scottwojan]

I liked option 1

Sent from my iPhone

On Oct 9, 2015, at 9:36 AM, Louy Alakkad <[email protected][e-mail]> wrote:

Hi,
I mentioned this earlier in #4630https://github.com/sequelize/sequelize/issues/4630 but @janmeierhttps://github.com/janmeier suggested that I discuss it here.
I'm willing to implement some of the query api. I just want to know if the following makes sense:

Model.findAll({
where: {
$or: [{
geom: { $contains: { $point: [0, 1] } },
// ST_Within( ST_MakePoint(1,0), table.geom )
}, {
geom: { $contains: { $point: [0, 1], $fuzziness: 10 } }, // or maybe $distance
// ST_DWithin( ST_MakePoint(1,0), table.geom, 10 )
}, {
point: { $inside: { $col: 'geom' } },
// ST_Within( table.point, table.geom )
}, {
point: { $inside: { $polygon: [/* some coordinates */] } },
// ST_Within( table.point, ST_GeomFromGeoJSON({type: 'Polygon'...}) )
}],
},
});

Or should I prefix operators with $geo instead?

Model.findAll({
where: {
$or: [{
geom: { $geoContains: { $point: [0, 1] } },
// ST_Within( ST_MakePoint(1,0), table.geom )
}, {
geom: { $geoContains: { $point: [0, 1], $fuzziness: 10 } }, // or maybe $distance
// ST_DWithin( ST_MakePoint(1,0), table.geom, 10 )
}, {
point: { $geoInside: { $col: 'geom' } },
// ST_Within( table.point, table.geom )
}, {
point: { $geoInside: { $polygon: [/* some coordinates */] } },
// ST_Within( table.point, ST_GeomFromGeoJSON({type: 'Polygon'...}) )
}],
},
});

Could even be...

Model.findAll({
where: {
$or: [{
$geoWithin: [{ $point: [0, 1] }, {$col: 'geom'}],
// ST_Within( ST_MakePoint(1,0), table.geom )
}, {
$geoWithin: [{ $col: 'point' }, { $polygon: [/* some coordinates */] }, 10],
// ST_DWithin( table.point, ST_GeomFromGeoJSON({type: 'Polygon'...}), 10 )
}],
},
});

Reply to this email directly or view it on GitHubhttps://github.com/sequelize/sequelize/issues/2839#issuecomment-146890212.

[mickhansen]

Without prefixes is cleaner. The query builder has attribute awareness anyways so.

[janmeier]

Yep, definitely without.

There were several competing PRs with geometry support, and the one that wasn't merged did have support for some of those operators. So you might look to that for inspiration / tests

[brad-decker]

@louy Is there any update on those operator implementations? If you'd like help I can take a look as well. We will need support for these operators as well going forward for some of our projects.

[jocull]

Is there any way that this works with SQLite / Spatialite currently? We've got a development case where we're trying to use SQLite for local development, but MySQL in production.

[janmeier]

@jocull no work has been done yet, but it should be possible. If you'd like to give it a try it shouldn't be too much work. Mostly just stringify and parsing the data and code for the geometry specific operators in query generator

[backnotprop]

I'm working on the latest version and am getting:
'type "geometry" does not exist'

• Using postres

[janmeier]

http://stackoverflow.com/questions/6850500/postgis-installation-type-geometry-does-not-exist

[ghost]

What if you want to store several types of geometry? So perhaps a point for one row and a line for the next? The schema seems to be locking this down.

[janmeier]

@latitudehopper How so? You can use Datatypes.GEOMETRY('POINT') for points only or Datatypes.GEOMETRY for all types of geometry.

https://github.com/sequelize/sequelize/blob/master/test/integration/model/geometry.test.js

[ghost]

Ah, I missed that. Awesome, thanks.

[janmeier]

@tayden-hakai geometry is already supported, only sqlite support is missing

[saurfangg]

Is the official documentation going to be updated to reflect this support? As of right now it mentions nothing regarding PostGIS or Geometry data types. It would be useful to have examples of Point, Polygon and Multipolygon geometries being used.

[jocull]

+1

On Mon, Jan 25, 2016 at 2:47 PM, 0x11 [email protected] wrote:

Is the official documentatino going to be updated to reflect this support?
As of right now it mentions nothing regarding PostGIS or Geometry data
types. It would be useful to have examples of Point, Polygon and
Multipolygon geometries being used.

—
Reply to this email directly or view it on GitHub
https://github.com/sequelize/sequelize/issues/2839#issuecomment-174637555
.

[mickhansen]

@0x11 Original author did not provide documentation: https://github.com/sequelize/sequelize/pull/4013. Maintainers only have so much time but we hope to get around to it yes.

[saurfangg]

@mickhansen I see that POLYGON is supported. Any chance that type also supports Multipolygons or would that need to be added separately?

[mickhansen]

@0x11 it might work out of the box, haven't tested it personally

[daniel7912]

Is it possible to find records within a certain distance now as in @louy's post? I couldn't see anything in the documentation but that would be great. Thanks!

[louy]

@daniel7912 it's possible. You have to use sequelize.fn, which is documented. Note that this is not a geometry-specific syntax, but a way of writing sql queries, which is why it's not documented.

If you're using postgis I think it will be something like this:

{
  where: {
    distance: {$lte: 30},
  },
  attributes: [['distance', sequelize.fn('ST_Distance', sequelize.col('point'), sequelize.col('shape'))]],
}

[daniel7912]

@louy brilliant, thanks for the tip. Look forward to giving this a go later. I use MySQL usually, will the code be similar?

[louy]

I don't think mysql supports geometry.

[scottwojan]

@louy https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

[corbanb]

@louy does your examples still work for you?

{
  where: {
    distance: {$lte: 30},
  },
  attributes: [['distance', sequelize.fn('ST_Distance', sequelize.col('point'), sequelize.col('shape'))]],
}

I am getting errors like:

{ [SequelizeDatabaseError: ERROR:  unknown GeoJSON type
]
  name: 'SequelizeDatabaseError',
  message: 'ERROR:  unknown GeoJSON type\n',
  parent:
   { [Error: ERROR:  unknown GeoJSON type
   ]
     severity: 'ERROR',
     sqlState: 'XX000',
     messagePrimary: 'unknown GeoJSON type',
     sourceFile: 'lwgeom_pg.c',
     sourceLine: '162',
     sourceFunction: 'pg_error',
     sql: 'SELECT count(*) AS "count" FROM "Trackers" AS "Tracker" WHERE ("Tracker"."deletedAt" IS NULL AND "Tracker"."geo" <= ST_GeomFromGeoJSON(\'30\'));' },
  original:
   { [Error: ERROR:  unknown GeoJSON type
   ]
     severity: 'ERROR',
     sqlState: 'XX000',
     messagePrimary: 'unknown GeoJSON type',
     sourceFile: 'lwgeom_pg.c',
     sourceLine: '162',
     sourceFunction: 'pg_error',
     sql: 'SELECT count(*) AS "count" FROM "Trackers" AS "Tracker" WHERE ("Tracker"."deletedAt" IS NULL AND "Tracker"."geo" <= ST_GeomFromGeoJSON(\'30\'));' },
  sql: 'SELECT count(*) AS "count" FROM "Trackers" AS "Tracker" WHERE ("Tracker"."deletedAt" IS NULL AND "Tracker"."geo" <= ST_GeomFromGeoJSON(\'30\'));' }

Which is obvious a format of my query. Thoughts?

[louy]

@corbanb I think you're getting this error for a different query. Take a look at your sql query:

SELECT count(*) AS "count" FROM "Trackers" AS "Tracker" 
WHERE (
  "Tracker"."deletedAt" IS NULL 
  AND "Tracker"."geo" <= ST_GeomFromGeoJSON('30')
);

[doublesharp]

@jocull @janmeier - My setup is using MariaDB in production and SQLite3 for tests. Some of my models contain POINT datatypes and inserting this data in a test would generate an error complaining that the geo features weren't available.

I was able to get this to work by loading the spatialite npm package (this runs make to build spatialite for your system and is really slow).

In my test class I aliased the sqlite.Database.run function and then replaced it with a wrapper that loads the spatialite module before running the query.

test class

const sqlite = require('spatialite');

sqlite.Database.prototype.run_cp = sqlite.Database.prototype.run;
sqlite.Database.prototype.run = function run(...args) {
  this.spatialite(err => {
    return this.run_cp(...args);  
  })
}

No longer getting errors when inserting geometry data in unit tests. Very possible there is a more efficient way to do this, but for my few test cases this worked.

[flockonus]

Using Sequelize v3 could find examples of defining a point in (lon, lat), but couldn't find a way to query points within a certain range in meters, is there a way to do so?

My actual use case is searching for other people nearby, so i'd input a point acquired from the client and get all other users within X meters (even better if takes into account Earth's curvature). I'd really prefer using sequelize over raw queries since we'll be leveraging joins, selects and such features. Is there a way to do this using sequelize?

[DavidVeloso]

+1

[akinba]

+1
Any progess about this topic because
I need to select spatial objects without using raw queries

SELECT gid, ST_AsGeoJSON(geom) as geom FROM buildings
where ST_Intersects( st_MakeEnvelope($1,$2,$3,$4, 4326) ;

[jackturnbull]

I've created a little transform helper to accept the findAll options and transform them into something Sequelize understands. Main advantage is that you can define separate operations and monkey-patch them into the where query so these are augmented with other query arguments. Main disadvantage is that it's only something I've rustled up this morning very quickly and only transforms queries into $and, so not (location column) OR (other column) sort of stuff, but works for our use case:

// location_helper.js
const _ = require('lodash');

function locationQueryOptions(options, sequelize, column) {
  const queryDefinitions = {
    $intersects: geoJson => sequelize.fn('ST_Intersects', sequelize.col(column), sequelize.fn('ST_GeomFromGeoJSON', geoJson)),
  };

  if (options && options.where) {
    const locationsQueries = Object.entries(options.where[column] || []).map(([query, geoJson]) => {
      const queryFunction = queryDefinitions[query];
      return queryFunction ? queryFunction(JSON.stringify(geoJson)) : null;
    }).filter(Boolean);

    return Object.assign(options, {
      where: {
        $and: [
          _.omit(options.where, [column]),
          ...locationsQueries,
        ],
      },
    });
  }

  return options;
}

module.exports = {
  locationQueryOptions,
};

At this point you only need to overload your findAll method in the model and pass it through the helper method. If you're using the 4.0 Model definition syntax:

const Sequelize = require('sequelize');
const { locationQueryOptions } = require('../helpers/location_helpers');

class User extends Sequelize.Model {
  static init(sequelize) {
    return super.init({
      location: {
        type: Sequelize.GEOMETRY,
      },
    }, {
      sequelize,
    });
  }

  static findAll(options) {
    return super.findAll(locationQueryOptions(options, this.sequelize, 'location'));
  }
}

module.exports = User;

And you can query the model as so:

User.findAll({
  where: {
    location: {
      $intersects: {
        //GeoJSON
      }
    }
  }
});

And I repeat; far from perfect but that's roughly the approach that I've went with in this case as we need to accept GeoJSON from the client as well as a variety of different query operations.

If anyone does tidy this up, or get it to accept $or queries then keep me posted.

[pensierinmusica]

if I want to find the 10 closest points to a certain location, how should the Sequelize query be?

Assume I have a model that looks something like this:

sequelize.define('Point', {geo: DataTypes.GEOMETRY('POINT')});

Now let's say we input 100 random points in the db through something like:

db.Point.create({geo: {type: 'Point', coordinates: [randomLat, randomLng]}});

When I run this query I get an error:

const location = sequelize.literal(`ST_GeomFromText('POINT(${lat} ${lng})', 4326)`);

db.Point.findAll({
  attributes: [['distance', sequelize.fn('ST_Distance', sequelize.col('Point'), location)]],
  order: 'distance',
  limit: 10
});

// -> TypeError: s.replace is not a function

Any idea what is the issue / how to fix it?

Thx!

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[pensierinmusica]

Still an issue

[javiermanzano]

@pensierinmusica any solution to that? I'm having the same problem

[pensierinmusica]

@mickhansen?

[mickhansen]

@pensierinmusica I'm not familiar with the geo API

[cladjules]

I have edited the sequelize files to check the dialect and stringify the correct function depending on it,
I could do create a PR if needed to hightlight my code.

but basically, in sequelize/lib/data-types.js, I have added dialect parameter:

ABSTRACT.prototype.stringify = function stringify(value, options, dialect) {
  if (this._stringify) {
    return this._stringify(value, options, dialect);
  }
  return value;
};

GEOMETRY.prototype._stringify = function _stringify(value, options, dialect) {
  if (dialect === 'mssql') { // You can do a switch here
    return 'geometry::STGeomFromText(' + options.escape(Wkt.convert(value)) + ', ' + value.srid + ')';
  }

  return 'GeomFromText(' + options.escape(Wkt.convert(value)) + ')';
};

and in sequelize/lib/dialects/abstract/query-generator.js:
value = field.type.stringify(value, { escape: simpleEscape, field, timezone: this.options.timezone }, this.dialect);

Hope that helps...

[ablankenship10]

Is there any documentation for these geo APIs and how to use them? I'm looking for how to find all POINTS within a given POLYGON and found one example that seemed to suggest this is the way to do that:

{
    where: {
      location: {$inside: {$polygon: zone.shape}, // address.location is POINT, zone.shape is POLYGON
    }
}

but I get this error:

Unknown GeoJSON type

from query:

"address"."location" = ST_GeomFromGeoJSON('{"$inside":{"type":"Polygon","coordinates":[[[ coords... ]]]"}}')

[dengue8830]

for those that will come here, this example works to find closest points in a radius for a given point

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. If this is still an issue, just leave a comment 🙂

[papb]

What is the current status on this? I am confused.

GEOMETRY seems already supported for PostgreSQL, MySQL and MariaDB. So is this issue just a feature request for SQLite and MSSQL? Or am I missing something?

[Akila1928]

Does sequelize support MSSQL geometry?

[papb]

@Akila1928 I should know, but I don't... Would you be willing to perform a quick test and let me know?

[CompuTecMJS]

Sigue siendo un problema
@pensierinmusicadid, did you get to solve that problem bro ?, I also have that problem

[pensierinmusica]

@CompuTecMJS I ended up using a raw query.

[sungle3737]

wkx module in sequelize does not support POINTZ , LINESTRINGZ , POLYGONZ so sequelize can't accept Z data with error

invalid GeoJson representation

[imdkbj]

5 years and still no support for mssql.