Solved

# Finding the Center of Gravity MS SQL Query

Posted on 2013-02-07
827 Views
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
Question by:Ross Turner
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 3
• 2
• +1

LVL 8

Expert Comment

ID: 38864010
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

LVL 7

Author Comment

ID: 38864022
Sorry i forgot to mention i'm running MS SQL 2005 which doesn't have Spatial Data Types
0

LVL 50

Expert Comment

ID: 38864048
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

LVL 55

Assisted Solution

Jaime Olivares earned 250 total points
ID: 38864085
the center of gravity is just the average coordinate. Something like

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

LVL 8

Accepted Solution

ID: 38864136
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

LVL 7

Author Comment

ID: 38864308
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

LVL 55

Expert Comment

ID: 38864396
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

LVL 50

Expert Comment

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

LVL 7

Author Comment

ID: 38864546
They are all UK postcodes Lowfatspread
0

LVL 8

Expert Comment

ID: 38868579
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
###### Suggested Courses
Course of the Month8 days, 18 hours left to enroll