How to insert selection between an interval in PostgreSQL?

Question

I have a shapefile of airports. You can download it from here.

I've loaded this shapefile to postgreSQL. What I wanted is add points on centroids of each polygon.

1) I've added my point column;

select AddGeometryColumn('public','airport_shp','geom2',4326,'POINT',2);

2)Transformed coordinates from 3857 to 4326;

ALTER TABLE airport_shp ALTER COLUMN geom TYPE geometry(MultiPolygon,4326) USING ST_Transform(ST_SetSRID( geom,3857),4326);

3) I've inserted the centroids as points

insert into airport_shp(geom2) select st_centroid(geom) from airport_shp

The problem is; instead of adding the new values from the first empty row of the column, new values are added to the end of the file.

To make more sense below is representation of my first table;

*****************************************
*  id   *geom(MULTIPOLYGON)*geom2(POINT)*
*****************************************
*  1    *ADSADF23D31E475424*            *
*  2    *2134ADSA0106000020*            *            
*  3    *214124RD74C5D6A5DE*            *            
*  4    *SAD23134230E24C5E4*            *            
*****************************************

I'm expecting this table to be like this after 3. step;

*****************************************
*  id   *geom(MULTIPOLYGON)*geom2(POINT)*
*****************************************
*  1    *ADSADF23D31E475424*ASDEF245A3RF*
*  2    *2134ADSA0106000020*523RFTYTFFTY*            
*  3    *214124RD74C5D6A5DE*324FGSGSD523*            
*  4    *SAD23134230E24C5E4*SADFS456V324*            
*****************************************

But result is;

*****************************************
*  id   *geom(MULTIPOLYGON)*geom2(POINT)*
*****************************************
*  1    *ADSADF23D31E475424*            *
*  2    *2134ADSA0106000020*            *
*  3    *214124RD74C5D6A5DE*            *
*  4    *SAD23134230E24C5E4*            *
*  5    *ADSADF23D31E475424*ASDEF245A3RF*
*  6    *2134ADSA0106000020*523RFTYTFFTY*
*  7    *214124RD74C5D6A5DE*324FGSGSD523*
*  8    *SAD23134230E24C5E4*SADFS456V324*
*****************************************

Thanks in advance ! !

I risk looking like an idiot, but... Wouldn't UPDATE airport_shp SET geom2 = st_centroid(geom) what you're looking for? — Jorge_Freitas
– Jorge_Freitas, Commented Nov 29, 2017 at 10:54

Michel Milezzi · Accepted Answer · 2017-11-29 10:54:34Z

1

You need a UPDATE instead of INSERT:

UPDATE airport_shp SET geom2 = st_centroid(geom);

answered Nov 29, 2017 at 10:54

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

How to insert selection between an interval in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related