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

[Paragon Corporation]

Matthew,
 
You want to create the table first
 
something like
 
CREATE TABLE shp_mcd_centroid(gid SERIAL PRIMARY KEY, mcd_name varchar(100),
type varchar(100));
 
then do the 
 
SELECT AddGeometryColumn ....
(http://postgis.refractions.net/documentation/manual-1.4.0SVN/AddGeometryCol
umn.html  - which will both register and create the geometry column)
 
Then do
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"
;
 
Leo

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Matthew
Pettis
Sent: Wednesday, July 15, 2009 3:29 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Creating a new table with geometry with
differentnumber of rows from another table: how to do this


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','MULTIPOIN
T',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"




============================================================================
====