[postgis-users] Appending geometry data from another table

[Bruce Rindahl]

Thanks for the hint. After testing I got the final solution:
 INSERT into county_parcels SELECT
'Archuleta' as county,
cast(GeomFromText(asText(the_geom),26913) as geometry) as the_geom
FROM county_temp;

I needed to cast the result as a geometry type and specify the SRID for the
constraint on the table.

If there is a cleaner solution I would love to hear it.

THANKS!!
Bruce


-----Original Message-----
From: Michael Fuhr [mailto:mike at fuhr.org] 
Sent: Friday, October 20, 2006 7:24 PM
To: rindahl at lrcwe.com; PostGIS Users Discussion
Subject: Re: [postgis-users] Appending geometry data from another table

On Fri, Oct 20, 2006 at 11:33:57AM -0600, Bruce Rindahl wrote:
> I have an existing table that I want to append new data from another
table.
> I can't figure out how to append the geometry.  My query looks something
> like this:
> 
> INSERT into county_parcels SELECT
> 'Archuleta' as county,
> the_geom as the_geom
>  FROM county_temp;
> 
> The error is:
> 
> ERROR:  column "the_geom" is of type geometry but expression is of type
> character varying

Since the INSERT doesn't specify a column list, make sure the first
two columns in county_parcels match the types of the corresponding
columns in the SELECT.  The error suggests that county_temp.the_geom
is a varchar, not a geometry; if so then you could use a constructor
like GeomFromText or GeomFromEWKT to convert the varchar to a geometry.
For example:

INSERT INTO county_parcels
SELECT 'Archuleta', GeomFromText(the_geom)
FROM county_temp;

-- 
Michael Fuhr