postgresqlpostgisnode-postgres

Insert POINT into postgres database


I need to insert/update a point column type in postgres database.

I'm using node-postgres

The script generated using POSTGRES admin panel shows the update query as

UPDATE public.places SET id=?, user_id=?, business_name=?, alternate_name=?, primary_category=?, categories=?, description=?, address=?, city=?, state=?, country=?, zip=?, point WHERE <condition>;

How do I achieve point from latitude and longitude?

I have seen couple of answers using POSTGIS, but could not get it working.

In the documentation of POSTGRES (https://www.postgresql.org/docs/9.2/static/xfunc-sql.html) it is mentioned we can use point '(2,1)', but this does not work with pg query.

What I have now :

var config = {
  user: 'postgres',
  database: 'PGDATABASE',
  password: 'PGPASSWORD!',
  host: 'localhost',
  port: 5432,
  max: 10,
  idleTimeoutMillis: 30000
};

And the update part :

app.post('/updatePlaces', function(req, res, next) {
    console.log("Update");
    console.log(req.body.places);
    pool.query('UPDATE places SET address = $1, alternate_name = $2, business_name = $3, categories = $4, city = $5, country = $6, description = $7, point = $8, primary_category = $9, state = $10, zip = $11', [req.body.places.address, req.body.places.alternate_name, req.body.places.business_name, req.body.places.categories, req.body.places.city, req.body.places.country, req.body.places.description, (req.body.places.point.x, req.body.places.point.y), req.body.places.primary_category, req.body.places.state, req.body.places.zip], function(err, result) {
      if(err) {
          console.log(err);
          return err;
      }

      res.send(result.rows[0]);
    });
});

Tried many different ways for passing point :

  1. (req.body.places.point.x, req.body.places.point.y)
  2. point(req.body.places.point.x, req.body.places.point.y)
  3. point '(2,1)'

All the above throws error. Do I need to use POSTGIS?


Solution

  • After couple of combinations, found out this works.!!

    ( '(' + req.body.places.point.x + ',' + req.body.places.point.y +')' )

    Posting as answer if someone is trying to do this just using node-postgres.

    So you can use single-quoted points: insert into x values ( '(1,2)' );

    But using insert into x values (point(1,2)); in the query does not work.