0

I am quite new to PostGIS.

I need take some points from an existing table and create a multipoint and save this.

How would i go about this?

The only way i can think without creating a new table is:

Alter table
alter column geom TYPE geometry (MULTIPOINT,3857) USING ST_Multi(geom);

if i had to create a table what would the statement look like when using the x,y of the points in the point table?

Ive been asked to then find the centre of the mutlipoint, I know i need to use ST_Centroid but what would the code look like?

  • Welcome to GIS SE! As a new user be sure to take the Tour where you will see that there should be only one question asked per question under our focussed Q&A format. – PolyGeo Jun 19 '18 at 7:21
  • adressing your first question as the main: it´s a shot in the dark what to advise here as there is a plethora of options. first off: in general, I´d advise against converting single-geom-per-row into multi-geom-per-row in the same table and simply altering the column type wouldn´t get you anywhere. how do you determine which points to collect into a MULTIPOINT? – ThingumaBob Jun 19 '18 at 7:52
  • The table has only 10 points so i would be using all 10. I guess it would be better to create a new table? – Tom Piggott Jun 19 '18 at 8:02
  • seems silly for a input table that size, but IMO that would be the proper way, at least for robustness and ease of use, and also concerning the internal table statistics and the spatial index. you can always drop the other one if you don´t need it. one more thing: do those points have singular attributes that need to be aggregated (i.e. you are collecting geometries, thus reducing rows; you need to decide what to to with the other columns when those rows are reduced)? – ThingumaBob Jun 19 '18 at 8:14
  • Thanks for the reply, no aggregation of the points is required – Tom Piggott Jun 19 '18 at 8:48
0

If I understand you correctly you could do this:

CREATE TABLE point_union
AS(SELECT ST_Multi(ST_Union(geom))::geometry(MultiPoint, 3857) AS geom
FROM your_table);

CREATE TABLE center
AS(SELECT ST_Centroid(geom)
FROM point_union);

You then get a MultiPoint Layer point_union and a centroid layer center of point_union.

EDIT: To clarify my answer, from what you described, I assumed you might be better of using ST_Union.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.