[postgis-users] PostGIS 1.5.3-2 Help: ST_ConvexHull ( ST_Collect ( points from a join table

[Domenico Giusti]

Dear Hugues,

merci beaucoup! Your query works.
It sounds like I need to study more sql...

Thank you very much!

Cheers,

On 02/27/2015 09:41 PM, Hugues François wrote:
> Hello,
> 
> It sounds like an sql problem more than a postgis one but the query below should work, assuming the code used is unique for table_record (which should be the case for the foreign key on point_record).
> 
> UPDATE table_record AS foo
> SET geom  = bar.geom
> FROM (
> SELECT p.code,
> ST_ConvexHull(ST_Collect(p.geom)) AS geom_hull FROM record_point AS p WHERE p.code = 'D19C5P05'
> GROUP BY p.code;
> ) AS bar
> WHERE foo.code = bar.code
> 
> Regards,
> 
> Hugues.
> 
> -----Message d'origine-----
> De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Domenico Giusti
> Envoyé : vendredi 27 février 2015 20:25
> À : Postgis users
> Objet : [postgis-users] PostGIS 1.5.3-2 Help: ST_ConvexHull ( ST_Collect ( points from a join table
> 
> Dear list,
> 
> I have two tables and I need help:)
> 
> * table_record with a 3d polygon geom
> 	id, primary key
> 	code,
> 	geom
> * table_record_point with a 3d point geom
> 	id, primary key
> 	code, reference table_record(code)
> 	x,
> 	y,
> 	z,
> 	geom
> 
> The relation is one-to-many, as one record could have one or more points.
> 
> I would like to update record.geom with a ConvexHull 3d polygon collecting points from record_point.geom for some records.
> 
> I can print out the geom_hull:
> 
> SELECT p.code,
> ST_ConvexHull(ST_Collect(p.geom)) AS geom_hull FROM record_point AS p WHERE p.code = 'D19C5P05'
> GROUP BY p.code;
> 
> but I miss the way to join the tables and update the record.geom.
> 
> Thanks for any help!
> 
> --
> Domenico Giusti <dncgst at gnewarchaeology.it> GPG keyID: 2048R/A3AB7054F6E5D778 _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 

-- 
Domenico Giusti <dncgst at gnewarchaeology.it>
GPG keyID: 2048R/A3AB7054F6E5D778