<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">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 :<div class=""><br class=""></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">CREATE TABLE NewPointLayer AS </span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">SELECT</span><span style="background-color: rgb(245, 236, 0);" class=""><span style="font-family: Consolas; font-size: 13.333333015441895px;" class=""> attr1, attr2, attr3, attr4</span><span style="font-family: Consolas; font-size: 13.333333015441895px;" class="">,</span></span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class=""> </span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom </span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">FROM OldPolygonLayer</span></div><div class=""><br class=""></div><div class="">, 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 :</div><div class=""><br class=""></div><div class=""><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">CREATE TABLE NewPointLayer AS </span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">SELECT </span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: yellow;" class="">*,</span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class=""> </span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">ST_PointOnSurface(the_geom)::geometry(Point,32632) AS </span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(245, 236, 0);" class="">geom</span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class=""> </span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">FROM OldPolygonLayer ;</span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class=""><br class=""></span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">ALTER TABLE </span><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">NewPointLayer</span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class="">DROP COLUMN the_geom;</span></div><div class=""><span style="font-family: Consolas; font-size: 13.333333015441895px; background-color: rgb(232, 242, 254);" class=""><br class=""></span></div><div class="">Didier</div><div class=""><br class=""></div><div class=""><br class=""></div><div><blockquote type="cite" class=""><div class="">Le 4 juil. 2019 à 14:26, <a href="mailto:paul.malm@lfv.se" class="">paul.malm@lfv.se</a> a écrit :</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="color: rgb(31, 73, 125);" class="">Thanks, I’ve tried that but get this error:<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 9.5pt; font-family: Consolas; color: rgb(34, 34, 34); background-color: white; background-position: initial initial; background-repeat: initial initial;" class="">ERROR: column "the_geom" is specified more than once SQL state: 42701<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 9.5pt; font-family: Consolas; color: rgb(34, 34, 34); background-color: white; background-position: initial initial; background-repeat: initial initial;" class="">/Paul</span><span lang="EN-US" style="color: rgb(31, 73, 125);" class=""><o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" class=""><o:p class=""> </o:p></span></div><div class=""><div style="border-style: solid none none; border-top-width: 1pt; border-top-color: rgb(181, 196, 223); padding: 3pt 0cm 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><b class=""><span style="font-size: 10pt; font-family: Tahoma, sans-serif;" class="">Från:</span></b><span style="font-size: 10pt; font-family: Tahoma, sans-serif;" class=""><span class="Apple-converted-space"> </span>postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org" style="color: purple; text-decoration: underline;" class="">mailto:postgis-users-bounces@lists.osgeo.org</a>]<span class="Apple-converted-space"> </span><b class="">För<span class="Apple-converted-space"> </span></b>Derek Watling<br class=""><b class="">Skickat:</b><span class="Apple-converted-space"> </span>den 4 juli 2019 13:55<br class=""><b class="">Till:</b><span class="Apple-converted-space"> </span>PostGIS Users Discussion<br class=""><b class="">Ämne:</b><span class="Apple-converted-space"> </span>Re: [postgis-users] creating points from polygons, storing all attributes<o:p class=""></o:p></span></div></div></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class="">CREATE TABLE NewPointLayer AS SELECT<span class="Apple-converted-space"> </span><span style="background-color: yellow; background-position: initial initial; background-repeat: initial initial;" class="">*,</span><span class="Apple-converted-space"> </span>ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM OldPolygonLayer</span><span lang="EN-ZA" class=""><o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-ZA" class=""><o:p class=""> </o:p></span></div><div class=""><div style="border-style: solid none none; border-top-width: 1pt; border-top-color: rgb(225, 225, 225); padding: 3pt 0cm 0cm;" class=""><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><b class=""><span lang="EN-US" class="">From:</span></b><span lang="EN-US" class=""><span class="Apple-converted-space"> </span>postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" style="color: purple; text-decoration: underline;" class="">postgis-users-bounces@lists.osgeo.org</a>><span class="Apple-converted-space"> </span><b class="">On Behalf Of<span class="Apple-converted-space"> </span></b><a href="mailto:paul.malm@lfv.se" style="color: purple; text-decoration: underline;" class="">paul.malm@lfv.se</a><br class=""><b class="">Sent:</b><span class="Apple-converted-space"> </span>Thursday, 04 July 2019 13:47<br class=""><b class="">To:</b><span class="Apple-converted-space"> </span><a href="mailto:postgis-users@lists.osgeo.org" style="color: purple; text-decoration: underline;" class="">postgis-users@lists.osgeo.org</a><br class=""><b class="">Subject:</b><span class="Apple-converted-space"> </span>[postgis-users] creating points from polygons, storing all attributes<o:p class=""></o:p></span></div></div></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-ZA" class=""><o:p class=""> </o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" class="">Hi,<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" class="">I would like to create a point table from a polygon table. One point in each polygon<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class="">Query = CREATE TABLE NewPointLayer AS SELECT ST_PointOnSurface(the_geom)::geometry(Point,32632) AS the_geom FROM OldPolygonLayer<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class=""><o:p class=""> </o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class="">But how do I write the query to get all the attributes from OldPolygonLayer to the NewPointLayer?<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class=""><o:p class=""> </o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class="">Thanks in advance,<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class="">Paul<o:p class=""></o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" style="font-size: 10pt; font-family: Consolas; color: rgb(187, 0, 94); background-color: rgb(232, 242, 254); background-position: initial initial; background-repeat: initial initial;" class=""><o:p class=""> </o:p></span></div><div style="margin: 0cm 0cm 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span lang="EN-US" class=""><o:p class=""> </o:p></span></div></div><span style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">_______________________________________________</span><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">postgis-users mailing list</span><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="mailto:postgis-users@lists.osgeo.org" style="color: purple; text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">postgis-users@lists.osgeo.org</a><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" style="color: purple; text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></blockquote></div><br class=""></div></body></html>