BostonGIS Blog

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)

As many people may have heard and as Mateusz kindly already commented on; Leo and I already mentioned in our Postgres OnLine Journal site, we are writing a book specifically focused on everything PostGIS. The hard copy version is do out around January of 2010, but you can pre-buy now and view the draft chapters as we write them. We are currently working on chapter 4 and the helper appendices and will try to get out more content before the end of May. Of course the more people pre-buy the more encouraged we will be to write quickly. You can download the first chapter for free from Manning: PostGIS in Action

As Brent Wood kindly commented on our Author Blog -- where is the primer on SQL and writing functions? before he quickly observed we have it planned for the appendix. Originally we stupidly had that as part of the meat of the book, but as our editor, Sebastian, and Marjan Bace rightfully commented, such things would bore people to death who are familiar with these things. So we are stuffing these in the appendix where you can refer to them, as we start doing wacky things you have never seen before done or envisioned relational databases can do, and where they don't interrupt the flow of the book and frustrate people already intimate with databases who want to learn about spatial.

Our book has now reached pre-book sales and nicely we've already got some purchases. You too can buy a copy (whether just MEAP/eBook or MEAP/eBook/hardcopy, start reading, make comments and thus help guide the direction of the final book. We look forward to getting feedback from people and hope this will be a valuable resource for those using PostGIS and future PostGIS users as well as encouraging adoption of PostGIS.

Below is more or less a repeat of what we posted to the PostGIS newsgroups with some minor edits:

PostGIS 1.4 out soon

PostGIS 1.4 will be out soon, which will be good because it feels like forever we've had this release baking in the oven. The key changes are as follows: Which many are detailed in New in PostGIS 1.4

• ST_IsValidReason() -- requires GEOS 3.1 -- will tell you why a geometry is not valid. Its a complement to ST_IsValid() which has existed for as long as I can remember
• Improvements in all the aggregate functions ST_Collect, ST_Union, ST_Accum to use new array logic that can better handle collecting of many geometries. You won't notice this benefit much until you start collecting and unioning 1000s of geometries
• Name change of hidden st*garray functions to be exposed as ST_Collect(geometry[]), ST_Union(geometry[]) ..etc
• Cascade Aggregate Union -- requires GEOS 3.1 -- need I say more?
• Prepared geometry - required GEOS 3.1
• Better debugging facilities and cleaner code base

Sadly ST_DumpPoints did not make it into 1.4, and we seem to have jumped to 2.0 thinking. Where is 1.5? 2.0 seems offly ambitious (geodetic, better 3d, WKT Raster, restructure to allow support for more types, improved curve) that I feel we really need at least a 1.5 to cushion the path. There are a lot of things we have left on the plate such as ST_DumpPoints, Steve Frost's Tiger Geocoder upgrade for new tiger 2007/2008 format, minor enhancements to distance speed and functionality and dumper that don't require major restructure that I feel those should just come first and that need only be a 3-6 month incubation. Given our new policy of not introducing new functions in minor releases, it would seem almost necessary to have at least a 1.5