Solved

Finding the Center of Gravity MS SQL Query

Posted on 2013-02-07
10
802 Views
Last Modified: 2013-02-11
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
Comment
Question by:Ross Turner
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:virtuadept
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

by:Ross Turner
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

by:Lowfatspread
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

by:Jaime Olivares
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

by:
virtuadept earned 250 total points
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Author Comment

by:Ross Turner
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

by:Jaime Olivares
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

by:Lowfatspread
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

by:Ross Turner
ID: 38864546
They are all UK postcodes Lowfatspread
0
 
LVL 8

Expert Comment

by:virtuadept
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL:  Negative Numbering in CTE Is Not Working 2 27
SQL Query stumper 3 36
datetime in sql 6 23
SQL Server Question 5 25
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now