One thing we are really fond of are the ANSI SQL Standard Row Constructors. PostgreSQL has supported this feature since 8.2 I believe and SQL Server 2008 supports it too, but prior SQL Server's do not. MySQL has supported for as far back as I can remember.
Any rate we thought what a fun way to play around with row constructors in SQL Server 2008 than to write queries we would write in PostGIS in SQL Server 2008. The results were a bit surprising when testing out Centroid. The STPointOnSurface I can accept as okay since I think the point is only guaranteed to be on the surface and when you look at say IBM DB II specs and ESRI manual it suggests these should only work for POLYGONS and MULTIPOLYGONS. Both PostGIS and SQL Server 2008 seem to return point on surface for any common geometry and both are on the surface just a different point. For Centroid however SQL Server as far as we can tell doesn't work for points, multipoints, linestrings and I presume probably only works for POLYGONS and MULTIPOLYGONs. Checking the docs confirms that SQL Server does not return a centroid for anything but POLYGONS and MULTIPOLYGONS. This is a bit of a bummer. Well given that PointOnSurface always works and that is what a lot of people expect from Centroid I guess the lesson is to use STPoinOnSurface. Centroid is not guaranteed to return a point on the surface in any spatial database we know of especially when you have polygons with holes.
UPDATE: On further inspection it appears the MM specs (at least the version I have seen) only specify centroid and point on surface for surface geometries (polygons and multipolygons) and spatial databases seem a bit split as to how they extend the spec in this regard. PostGIS extends the concept to apply to all common geometries. SQL Server extends it for PointOnSurface but not for Centroid. IBM extends it for centroid but not PointOnSurface and Oracle appears to read the spec to the letter not extending for either.
Anyrate here are 2 queries which are in theory identical but not.
--Query on SQL Server 2008
SELECT name, the_geom.STCentroid().STAsText() As cent_geomwkt,
the_geom.STPointOnSurface().STAsText() As pos_geomwkt
FROM
( VALUES ('mpoint', geometry::STGeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)',0 ) ),
('mlinestring', geometry::STGeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))',0) ),
('polygon', geometry::STGeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25),
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))', 0) ) ) As foo(name, the_geom);
--Results
name cent_geomwkt pos_geomwkt
point NULL POINT (-1 1)
mpoint NULL POINT (2 3)
mlinestring NULL POINT (0 0.5)
polygon POINT (1.125 0) POINT (1.1666666666666667 1.1666666666666667)
Same query on PostGIS :
SELECT name, ST_AsText(ST_Centroid(the_geom)) As cent_geomwkt,
ST_AsText(ST_PointOnSurface(the_geom)) As pos_geomwkt
FROM (VALUES ('point', ST_GeomFromText('POINT(-1 1)') ),
('mpoint', ST_GeomFromText('MULTIPOINT(-1 1, 0 0, 2 3)') ),
('mlinestring', ST_GeomFromText('MULTILINESTRING((0 0,0 1,1 1),(-1 1,-1 -1))') ),
('polygon', ST_GeomFromText('POLYGON((-0.25 -1.25,-0.25 1.25,2.5 1.25,2.5 -1.25,-0.25 -1.25),
(2.25 0,1.25 1,1.25 -1,2.25 0),(1 -1,1 1,0 0,1 -1))') ) )
As foo(name, the_geom);
--Results
name | cent_geomwkt | pos_geomwkt
-------------+-------------------------------------------+-----------------
point | POINT(-1 1) | POINT(-1 1)
mpoint | POINT(0.333333333333333 1.33333333333333) | POINT(-1 1)
mlinestring | POINT(-0.375 0.375) | POINT(0 1)
polygon | POINT(1.125 0) | POINT(-0.125 0)