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

[Eduin Carrillo]

I think you must use a bidimensional field:

...'MULTIPOINT',2)...

Eduin Yesid Carrillo Vega
Ingeniero Civil, Esp. Sistemas de Información Geográfica
Grupo IDE & GIG
Centro de Investigación y Desarrollo en Información Geográfica
Instituto Geográfico Agustín Codazzi - IGAC
Cr 30 48-51
Tel +57 1 3694000  ext 4305
Bogotá D.C., Colombia



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"
>
> ================================================================================
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>