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

[paul.malm at lfv.se]

Thanks  everyone!

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För didier peeters
Skickat: den 4 juli 2019 14:46
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] creating points from polygons, storing all attributes

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<mailto: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] 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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190704/c7df1d63/attachment.html>