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

[Hugues François]

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