Solved

Finding the Center of Gravity MS SQL Query

Posted on 2013-02-07
10
815 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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
 
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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

808 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