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    |              |
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Newwt
  • 199
  • 1
  • 2
  • 10
  • 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 May 16 '17 at 08:43
  • @amball If I remember correctly, I just created the new table putting POINT as data, or something similar. – Newwt May 16 '17 at 08:55

1 Answers1

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)
Simbamangu
  • 14,773
  • 6
  • 59
  • 93
  • 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 May 16 '17 at 10:30