current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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?

share|improve this question
    
It can be done automatically when using pg-promise. If you are interested, then I will add an answer with examples ;) – vitaly-t Jan 6 at 16:22
    
Yes please @vitaly-t – Deepak Bandi Jan 6 at 16:25
up vote 1 down vote

If you are using pg-promise, then custom types can be formatted automatically, see Custom Type Formatting.

You can introduce your own type like this:

function Point(x, y) {
    this.x = x;
    this.y = y;

    // Custom Type Formatting:
    this._rawDBType = true; // to make the type return the string without escaping it;

    this.formatDBType = function () {
        return 'ST_MakePoint(' + this.x + ',' + this.y + ')';
    };
}

At some point you would create your objects:

var p = new Point(11, 22);

And then you can use such variables as regular types:

db.query('INSERT INTO places(place) VALUES(ST_SetSRID($1, 4326))', [p]);

See also: Geometry Constructors.

share|improve this answer
    
Thanks @vitaly-t I have one more question, my database is more of location oriented, should I use postGIS? – Deepak Bandi Jan 6 at 17:00
1  
Yes, it is generally a good idea, as PostGIS is like a native component for PostgreSQL these days, providing all the intelligent searches that one needs for location-based searches ;) – vitaly-t Jan 6 at 17:10
    
Cool. Thank you. :) – Deepak Bandi Jan 6 at 17:11
up vote 0 down vote accepted

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.

share|improve this answer
up vote 0 down vote

This works if you write SQL "directly":

CREATE TEMP TABLE x(p point) ;
INSERT INTO x VALUES ('(1,2)');
INSERT INTO x VALUES (point(3, 4));
SELECT * FROM x ;

Results

(1,2)
(3,4)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.