MS SQL 2008 - Geometry Centroids

[pix101]

Hi Everyone,

Been on the list for a looong time but first time poster, really hope
someone can help me out!

I've been porting many of my spatial data maintenance processes to SQL
with the advent of spatial data support by MS SQL 2008 and increased
support by Mapinfo Professional. So far its been a great experience
and learning curve, and I've had some great successes. But I've run
into a few snags in recent months for which I can't think of a simple
solution, one of which is the way SQL calculates the Centroid of
geometries.

Polygon Centroids in Mapinfo Pro, are always contained/on the surface
of the geometry unless they are manually moved or manipulated. As
such, when the Centroid(X), Centroid(Y) functions are used in Mapinfo
Pro to update coordinate columns you always get a coordinate of
somewhere on the surface of the polygons regardless of the shape (i.e.
"U" shaped geometry or Panhandle Property boundaries).

MS SQL Server 2008 however, in the case of "U" shaped geometries for
instance, returns Long/Lat coordinates of the bounds of the geometry,
and therefore the centroid could be outside of the geometry.

My question is, do anyone know of a way to calculate centroids of
features to be on the surface of the feature.

Thanks in advance for any assistance
Rudi

[Lars I. Nielsen (GisPro)]

Hi Rudi,

I assume you mean "MS SQL Server 2008 however, ..., returns Long/Lat coordinates of THE CENTER OF the bounds of the geometry".

You probably won't find a simpe formula for this, as the geometry could be a multi-polygon of multiple tiny polynesian islands, so to speak.

I assume you're looking for a solution in MS/SQL (i.e. as a stored procedure or a function), as the Centroid() function in MapInfo Pro should do the trick very nicely.

Will a point anywhere on the surface do, or should it be as close to the bounds center as possible ? I think Pro has some logic to the latter effect.

Best regards / Med venlig hilsen
Lars I. Nielsen
GIS & DB Integrator
GisPro

[pix101]

Thanks for the prompt reply and for adding the missing detail to my
explanation.

Yes, a function or procedure with some logic(similar to that of MI
Pro) to maintain some sort of consistency is required. That's all,
not asking for much, hehe. I'm quite surprised that this is not an
OGC standard to have centroid on features, or even if it had to be a
"secondary centroid".

I applied my mind, and have some ideas of how to approach it, and it
will be quite complex I'm sure. I'm thinking of using the Centroid of
each polygon as a starting point for a line to be drawn out to the top
& and bottom and left to right, using the envelope of the polygon to
determine the extents. Then I'll calculate the length of the
intersection of each line where they intersect with the polygon. Then
use the mid point of the longest intersection segment, which is
nearest to the starting point as the centroid. This might end up a
bit more complicated once I get going, and if this works I can refine
it with further logic.

Thanks again
Rudi



On Oct 13, 10:27 am, "Lars I. Nielsen (GisPro)" <L...@gispro.dk>
wrote:

> Hi Rudi,
> I assume you mean "MS SQL Server 2008 however, ..., returns Long/Lat coordinates of THE CENTER OF the bounds of the geometry".
> You probably won't find a simpe formula for this, as the geometry could be a multi-polygon of multiple tiny polynesian islands, so to speak.
> I assume you're looking for a solution in MS/SQL (i.e. as a stored procedure or a function), as the Centroid() function in MapInfo Pro should do the trick very nicely.

> Will a point anywhere on the surface do, or should it be as close to the bounds center as possible ? I think Pro has some logic to the latter effect.Best regards / Med venlig hilsen Lars I. Nielsen GIS & DB Integrator GisPro

[Lars I. Nielsen (GisPro)]

Hi Rudi,

Your approach is similar to what I would do.

The trick here is to choose a line that'll hit something, and that's not necessaily easy, e.g. given the "tiny islands" case.

So I did some additional thinking. Maybe the vector should go to the midpoint of the first segment of the geometry, to ensure that you'll always hit something ?

Best regards / Med venlig hilsen
Lars I. Nielsen
GIS & DB Integrator
GisPro