# Geometry collections containing other multielement geometries

• ### Question

• I experienced some unexpected results using STNumGeometries() on a geometrycollection, and it's led me to ask a question regarding the value returned by this method:

The particular example in question was a geometrycollection that contained other multielement geometries - multipoints and multilinestrings.
I know this is unusual - when you use STUnion() to create a geometrycollection, it normally seperates each multi-element geometry out into single element geometries contained in the collection (i.e. A union between a multilinestring and a multipoint will create a geometrycollection containing a lot of individual linestrings and points). However, this data was created from an external application, and as far as I understand the OGC specifications, a geometrycollection may contain any instantiable type of geometry, so there is no reason why this should not be okay.

The problem is that STNumGeometries() does not seem to be counting the individual geometries contained within each multielement geometry of the collection, but just the number of multielement geometries themselves. To demonstrate:

Code Snippet

DECLARE @GeometryCollection geometry
SET @GeometryCollection = geometry::STGeomFromText('
GEOMETRYCOLLECTION(
MULTIPOINT((32 -122),(23 -12),(-3 12)),
MULTILINESTRING((0 0,12 20),(12 23,13 30))
)
',4326)

SELECT
@GeometryCollection.STNumGeometries()

According to Books Online, the STNumGeometries() method returns "the number of geometries that make up a geometry (geography) instance.". If you were to perform STNumGeometries() on the multipoint and the multilinestring elements seperately, you would get the values 3 and 2 respectively. I was therefore expecting the result of the method on the geometrycollection containing both these elements to be 5, but instead the method only returns 2.

Is this behaviour by design, or is this a flaw in the assumption of the STNumGeometries() method that each element contained within a geometrycollection will only be a single-element geometry?
Wednesday, June 4, 2008 7:30 PM

• This is the design of GeometryCollection - calls to NumGeometries and GeometryN are local to the specific instance.  Nested collections in a GeometryCollection definitely seem a little funky, but the semantics should be consistent.  Consider also the following:

select geometry::Parse('GEOMETRYCOLLECTION (POINT EMPTY)').STNumGeometries()

it returns 1 as the GeometryCollection has 1 child geometry, even though calling STNumGeometries() on the child would return 0 as it is empty.

Feel free to submit documentation feedback on the description in BOL if you think it should be cleaned up to more clearly account for this case.

Steven

Wednesday, June 4, 2008 10:03 PM
• Absolutely nobody wishes the OGC Simple Feature Spec were more clear and exact than me.  Since the behavior was not clearly defined, we went with the implementation that made the most sense and was the most usable.

If STNumGeometries were to return the total number of geometries in the collection, how would you distinguish between the structure GEOMETRYCOLLECTION (POINT, POINT, POINT) and GEOMETRYCOLLECTION (MULTIPOINT(POINT, POINT, POINT))? You couldn't, as STGeometryN would also have to be written to give the depth-first element N.  The structure of these functions would no longer match the structure of the WKT, which definitely seems wrong.

Since this behavior for GeometryCollections in general was left open, we did consider what the other implementations did in case there was an standard way to interpret this.  Here are my notes from this just to show the process we went through, don't take it as authoritative information about these products:

Oracle - does not implement ST_NumGeometries.  Similar function GETNUMELEM returns the number of elements based on their internal structure which does not map to the OGC type hierarchy, so a MultiPoint counts as 1 while a MultiLineString counts as the number of linestrings.  The example here returns 3, 1 for the MultiPoint and 2 for the MultiLineString.

DB2 - Does not support heterogeneous GeometryCollections, only the collections that are constrained to a single element type.

JTS/PostGIS - Does not support spatial operations on GeometryCollections in general, as the semantics are so unclear.  I’m not sure if this includes STNumGeometries or not, however.

Friday, June 6, 2008 6:01 PM

### All replies

• This is the design of GeometryCollection - calls to NumGeometries and GeometryN are local to the specific instance.  Nested collections in a GeometryCollection definitely seem a little funky, but the semantics should be consistent.  Consider also the following:

select geometry::Parse('GEOMETRYCOLLECTION (POINT EMPTY)').STNumGeometries()

it returns 1 as the GeometryCollection has 1 child geometry, even though calling STNumGeometries() on the child would return 0 as it is empty.

Feel free to submit documentation feedback on the description in BOL if you think it should be cleaned up to more clearly account for this case.

Steven

Wednesday, June 4, 2008 10:03 PM
• I *suspect* its by design, and I think its a curious choice.

OGC 99-049 uses similar language to BOL, "Returns the number of geometries in this GeometryCollection." But that's all that it says. No examples.

OGC 06-104r3 defines a test suite including the following item as 'Route 75' (as adapted for SQL Server 2008 CTP 6).

Code Snippet
use scratch
go
create table dbo.carto(
itemID tinyint identity(1,1) not null primary key,
descr nvarchar(50) not null,
geo geometry not null);
go
insert into dbo.carto values ('Route 75',geometry::STMLineFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))', 101));
go
select geo.STNumGeometries() from dbo.carto;
go

OGC 06-104r3 says the answer should be two (not eight) and that's what SQL 2008 is returning as well. See section C3.3, table C4, entry T30

I guess the justification is that elements in a collection aren't the atomic, the collection is.

I am having a hard time finding examples like yours in the OCG test suite.

