[postgis-users] Creating a new table with geometry with different number of rows from another table: how to do this

[Matthew Pettis]

Thanks both for your answers... I'm going to try Leo's solution.
Matt

On Wed, Jul 15, 2009 at 2:29 PM, Matthew Pettis <matthew.pettis at gmail.com>wrote:

> Hi,
>
> I’m trying to find the right incantation on how to create a new table with
> a geometry column from an old one efficiently.  Here’s the essential query I
> want to do...
>
> --------------------------------------------------------
> CREATE TABLE shp_mcd_centroids AS
> SELECT DISTINCT
>     mcd_name,
>     "type",
>     Centroid( Collect ( the_geom ) ) as the_geom
> FROM
>     shp_mcd
> GROUP BY
>     mcd_name,
>     "type"
> ;
> --------------------------------------------------------
>
>
> the_geom in shp_mcd is of type MULTIPOLYGON.  The_geom in shp_mcd_centroids
> will be of type POINT or MULTIPOINT (I’m not sure which, an not sure how to
> tell).  What I am missing is that I want to use AddGeometryColumn() to
> register the new the_geom column in the geometry_columns table, and I want
> to add a ‘gid’ column that is just an incremental integer as a PK on this
> table, just like shp2pgsql created in my other tables.  I just can’t seem to
> get the right combination of SQL statements to do this.
>
> Below is my (best) attempt with the results as well.  Any help is
> appreciated.
>
> Thanks,
> Matt
>
>
>
>
>
>
>
>
>
>
>
>
>
> ================================================================================
>
> --------------------------------------------------------------------------------
> Code
>
> --------------------------------------------------------------------------------
> create table shp_mcd_centroids (
>     gid serial PRIMARY KEY,
>     mcd_name character varying(10),
>     "type" character varying(14)
> );
> SELECT
> AddGeometryColumn('public','shp_mcd_centroids','the_geom','26915','MULTIPOINT',1);
>
> INSERT INTO shp_mcd_centroids (mcd_name, "type", the_geom)
>     SELECT
>     DISTINCT
>         mcd_name,
>         "type",
>         Centroid( Collect ( the_geom ) ) as the_geom
>     FROM
>         shp_mcd
>     GROUP BY
>         mcd_name,
>         "type"
> ;
>
>
> --------------------------------------------------------------------------------
> Result
>
> --------------------------------------------------------------------------------
>
> psql:temp.sql:5: NOTICE:  CREATE TABLE will create implicit sequence
> "shp_mcd_centroids_gid_seq" for serial column "shp_mcd_centroids.gid"
> psql:temp.sql:5: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
> index "shp_mcd_centroids_pkey" for table "shp_mcd_centroids"
> CREATE TABLE
>                           addgeometrycolumn
> ---------------------------------------------------------------------
>  public.shp_mcd_centroids.the_geom SRID:26915 TYPE:MULTIPOINT DIMS:1
>
> (1 row)
>
> psql:temp.sql:19: ERROR:  new row for relation "shp_mcd_centroids" violates
> check constraint "enforce_geotype_the_geom"
>
>
>
> ================================================================================
>
>
>


-- 
One of the penalties for refusing to participate in politics is that you end
up being governed by your inferiors.
-- Plato

It is from the wellspring of our despair and the places that we are broken
that we come to repair the world.
-- Murray Waas
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090715/777e7c54/attachment.html>