How to get lat/lng coordinates from GEOMETRY('POINT') with Sequelize?

Question

I have an MS SQL database that contains a table with latitude/longitude positions of some devices. These positions are stored in a GEOGRAPHY field. Now I want to get these positions from my node app with Sequelize.

Here's my sequelize model definition:

db.define('StationLocation', {
    StationId: { type: Sequelize.INTEGER },
    Position: { type: Sequelize.GEOMETRY('POINT') }
  }
)

And here's what I currently get:

{  
   "id":4,
   "StationId":1,
   "Position":{  
      "type":"Buffer",
      "data":[  
         230,
         16,
         0,
         0,
         1,
         12,
         25,
         159,
         112,
         57,
         112,
         200,
         73,
         64,
         0,
         0,
         0,
         128,
         184,
         247,
         26,
         64
      ]
   }
}

How can I convert this to latitude and longitude values? Is Sequelize able to do this?

Update: In the example above, the position is this:

Latitude: 51.565924816122966
Longitude: 6.741914749145508

Here's my query:

StationLocation
.findAll({ raw: true })
.then(function(allStationLocations) {
    console.dir(allStationLocations[0].coordinates); // Just for debugging
    res.send(JSON.stringify(allStationLocations));
});

This translates to this SQL statement:

SELECT [id], [StationId], [Position] FROM [StationLocations] AS [StationLocation];

are you 100% positive that the column has the right datatype in the DB? — Tamas, May 19 '17 at 14:41

score 2 · Answer 1 · answered Sep 18 '18 at 16:24

Proper support for MSSQL geometry and geography data types is still pending. Tedious does however provide a parser for that buffer you get as a result: Tediousjs Geoparser

As a workaround for anyone having the same problem i've created an adapter utility, it's fairly simple to use but only supports points: Sequelize MSSQL Adapter

Require and use accordingly in your model definition, more info on the snippet's description.

const adapter = require('../utils/sequelize-geodata-adapter');

module.exports = (sequelize, DataType) =>
    sequelize.define('MyModel', {
        // Attributes
        location: {
            type: DataType.GEOGRAPHY('POINT', 4326),
            get: function () {
                const location = this.getDataValue('location');
                if (location)
                    return adapter.get(location);
            },
            set: function (value) {
                if (value === undefined || value === null)
                    return this.setDataValue('location', value);

                this.setDataValue('location', adapter.set(value));
            },
            validate: {
                hasCoordinates: adapter.validate
            }
        },
        ...
    }, {
        tableName: 'MyModels'
        ...
    });

Setter supports GeoJSON and Google-style {lat, lng} location formats, getter always returns {lat, lng} objects.

In your particular case you'll need to replace geography::Point with geometry::Point at the adapter since you are using the GEOMETRY data type. Parser is indistinct for both.

Also, this is a different approach and an interesting read: [Using geo-based data with SequelizeJS](https://manuel-rauber.com/2016/01/08/using-geo-based-data-with-sequelizejs-utilizing-postgresql-and-ms-sql-server-in-node-js/) — Lucas Astrada, Sep 18 '18 at 16:27

How to get lat/lng coordinates from GEOMETRY('POINT') with Sequelize?

1 Answers1