[postgis-users] Insert geometry in a table

[Kai Behncke]

On Fri, 9 Mar 2007 14:11:05 +0100, Kai Behncke wrote
> Hello list,
> 
> I wonder how to integrate a geometry in a table with INSERT.
> 
> If I write: INSERT INTO "city" ("gid","name",the_geom) VALUES
> 
> ('189','osnabrueck','SRID=31467; MULTIPOLYGON(((3434250 5793220,3435960
> 5793220,3435960 5794870,3434250 5794870, 3434250 5793220)))');
> 
> this works fine.
> 
> But I want to get the geom out of a table to produce a new geomtry 
> with a buffer.
> 
> If I write: 
> INSERT INTO "city" (name,the_geom) SELECT
> 'test',astext(buffer(p.the_geom,500)) FROM poi p
> WHERE p.poinameid='Marienhospital';   this wokrs fine as well, but 
> only if there are no constraints in the table "city".
> 
> How can I make it that I insert the buffer(p.the_geom,500) as the geometrytype
> POLYGON and with SRID=31467??
> 
> Has anybody an idea?
> Thank you in advance.
> Kai
> 
Allright, got an idea by myself.
It works with:

INSERT INTO "city" (name,the_geom) SELECT
'hallo',setSRID((buffer(p.the_geom,500)),31467) FROM poi p
WHERE p.poinameid='Marienhospital';

To produce a multipolygon:

INSERT INTO "city" (name,the_geom) SELECT
'hallo',MULTI(setSRID((buffer(p.the_geom,500)),31467)) FROM poi p
WHERE p.poinameid='Marienhospital';
--
Dipl.-Geogr. Dipl.-Umweltwiss. Kai Behncke

Institut für Geoinformatik und Fernerkundung (IGF)
Universität Osnabrück
Kolpingstr. 7
49074 Osnabrück

Raum: 01/308
Tel.: +49 541 969-4450
E-Mail: mailto:kbehncke at igf.uni-osnabrueck.de 
Web:  http://www.igf.uni-osnabrueck.de/mitarbeiter/behncke/index.html