CODEWITHSUNDEEP
sqldatabasepostgresqlpostgis
Capan
Capan

Reputation: 756

How to insert selection between an interval in PostgreSQL?

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 ! !

Upvotes: 0

Views: 44

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11175

You need a UPDATE instead of INSERT:

UPDATE airport_shp SET geom2 = st_centroid(geom);

Upvotes: 1

Related Questions