[postgis-users] creating points from polygons, storing all attributes

[didier peeters]

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, paul.malm at lfv.se 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:postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>] 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 <postgis-users-bounces at lists.osgeo.org <mailto:postgis-users-bounces at lists.osgeo.org>> On Behalf Of paul.malm at lfv.se <mailto:paul.malm at lfv.se>
> Sent: Thursday, 04 July 2019 13:47
> To: postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> 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
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> https://lists.osgeo.org/mailman/listinfo/postgis-users <https://lists.osgeo.org/mailman/listinfo/postgis-users>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190704/36f4b33e/attachment.html>