The order of things will be different if a geography STUnion itself

Question

is this by design? the result seems not very logical.

 

just try this one and you'll see

DECLARE @g geography;

SET @g = geography::STGeomFromText('POLYGON((-122.358 47.653, -122.348 47.649, -122.348 47.658, -122.358 47.653))', 4326);

SELECT @g.STUnion(@g).ToString(), @g.ToString();

Answer 1

That's expected behaviour - even though, in this case, you haven't changed the points included in the polygon, the result of STUnion is still based on a calculation, so there's no reason to expect the start point and end point of the polygon to remain the same.

---

twitter: @alastaira blog: http://alastaira.wordpress.com/

Answer 2

Hi tanoshimi

Wow - you do seem to post an AWFUL lot on here. When searching for almost every spatial problem I'm trying to solve, I end up on social.msdn and you seem to be the one answering all the questions. Your MVP status is richly deserved! Thanks so much for all your time and effort.

To the problem at hand... I'm having an issue with STUnion and it's a little deeper than just ordering.

I'm using STUnion as part of a solution to find the halfway point between two points, as in:

	SELECT
			GEOMETRY::STGeomFromWKB(
				@tile.STPointN(1).STUnion(@tile.STPointN(2)).STAsBinary(),
				4326
			).STEnvelope().STCentroid().ToString()

This works all the time, except when one of the points has a long of -180 and the other has a long in the negative range, i.e. both points in the western hemisphere and one of them on -180 meridian.

In this situation, STUnion returns a multipoint with the long set as 180, when it ought to be -180. The result of course is that I end up with two points covering the inverse of the points I expected.

Below is some sample SQL. You can see the result of the halfway point, the result of the union, and then the result if the union had returned -180 instead of 180:

DECLARE	
	@tile geography = 'POLYGON((180 0, 270 0, 180 90, 180 0))'

	SELECT
			GEOMETRY::STGeomFromWKB(
				@tile.STPointN(1).STUnion(@tile.STPointN(2)).STAsBinary(),
				4326
			).STEnvelope().STCentroid().ToString()


	SELECT
			@tile.STPointN(1).STUnion(@tile.STPointN(2)).ToString()



DECLARE @tile2 geography = 'MULTIPOINT ((-90 0), (-180 0))'

SELECT GEOMETRY::STGeomFromWKB(@tile2.STAsBinary(), 4326).STEnvelope().STCentroid().ToString()

So, I'm wondering how to fix this. Firstly however, I'm wondering if this is a bug? What is your opinion on the matter and is this something that ought to be reported back to MSFT?

The code will be really hacky, but I could insert a case statement to check to see if one of the points in on the -180/+180 meridian AND if the other point is between -180 and 0 or 180 and 360 and if so, then attempt to change the multipoint outcome by converting to text, inserting the minus sign and converting back to geog.... OMG... Do I need to say more?

Any bright ideas come to mind?

Any suggestions welcome... I'm working on this problem in my spare time, which is to create my own tessellation pattern for a custom Quad-tree, and I've been trying to crack Step 1 for more than five days...

Thanks,

Paul