6

I'm trying to insert point data in my database, but it tells me the data types aren't correct (and I think they are). Here's my table:

 Column      |  Type
 ------------+----------
 coordinates | point

And my query:

INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003 54.097834)',4326));
ERROR:  column "coordinates" is of type point but expression is of type geometry
LINE 1: INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POI...
                                                ^
HINT:  You will need to rewrite or cast the expression.

I don't quite get what is wrong (using the same as a select works). I get the same with LINESTRING and POINT, and my syntax seems to be the same as How to insert a point into postgis?


I partially made it work with help from @amball by dropping the table and re-making it. The insert worked, but not I have this:

gisdb=# INSERT INTO points(coordinates) VALUES (ST_GeomFromText('POINT(10.809003 54.097834)',4326));
INSERT 0 1
gisdb=# select * from points;
 id |                    coordinates
----+----------------------------------------------------
  1 | 0101000020E610000056BB26A4359E2540EB3713D3850C4B40
(1 row)

I don't know what happened with the coordinates (they are supposed to be 10.809003 54.097834 or something similar.

Here's the table info, which looks better, by the way:

   Column    |         Type         |                      Modifiers                      | Storage | Stats target | Description
-------------+----------------------+-----------------------------------------------------+---------+--------------+-------------
 id          | integer              | not null default nextval('points_id_seq'::regclass) | plain   |              |
 coordinates | geometry(Point,4326) |                                                     | main    |              |
2
  • 1
    How did you create your column "coordinates"? If you created it with SELECT AddGeometryColumn('points','coordinates','4326','POINT',2); your geometry column should have type geometry(Point, 4326)
    – amball
    Commented May 16, 2017 at 8:43
  • @amball If I remember correctly, I just created the new table putting POINT as data, or something similar.
    – Newwt
    Commented May 16, 2017 at 8:55

1 Answer 1

8

You've succeeded! The strange text you're seeing - 0101000020E610000056BB26A4359E2540EB3713D3850C4B40 ... is the point you made before, represented as 'well known binary' (WKB).

To see it in something more human-readable (Well Known Text):

SELECT st_asewkt('0101000020E610000056BB26A4359E2540EB3713D3850C4B40')

giving you:

SRID=4326;POINT(10.809003 54.097834)
1
  • Yes, it does! Thank you very much. I thought it would be stored like 10.809003 54.097834 and got me really confused haha
    – Newwt
    Commented May 16, 2017 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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