Can't set a geometry data type in PostgreSQL

Question

I have the datatype geometry defined. But I can't set it to one of the columns geom. as shown in the figure.

You have set a native PostgrSQL datatype "box" https://www.postgresql.org/docs/9.4/static/datatype-geometric.html. You may believe that you are dealing with the datatype "geometry" that comes with the PostGIS extension, but you should clarify your question. — user30184, Feb 06 '17 at 12:23
@user30184 I would like to set the geom to geometry datatype. how would I set it . — Andre Ahmed, Feb 06 '17 at 12:28
Please edit the question to specify the exact versions of PostgreSQL and PostGIS installed and confirm that you have enabled the PostGIS extension. — Vince, Feb 06 '17 at 12:32
I created a postgis extension. and I still can not specifiy the geometry or a point — Andre Ahmed, Feb 06 '17 at 14:23
Could you send links to the tutorials which you are following? — user30184, Feb 06 '17 at 17:09
use this query to alter geometry type ALTER TABLE poi ALTER COLUMN geom TYPE GEOMETRY(POINT,4326) — Shahzad Bacha, Feb 07 '17 at 06:03
@ShahzadBacha I would like to add lon, lat, alt, so point is not sufficient what should I use ? — Andre Ahmed, Feb 07 '17 at 08:23

Shahzad Bacha · Accepted Answer · 2017-02-07T11:04:57.893

9

First you need to create postgis extension to add spatial functions such as distance, area, union, intersection, and specialty geometry data types to the database.

create extension postgis;

Then alter geometry column of your table, in your case geom column using this SQL query.

ALTER TABLE poi
ALTER COLUMN geom
TYPE GEOMETRY(POINT,4326);

You can set your own projection, i assume your projection is Geographic Coordinate System (GCS WGS84)

Update:

If you want to store altitude with xy, you need to use POINTZ instead of POINT.

 TYPE GEOMETRY(POINTZ,4326);

Read this for further information.

edited Feb 07 '17 at 11:04

answered Feb 07 '17 at 10:52

Shahzad Bacha

1,596
1
14
23

a point is just an xy, how would I set an altitude, x,y,z – Andre Ahmed Feb 07 '17 at 10:54
you can store altitude in point data.. let me update my answer – Shahzad Bacha Feb 07 '17 at 10:55
when I do the above, I get the following error Column has Z dimension but geometry does not – Andre Ahmed Feb 07 '17 at 11:37
it's because your geometry doesn't have altitude information, just latitude and longitude. – Shahzad Bacha Feb 07 '17 at 11:52

Can't set a geometry data type in PostgreSQL

1 Answers1