Thursday, June 5, 2008 12:29 AM
• Thanks for the responses. I realise that this is a slightly edge-case situation that is unlikely to affect many people - I too couldn't find any specific examples like this anywhere (but nor could I find anything to suggest that this was invalid either). But then isn't that the point of releasing CTP editions for the community to test?

We're talking about semantics more than anything else (again!), but I guess I would describe STNumGeometries() as counting the number of elements, or items,  in a geometrycollection, rather than the number of geometries.
Thursday, June 5, 2008 8:11 AM
• Hi Folks,

FWIW, the way we measure this is a very typical way to measure the size of a set.  Ask a set theorist what the cardinality of {1, {2, 3, 4}} is, and he'll say 2.  As Steven points out, an empty geometry has size 0, but a geometry collection with an empty geometry in it has size 1.  Again, this is what a mathematician would say: 0, the empty set, has size 0, but {0} has size 1, {0, {0}} has size 2, etc.

(I can't find a good symbol for the emtpy set on these forums...)

Cheers,
-Isaac
Thursday, June 5, 2008 1:22 PM
• Agreed, yet, somebody really needs to encourage the OCG to:

a.) Write more formalized specifications then. When you stop to consider that many other methods are mathematically defined in their specs but NumGeometries isn't, it leads to an open interpreation.

b.) Write more test cases that demonstrate that.

Then I think we can bang AllenB and crew for writing this with a variation of possible interpretations.

BTW, Issac, hopefully you can go back to some of the other posts here about Visualization and spill the eye candy for us.

Thursday, June 5, 2008 1:31 PM
• Hear, hear! I second both those suggestions.

I'm quite comfortable with how to measure the size of a set, and if that's what STNumGeometries() does, then that's fine.... It's just I don't think that this represents "the number of geometries that make up a geometry (geography) instance".

(and also off-topic, I'd also like to see more of the spatial results visualisation tab! Can't make it to tech-ed... damn... does it do geography as well as geometry? What projections are supported? When can I play with it?!)

Thursday, June 5, 2008 2:23 PM
• This feels like we're playing a little fast and loose with differences between sets and collections. But I'll go back OCG definitions. They don't declare Collections as sets (and least in 99-049). So it boils down to what's the difference between and a collection?

Well, for one thing, Sets don't allow duplicates, so three seems like a strange answer here when I put on my rose-coloured mathematician glasses.

Code Snippet

declare @gc geometry=geometry::STGeomFromText('GEOMETRYCOLLECTION(POINT(3 3), POINT(5 5), POINT(3 3))',100);

select @gc.STNumGeometries();

I'm also not to hip on idealzing collections about Sets since there's no requirement to persist or retreive the members of a set in any order, but -- as we've seen -- the order of point definition is essential in defining a geography instance.

I'm okay with answers that SQL Server is generating and since the standards bodies aren't doing a good job of formal definitions, I don't see where the SQL Team needs to do that for them.

Thursday, June 5, 2008 11:40 PM
• Absolutely nobody wishes the OGC Simple Feature Spec were more clear and exact than me.  Since the behavior was not clearly defined, we went with the implementation that made the most sense and was the most usable.

If STNumGeometries were to return the total number of geometries in the collection, how would you distinguish between the structure GEOMETRYCOLLECTION (POINT, POINT, POINT) and GEOMETRYCOLLECTION (MULTIPOINT(POINT, POINT, POINT))? You couldn't, as STGeometryN would also have to be written to give the depth-first element N.  The structure of these functions would no longer match the structure of the WKT, which definitely seems wrong.

Since this behavior for GeometryCollections in general was left open, we did consider what the other implementations did in case there was an standard way to interpret this.  Here are my notes from this just to show the process we went through, don't take it as authoritative information about these products:

Oracle - does not implement ST_NumGeometries.  Similar function GETNUMELEM returns the number of elements based on their internal structure which does not map to the OGC type hierarchy, so a MultiPoint counts as 1 while a MultiLineString counts as the number of linestrings.  The example here returns 3, 1 for the MultiPoint and 2 for the MultiLineString.

DB2 - Does not support heterogeneous GeometryCollections, only the collections that are constrained to a single element type.

JTS/PostGIS - Does not support spatial operations on GeometryCollections in general, as the semantics are so unclear.  I’m not sure if this includes STNumGeometries or not, however.

Friday, June 6, 2008 6:01 PM
• Thankyou for the detailed response, Steven - I think you've justified the behaviour

Also, this seems a suitable point to say that I (and I'm sure many others) really do appreciate the open and honest debates that occur on these forums, particularly the direct input of people like Steven, Michael, and Isaac at Microsoft.
You could have easily chosen to reply and said "That's the way it is.", or not even bothered to reply at all. Instead we get a very reasoned response behind the decisions that have been made.

It really helps the developer community to feel connected and involved. So thanks, guys.
Friday, June 6, 2008 7:02 PM
• Thanks for your kind words.  From my perspective, these discussions have made the community an active part of our develipment process, and have been truly valuable in validating and improving our design.  I really appreciate the particpation of all of you who have kept us honest with tough questions throughout this process, and I'm glad you're finding it equally useful.

Cheers,

-Isaac

Sunday, June 8, 2008 10:50 PM
• I have to add my 5 cents worth and would like to say: Keep at it!

Thomas

Tuesday, June 17, 2008 8:01 AM