[postgis-users] Need a "polygon box" of a table instead of the bounding box that returns extent function

[Oscar Zamudio]

Nicolas,
What I want to do is an insert of the type INSERT INTO...VALUES .. as
follows:

INSERT INTO boundaries ( the_geom, the_name ) VALUES
 (some_geometry_data,'some_arbitrary_name')


My problem is that I want to replace the some_geometry_data value by the
result of the SELECT query. And of course I am not an expert user of SQL
statements so I don't know how to do that..
from my_street_table;
Maybe your example can work, I will try it later and let you know.

INSERT INTO boundaries ( the_geom, the_name ) SELECT
ST_ConvexHull(ST_Collect(the_geom)) as the_geom, 'mytablename' from
my_street_table;


Regards,

On Wed, May 5, 2010 at 5:52 AM, Nicolas Ribot <nicolas.ribot at gmail.com>wrote:

> On 5 May 2010 02:38, Oscar Zamudio <cmntlk at gmail.com> wrote:
> > Well, it works like a charm!
> > But I still need some advise. The query generates a polygon and I use
> astext
> > to get readable output. Now I create a new table that will contain a name
> > and the geometry result of the query. I want to use something like:
> > INSERT INTO boundaries ( the_geom, the_name ) VALUES (SELECT
> > ST_ConvexHull(ST_Collect(the_geom)) as the_geom from my_street_table),
> > 'mytablename');
> > This can't work but I don't know how to do it better.
> > By now I first returned the polygon as text and then did the following:
> > INSERT INTO boundaries ( the_geom, the_name ) VALUES
> > (ST_GeomFromText('POLYGON((-58.8714168974345
> > -34.5696042275729,-58.9620017725354 .......))'), 'mytablename');
> > With this and pgsql2shp I generated a shapefile for the boundary. I could
> > draw the streets (original shapefile) and the boundary both together and
> > they match perfect!
> > Please excuse me if I am making a silly question but I'm a newbie.
> > Thanks in advance
> >
>
> Hi,
> I'm not sure I understand your question.
> You want to find the INSERT syntax allowing to use a query to provide
> the table with values ?
>
> INSERT also accepts a query to get the values
> (http://www.postgresql.org/docs/8.4/static/sql-insert.html):
>
> NSERT INTO boundaries ( the_geom, the_name )
> SELECT ST_ConvexHull(ST_Collect(the_geom)) as the_geom, 'mytablename'
> from my_street_table;
>
> Nicolas
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20100505/8c62214c/attachment.html>