Solved

Finding the Center of Gravity MS SQL Query

Posted on 2013-02-07
10
797 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

708 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

10 Experts available now in Live!

Get 1:1 Help Now