That’s because you named the new geometry « the_geom » like the old one, and using « * » gets you all the attributes including « the_geom » . So either you list all the attributes you want to keep in the SELECT instead of using the « * » like this :
CREATE TABLE NewPointLayer AS SELECT attr1, attr2, attr3, attr4, ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM OldPolygonLayer , or you use another name for your new geometry and you drop the old geometry afterward (if you don’t want to have these 2 different geometry columns in your new table), like this : CREATE TABLE NewPointLayer AS SELECT *, ST_PointOnSurface(the_geom)::geometry(Point,32632) AS geom FROM OldPolygonLayer ; ALTER TABLE NewPointLayer DROP COLUMN the_geom; Didier > Le 4 juil. 2019 à 14:26, [email protected] a écrit : > > Thanks, I’ve tried that but get this error: > ERROR: column "the_geom" is specified more than once SQL state: 42701 > /Paul > > Från: postgis-users [mailto:[email protected] > <mailto:[email protected]>] För Derek Watling > Skickat: den 4 juli 2019 13:55 > Till: PostGIS Users Discussion > Ämne: Re: [postgis-users] creating points from polygons, storing all > attributes > > CREATE TABLE NewPointLayer AS SELECT *, > ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM > OldPolygonLayer > > From: postgis-users <[email protected] > <mailto:[email protected]>> On Behalf Of [email protected] > <mailto:[email protected]> > Sent: Thursday, 04 July 2019 13:47 > To: [email protected] <mailto:[email protected]> > Subject: [postgis-users] creating points from polygons, storing all attributes > > Hi, > I would like to create a point table from a polygon table. One point in each > polygon > Query = CREATE TABLE NewPointLayer AS SELECT > ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM > OldPolygonLayer > > But how do I write the query to get all the attributes from OldPolygonLayer > to the NewPointLayer? > > Thanks in advance, > Paul > > > _______________________________________________ > postgis-users mailing list > [email protected] <mailto:[email protected]> > https://lists.osgeo.org/mailman/listinfo/postgis-users > <https://lists.osgeo.org/mailman/listinfo/postgis-users>
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
