x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 896

# Finding the Center of Gravity MS SQL Query

Hi EE,

I've got a table with about 34,000 longitude and latitude  which i want to find the Center of Gravity / Centroid longitude and latitude.

Name      Latitude      Longitude
A      57.512868      -1.789325
B      51.548893      -2.55282
C      51.542677      -2.572183
D      51.525163      -2.595357
E      52.082519      0.434123
F      51.45771              -3.186617
G      51.45771              -3.186617
H      51.466517      -3.181778
I      51.480896      -3.187089
J      51.379242      -0.082563
K      51.379242      -0.082563
L      51.281556      -0.077256

I've been trying all sorts to calculate in ms sql

Can anyone help me with a function/script to work this out

Many Thanks

Ross
0
Ross Turner
• 3
• 3
• 2
• +1
2 Solutions

Commented:
MS SQL has built in spatial data types and methods. One of them is:

http://msdn.microsoft.com/en-us/library/bb933847.aspx

STCentroid()

However, you'll have to convert the lat/long data to a spatial data type references which is pretty tricky.

Here is some help to get started on that:

http://msdn.microsoft.com/en-us/library/bb933876(v=sql.105).aspx

In short, this is not a quick and simple problem to solve and will require some research. But there is functionality built into the server to do it.
0

Management Information Support AnalystAuthor Commented:
Sorry i forgot to mention i'm running MS SQL 2005 which doesn't have Spatial Data Types
0

Commented:
could you consider loading a copy of sql server express 2008 or 2012 and doing the spatial calculations in that ...?

how long term is your requirement?
0

Software ArchitectCommented:
the center of gravity is just the average coordinate. Something like

SELECT AVG(Latitude), AVG(Longitude) FROM yourTable GROUP BY some_criteria .....
0

Commented:
The avergage of the lat longs may not be the right answer. But it may be close enough. Here is a very detailed page that describes various methods for calculating spatial data.

http://www.geomidpoint.com/calculation.html

EDIT Also I searched and could not find a generic SQL algorithm to do this but I did find one written in VB but the way it was coded would make it difficult to convert.
0

Management Information Support AnalystAuthor Commented:
I've tried the averages of Lat / Long and well it does come up with what i presume is a fairly correct estimation of where the center of gravity.

All these Lat / Longs are for the UK and i suppose with the distance involved the error rate may be small however that is still and error.

virtuadept - I've googled the hell out of trying to find sql algorithm and found so much vb / perl script that boggle my brain i wouldn't know where to begin converting them.
0

Software ArchitectCommented:
Using those algorithms in SQL (let's say, in a stored procedure), would be very resource consuming. I think you can do that in a local machine (c#, vb, or any other) and update a new field called Centroid.
0

Commented:
are these uk postcodes? if so i'd be surprised if the calculation/data wasn't already available some on the web...
0

Management Information Support AnalystAuthor Commented:
They are all UK postcodes Lowfatspread
0

Commented:
Well if you did find a good VB algorithm for it, and here I'm not sure, but if SQL 2005 has CLR capability, you could make the VB routine a CLR stored procedure so that you don't have to rewrite the logic as SQL. I am not sure when they added CLR Procedures, I know they had it in SQL Server 2008 R2 because I have written one on that. Writing your first CLR is a bit tricky, and you have to enable that feature with dbo rights on the database before you can even use it. I would recommend reading about it before attempting. Is this for an external application that talks to the SQL 2005 database? Perhaps the calculations could be done in the front end easier than on the back end?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.