Insert POINT into postgres database

Question

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 :

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

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

It can be done automatically when using pg-promise. If you are interested, then I will add an answer with examples ;) — vitaly-t
– vitaly-t, Commented Jan 6, 2017 at 16:22

joanolo · Accepted Answer · 2017-01-06 00:49:57Z

15

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)

answered Jan 6, 2017 at 0:49

joanolo

6,3881 gold badge33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jcollum Over a year ago

Questions asked in Language X should be answered in Language X.

Deepak Bandi · Accepted Answer · 2017-01-05 21:10:38Z

11

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.

answered Jan 5, 2017 at 21:10

Deepak Bandi

1,9064 gold badges22 silver badges37 bronze badges

1 Comment

Pirulax Jul 19 at 16:26

I very much hope so that you've validated that input, because as it is, it's very prone to sql injection.

Chris · Accepted Answer · 2020-03-23 02:39:45Z

7

I recently came across a similar problem using node-postgres when inserting into a postgis database with a geography (point) column. My solution was to use:

pool.query("INSERT INTO table (name, geography) VALUES ($1, ST_SetSRID(ST_POINT($2, $3), 4326))",
      [req.body.name, req.body.lat, req.body.lng ]);

answered Mar 23, 2020 at 2:39

Chris

4,0561 gold badge19 silver badges35 bronze badges

Comments

msg45f · Accepted Answer · 2020-08-29 08:55:57Z

4

Current versions (Postgres 12, pg 8) should work simply use Postgres's POINT function to set a point column value.

Example:

export async function setPoint(client, x, y, id) {
    const sql = `UPDATE table_name SET my_point = POINT($1,$2) WHERE id = $3 RETURNING my_point`;
    const result = await client.query(sql, [x, y, id]);
    return result.rows[0];
}
await setPoint(client, 10, 20, 5);

Result:

{x: 10.0, y: 20.0}

answered Aug 29, 2020 at 8:55

msg45f

85514 silver badges23 bronze badges

Comments

vitaly-t · Accepted Answer · 2023-04-27 23:29:41Z

1

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]);

4 Comments

Deepak Bandi Over a year ago

Thanks @vitaly-t I have one more question, my database is more of location oriented, should I use postGIS?

vitaly-t Over a year ago

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 ;)

Deepak Bandi Over a year ago

Cool. Thank you. :)

jcollum Over a year ago

hey that last link is dead, do you have the current version?

Collectives™ on Stack Overflow

Insert POINT into postgres database

5 Answers 5

1 Comment

1 Comment

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related