New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for geometry #2839

Open
milovanderlinden opened this Issue Jan 5, 2015 · 65 comments

Comments

@milovanderlinden

milovanderlinden commented Jan 5, 2015

All sequelize supported databases can hold geometry:

Wouldn't it be a nice enhancement to support

  • Point
  • LineString
  • Polygon

geometry datatypes?

@mickhansen mickhansen added the feature label Jan 5, 2015

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 5, 2015

#1445 related.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 5, 2015

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

@milovanderlinden

This comment has been minimized.

milovanderlinden commented Jan 5, 2015

@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

This comment has been minimized.

milovanderlinden commented Jan 5, 2015

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

This comment has been minimized.

Contributor

mickhansen commented Jan 5, 2015

@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

This comment has been minimized.

ptmt commented Jan 10, 2015

+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

This comment has been minimized.

Pana commented Mar 16, 2015

+1

3 similar comments
@rafaelveloso

This comment has been minimized.

rafaelveloso commented Mar 16, 2015

+1

@nford

This comment has been minimized.

nford commented Mar 21, 2015

+1

@johnraber

This comment has been minimized.

johnraber commented Mar 24, 2015

+1

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Mar 24, 2015

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

@paulxtiseo

This comment has been minimized.

paulxtiseo commented Mar 24, 2015

@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

This comment has been minimized.

Contributor

mickhansen commented Mar 24, 2015

@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

This comment has been minimized.

Contributor

mbroadst commented Mar 24, 2015

@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

This comment has been minimized.

paulxtiseo commented Mar 24, 2015

@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

This comment has been minimized.

paulxtiseo commented Mar 24, 2015

@igorgolovanov

This comment has been minimized.

igorgolovanov commented Mar 31, 2015

+1

@scottwojan

This comment has been minimized.

scottwojan commented Apr 16, 2015

+1

@mshahriarinia

This comment has been minimized.

mshahriarinia commented May 27, 2015

+1

@komocode

This comment has been minimized.

komocode commented Jun 3, 2015

+1

2 similar comments
@no-donut-for-you

This comment has been minimized.

no-donut-for-you commented Jun 10, 2015

+1

@fanshuai

This comment has been minimized.

fanshuai commented Jun 26, 2015

+1

@janmeier

This comment has been minimized.

Member

janmeier commented Jun 28, 2015

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

@mshahriarinia

This comment has been minimized.

mshahriarinia commented Jul 9, 2015

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

@janmeier

This comment has been minimized.

Member

janmeier commented Jan 15, 2016

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

@saurfangg

This comment has been minimized.

saurfangg commented Jan 25, 2016

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

This comment has been minimized.

jocull commented Jan 25, 2016

+1

On Mon, Jan 25, 2016 at 2:47 PM, 0x11 notifications@github.com 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
#2839 (comment)
.

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 26, 2016

@0x11 Original author did not provide documentation: #4013. Maintainers only have so much time but we hope to get around to it yes.

@saurfangg

This comment has been minimized.

saurfangg commented Jan 26, 2016

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

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jan 26, 2016

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

@daniel7912

This comment has been minimized.

daniel7912 commented Feb 26, 2016

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

This comment has been minimized.

louy commented Feb 29, 2016

@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

This comment has been minimized.

daniel7912 commented Feb 29, 2016

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

@louy

This comment has been minimized.

louy commented Mar 1, 2016

I don't think mysql supports geometry.

@corbanb

This comment has been minimized.

corbanb commented May 4, 2016

@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

This comment has been minimized.

louy commented May 7, 2016

@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

This comment has been minimized.

doublesharp commented Dec 10, 2016

@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

This comment has been minimized.

flockonus commented Mar 17, 2017

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

This comment has been minimized.

DavidVeloso commented Mar 29, 2017

+1

@akinba

This comment has been minimized.

akinba commented Apr 3, 2017

+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

This comment has been minimized.

jackturnbull commented Apr 3, 2017

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

This comment has been minimized.

Contributor

pensierinmusica commented May 16, 2017

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 stale bot added the stale label Jul 16, 2017

@stale

This comment has been minimized.

stale bot commented Jul 16, 2017

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

This comment has been minimized.

Contributor

pensierinmusica commented Jul 22, 2017

Still an issue

@stale stale bot removed the stale label Jul 22, 2017

@javiermanzano

This comment has been minimized.

javiermanzano commented Jul 23, 2017

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

@pensierinmusica

This comment has been minimized.

Contributor

pensierinmusica commented Jul 26, 2017

@mickhansen

This comment has been minimized.

Contributor

mickhansen commented Jul 26, 2017

@pensierinmusica I'm not familiar with the geo API

@cladjules

This comment has been minimized.

cladjules commented Aug 14, 2017

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

This comment has been minimized.

ablankenship10 commented Nov 5, 2018

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

This comment has been minimized.

dengue8830 commented Dec 3, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment