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

[Matthew Pettis]

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"


================================================================================
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090715/42eca763/attachment.html>