Find centroids of rows with Multipoint Geometry

I wish to find the centroid of a set of given points. The points will be rows of my SpatiaLite database and have Multipoint geometry. I checked the SpatiaLite manual but the ST_Centroid function I find says it takes as input a Surface s (while the corresponding function in PostGis takes as input Multipoint as well). I thought of creating a surface from the Multipoint rows but it seems I have to do it in this order: Multipoint->Linestring (with MakeLine)->Area (BuildArea)->Centroid (with ST_Centroid), something that seems a heavy workaround to even try. Of course I tried it but it does not work. Can anyone suggest a fast workaround?

• Can you describe the points more? Is each row a single point (i.e. one point as MULTIPOINT) and you want to find the centroid of a set of rows based on SELECT of some attribute, or does each row have a set of points (i.e. multiple points as MULTIPOINT), and you want to find the centroid of each row? Also, does centroid mean a straight average or do you need to weight the points? May 15, 2016 at 6:13
• Each row is a single point as in one point is a MULTIPOINT. Straight average is fine.
– lios
May 15, 2016 at 6:19

Your problem is very easy to resolve because the problem is only in the documentation. https://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html is indeed claiming that ST_Centroid requires Surface but it is not true and you can just feed in your MultiPoints as they are.

I made a a following test with spatialite-gui versions 1.8.0 and 2.0.0:

Query:

``````SELECT ST_AsText(ST_Centroid(ST_GeomFromText(
'MULTIPOINT (( 400 400 ), ( 340 420 ), ( 360 360 ))')));
``````

Result:

``````POINT(366.666667 393.333333)
``````

As @BradHards pointed out, because your source data contain multiple rows you must add ST_Collect into the query. The final query would be like like

``````SELECT ST_Centroid(ST_Collect(geometry)) from your_multipoint_table;
``````
• The source data was one point per row (see detail in comments "Each row is a single point as in one point is a MULTIPOINT."), so you'd need at least ST_Collect. May 16, 2016 at 2:15

``````spatialite> CREATE TABLE gis193636 (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, flag INTEGER NOT NULL);
spatialite> SELECT AddGeometryColumn( 'gis193636', 'geom', 0, 'MULTIPOINT', 'XY', 1);
1
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (1, GeomFromText('MULTIPOINT((0 0))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (1, GeomFromText('MULTIPOINT((10 10))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (1, GeomFromText('MULTIPOINT((0 10))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (1, GeomFromText('MULTIPOINT((10 0))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (2, GeomFromText('MULTIPOINT((10 5))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (2, GeomFromText('MULTIPOINT((10 2.5))', 0));
spatialite> INSERT INTO gis193636 (flag, geom) VALUES (2, GeomFromText('MULTIPOINT((10 7.5))', 0));
``````

That just set up the test data, which we can check here:

``````SELECT id, flag, AsEwkt(geom) FROM gis193636;
1|1|SRID=0;MULTIPOINT(0 0)
2|1|SRID=0;MULTIPOINT(10 10)
3|1|SRID=0;MULTIPOINT(0 10)
4|1|SRID=0;MULTIPOINT(10 0)
5|2|SRID=0;MULTIPOINT(10 5)
6|2|SRID=0;MULTIPOINT(10 2.5)
7|2|SRID=0;MULTIPOINT(10 7.5)
``````

This will be easier to follow if we just have plain points, so lets convert that:

``````spatialite> SELECT id, flag, AsEwkt(CastToPoint(geom)) FROM gis193636 WHERE flag=1;
1|1|SRID=0;POINT(0 0)
2|1|SRID=0;POINT(10 10)
3|1|SRID=0;POINT(0 10)
4|1|SRID=0;POINT(10 0)
``````

Now the centroid is an average, and you can calculate the averages for the two (independent) values using the sqlite avg() function:

``````spatialite> SELECT ST_X(CastToPoint(geom)), ST_Y(CastToPoint(geom)) FROM gis193636 WHERE flag=1;
0.0|0.0
10.0|10.0
0.0|10.0
10.0|0.0
spatialite> SELECT Avg(ST_X(CastToPoint(geom))), Avg(ST_Y(CastToPoint(geom))) FROM gis193636 WHERE flag=1;
5.0|5.0
``````

You can turn that back into a point with the `MakePoint` function, then cast that to MULTIPOINT if necessary.

We can see the centroid move by introducing those extra values (flag value of 2, all with X = 10):

``````spatialite> SELECT Avg(ST_X(CastToPoint(geom))), Avg(ST_Y(CastToPoint(geom))) FROM gis193636 WHERE flag<3;
7.14285714285714|5.0
``````

Note that if you build an area that encompasses the points and use ST_Centroid, you won't get the same result (i.e. the location of the points doesn't matter - just the envelope):

``````spatialite> SELECT AsEwkt(ST_Centroid(GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')));
SRID=0;POINT(5 5)
``````

So in your original idea, you won't get the centroid of the points unless they happen to be regularly spaced. If that is what you want (i.e. you really don't care about the points, just the boundary), then I'd use ST_Collect, then take the ST_ConvexHull of that, then apply ST_Centroid. To see that build up:

``````spatialite> SELECT AsEwkt(ST_Collect(geom)) FROM gis193636 WHERE flag=1;
SRID=0;MULTIPOINT(0 0,10 0,10 10,0 10)
spatialite> SELECT AsEwkt(ST_ConvexHull(ST_Collect(geom))) FROM gis193636 WHERE flag=1;
SRID=0;POLYGON((0 0,0 10,10 10,10 0,0 0))
spatialite> SELECT AsEwkt(ST_Centroid(ST_ConvexHull(ST_Collect(geom)))) FROM gis193636 WHERE flag=1;
SRID=0;POINT(5 5)
``````