Insert point into a PostGis enabled Postgres db

Question

I have this (non working) query:

"INSERT INTO notes (
         lat, 
         lng, 
         point)
      VALUES (
         :lat,
         :lng,
         ST_GeometryFromText('POINT(:lat :lng)'))"

Which I am running against this table:

CREATE TABLE notes
(
  id serial NOT NULL,
  lat real NOT NULL,
  lng real NOT NULL,
  point point NOT NULL,
  CONSTRAINT notes_pkey PRIMARY KEY (id )
)

The query is prepared with PDO (php) but that is not the point I think. The result I get back is an exception claiming that:

SQLSTATE[42804]: Datatype mismatch: 7 ERROR:  column "point" is of type point but expression is of type geometry
LINE 8:          $8, $9, $10, $11, $12, ST_GeometryFromText('POINT(:...
                                        ^
HINT:  You will need to rewrite or cast the expression.

How can I cast the expression as suggested?

Erwin Brandstetter · Accepted Answer · 2014-02-19 18:09:16Z

1

Looks like you are using the standard Postgres type point:

"INSERT INTO notes (lat, lng, point)
 VALUES (:lat, :lng, '(:lat, :lng)'::point)"

You may want to use the PostGis type geometry in your table instead.

answered Feb 19, 2014 at 18:09

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

nourdine Over a year ago

Oh so when I do CREATE EXTENSION postgis; new data types are added?

Erwin Brandstetter Over a year ago

@nourdine: Yes. Lots of new types and functions and other stuff.

Community · Accepted Answer · 2017-04-13 12:33:44Z

0

It's not clear if you want to use PostgreSQL's point type, or PostGIS's geometry type. If it is the later, this answer should help out.

edited Apr 13, 2017 at 12:33

CommunityBot

11 silver badge

answered Feb 20, 2014 at 4:20

Mike T

44.4k18 gold badges166 silver badges214 bronze badges

Collectives™ on Stack Overflow

Insert point into a PostGis enabled Postgres db

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related