Thanks both for your answers... I'm going to try Leo's solution.<div><br></div><div>Matt<br><br><div class="gmail_quote">On Wed, Jul 15, 2009 at 2:29 PM, Matthew Pettis <span dir="ltr"><<a href="mailto:matthew.pettis@gmail.com">matthew.pettis@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div><font face="'courier new', monospace"><span style="font-size:large">Hi,</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">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...</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">CREATE TABLE shp_mcd_centroids AS</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">SELECT DISTINCT</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> mcd_name,</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> "type",</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> Centroid( Collect ( the_geom ) ) as the_geom</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">FROM</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> shp_mcd</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">GROUP BY</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> mcd_name,</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> "type"</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">;</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">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.</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">Below is my (best) attempt with the results as well. Any help is appreciated.</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">Thanks,</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">Matt</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">================================================================================</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">Code</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">create table shp_mcd_centroids (</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> gid serial PRIMARY KEY,</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> mcd_name character varying(10),</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> "type" character varying(14)</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">);</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">SELECT AddGeometryColumn('public','shp_mcd_centroids','the_geom','26915','MULTIPOINT',1);</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">INSERT INTO shp_mcd_centroids (mcd_name, "type", the_geom)</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> SELECT</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> DISTINCT</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> mcd_name,</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> "type",</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> Centroid( Collect ( the_geom ) ) as the_geom</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> FROM</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> shp_mcd</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> GROUP BY</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> mcd_name,</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> "type"</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">;</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">Result</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">--------------------------------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">psql:temp.sql:5: NOTICE: CREATE TABLE will create implicit sequence "shp_mcd_centroids_gid_seq" for serial column "shp_mcd_centroids.gid"</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">psql:temp.sql:5: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "shp_mcd_centroids_pkey" for table "shp_mcd_centroids"</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">CREATE TABLE</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"> addgeometrycolumn</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large">---------------------------------------------------------------------</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"> public.shp_mcd_centroids.the_geom SRID:26915 TYPE:MULTIPOINT DIMS:1</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">(1 row)</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">psql:temp.sql:19: ERROR: new row for relation "shp_mcd_centroids" violates check constraint "enforce_geotype_the_geom"</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><font face="'courier new', monospace"><span style="font-size:large"><br>
</span></font></div><div><font face="'courier new', monospace"><span style="font-size:large">================================================================================</span></font></div>
<div><font face="'courier new', monospace"><span style="font-size:large"><br></span></font></div><div><br></div>
</blockquote></div><br><br clear="all"><br>-- <br>One of the penalties for refusing to participate in politics is that you end up being governed by your inferiors.<br>-- Plato<br><br>It is from the wellspring of our despair and the places that we are broken that we come to repair the world.<br>
-- Murray Waas<br>
</div>