CODEWITHSUNDEEP
node.jspostgresqlsequelize.jspostgissequelize-cli
Patryk Bernasiewicz
Patryk Bernasiewicz

Reputation: 119

Sequelize - How do I seed database with Geometry value?

I use Sequelize to connect to my PostgreSQL database and during development, I use seed files to populate database with example data. I recently installed PostGIS for my database and wanted to use the GEOMETRY('POINT') type to describe the latitude/longitude position.

However, I have no idea how to put some GEOMETRY data using seeders. I tried following the examples in Sequelize docs:

module.exports = {
    up: (queryInterface, Sequelize) =>
        queryInterface.bulkInsert('Vets', [{
            title: 'Centrum Zdrowia Małych Zwierząt',
            city: 'Poznań',
            googleMapsID: 'ChIJQ8EgpGpDBEcR1d0wYZTGPbI',
            position: {
                type: 'Point',
                coordinates: [52.458415, 16.904740]
            },
            rodents: true
        }], {}),
    down: (queryInterface, Sequelize) =>
        queryInterface.bulkDelete('Vets', null, {})
};

but when I run the sequelize db:seed:all command, following error occurs:

ERROR: Invalid value [object Object]

I guess I just need to specify the position in some other way, but the Sequelize docs don't mention any for seeds. Can anyone help me with this problem?

The migration file for the Vets database is as follows:

module.exports = {
  up: (queryInterface, Sequelize) => queryInterface.createTable('Vets', {
    ...
    rodents: {
      type: Sequelize.BOOLEAN
    },
    position: {
      type: Sequelize.GEOMETRY
    },
    websiteUrl: {
      type: Sequelize.STRING
    },
    ...
  }, {
    indexes: [
      {
        unique: true,
        fields: ['title', 'city']
      }
    ]
  }),
  down: (queryInterface, Sequelize) =>
    queryInterface.dropTable('Vets')
};

And the model definition:

module.exports = (sequelize, DataTypes) => {
  const Vet = sequelize.define('Vet', {
    ...
    rodents: {
      type: DataTypes.BOOLEAN,
      defaultValue: false
    },
    position: DataTypes.GEOMETRY('POINT'),
    websiteUrl: DataTypes.STRING,
    ...
  }, {
    indexes: [
      {
        unique: true,
        fields: ['title', 'city']
      }
    ]
  });

  Vet.associate = (models) => {
    Vet.belongsTo(models.User, { as: 'suggestedBy' });
    Vet.belongsTo(models.User, { as: 'acceptedBy' });
  };

  return Vet;
};

Upvotes: 3

Views: 3489

Answers (2)

Pedro Henrique Bufulin
Pedro Henrique Bufulin

Reputation: 769

I would like to add to your answer, that if you want to add a type: 'Polygon' and as a geoJson you can do the following:


  up: (queryInterface, Sequelize) => {


    const polygon = { type: 'Polygon', coordinates: [
      [ [100.0, 0.0], [101.0, 0.0], [101.0, 1.0],
        [100.0, 1.0], [100.0, 0.0] ]
      ]};

    const cityCreated = {
   ....
      boundaries: Sequelize.fn('ST_GeomFromGeoJSON', JSON.stringify(polygon)),
   ....
    }

    return queryInterface.bulkInsert('cities', [cityCreated], {});
  },

in this case, the column boundaries is the one defined as GEOMETRY

this is good for adding boundaries, and geoJson is a more common dataType than text for this purpose.

Upvotes: 4

Patryk Bernasiewicz
Patryk Bernasiewicz

Reputation: 119

I found a solution (or rather a workaround), turns out I needed to use Sequelize.fn():

module.exports = {
  up: (queryInterface, Sequelize) =>
    queryInterface.bulkInsert('Vets', [
      {
        ...
        position: Sequelize.fn('ST_GeomFromText', 'POINT(52.458415 16.904740)'),
        ...
      }
    ], {}),
  down: (queryInterface, Sequelize) =>
    queryInterface.bulkDelete('Vets', null, {})
};

Upvotes: 6

Related Questions