Add new geometry columns in postgres using python

Question

I'm new in postgres and postgis, I loaded data from csv file to postgres database but the problem is when I want to add a new geometry column. thank you for your help

try:
   cursor.execute("""alter table Pluvometre add column geom geometry(point, 4326)
                  using st_setsrid(st_makepoint(X, Y),4326);""")
except:
   print "Error add gemetry column"

my data:
--------
  X                Y               ID  nosico noi  mt8_x             mt8_y            lat  lon
-74.0313470791373 45.3929059093611 1 OBXL1011 33   263196.452317745  5028244.700001    45 -74
-73.9491598482168 45.39888024068   2 OBXL1021 21   269635.2727916759 5028869.415663255 45 -74

Tom-db · Accepted Answer · 2017-03-03 07:25:41Z

2

The USING clause cannot be used with ADD COLUMN. See https://www.postgresql.org/docs/9.6/static/sql-altertable.html

In your case you need two statements: a ALTER TABLE and a UPDATE:

ALTER TABLE Pluvometre ADD COLUMN geom geometry(point, 4326);
UPDATE Pluvometre SET geom = st_setsrid(st_makepoint(X, Y),4326);

answered Mar 3, 2017 at 7:25

Tom-db

6,9584 gold badges34 silver badges51 bronze badges

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

2 Comments

Adil Blanco Over a year ago

Tahnk you for your help, I have another question, it's normal to have data like this: 0101000020AD10000012EE2D97018252C0378EA7BD4AB24640 0101000020AD1000001EB1F208BF7C52C0E05EFA810EB34640 in my new geometry column ?

Tom-db Over a year ago

Yes, they are WKB strings, binary representation of Postgis geometries. See en.wikipedia.org/wiki/Well-known_text#Well-known_binary. The functions St_SetSrid() and St_MakePoint() return this kind of data. Use the function St_AsText(geom) to see the original coordinates.

Collectives™ on Stack Overflow

Add new geometry columns in postgres using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related