469,362 Members | 2,367 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,362 developers. It's quick & easy.

SQL Interpolation query

I want to write a query to get the interpolated result.

I have a table (which contains the gridded data - means lat, long, sst values for some points), the table contains around 100 million records.

I need to get a sst for my lat,long values (for these lat, long there is no exact match of records, so i need to take the result on interpolation based).

Please help me how to write the query to get the result (based on interpolation only).

Thanks in advance, reply me ASAP.
Feb 15 '11 #1
11 6107
debasisdas
8,127 Expert 4TB
Kindly post your exact table structure with some sample data. and expected output.
Feb 15 '11 #2
see the table below:

LATITUDE LONGITUDE SST

13.09806 80.30028 28.2
13.09806 80.30028 28.2
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.09806 80.30028 28.3
13.11111 80.30889 28.2
13.08722 80.36056 26.6
13.01667 80.39167 26.7
12.97611 80.40472 26.7
12.93556 80.41972 26.7
12.89444 80.43417 26.7
12.85278 80.44917 26.7
12.81167 80.46389 26.7
12.77056 80.47861 26.7
12.68833 80.50806 13.9
12.64806 80.52222 26.8
12.60833 80.53667 14.6
12.56861 80.55083 26.6
12.52944 80.56472 26.5
12.49056 80.57861 26.5
12.45194 80.5925 26.5


This is sample rows of the original table.

If i give the values like 12.45345- lat, 80.5714- long ... then i need to get the SST as a result for the above given locations.
Feb 15 '11 #3
Stewart Ross
2,545 Expert Mod 2GB
I have no idea what an 'SST' is, and if I don't I'm sure others won't either - please advise.

What algorithm are you meaning when you say 'interpolation'? You do not tell us. Please advise.

The data you post is fine as far as it goes. What is it you want done with the data? How do you recognise which rows need to go into whatever computation is being performed? How would you do this manually if you were asked to do so (asked because if you can't tell us how to do it manually there is no way it can be automated).

-Stewart
Feb 15 '11 #4
SST- Sea Surface Temperature (treat this as a value which need to get as a result).

My problem is :

First, I need to get one box, my given point should be in that box (to get the box we need to find the coordinates of 4 points from table which is near by our given point).

Second, with that 4 points data we need to find our point value using interpolation formula

I hope you understand, if you need any more clarity, please feel free to write here.
Feb 15 '11 #5
Stewart Ross
2,545 Expert Mod 2GB
This looks like an assignment question, which we can't answer for you.

I have no idea at all, and nor have you told us, how we would establish an interpolated SST from the data listed.

Too many uncertainties here. Sorry.

-Stewart
Feb 15 '11 #6
Rabbit
12,516 Expert Mod 8TB
From what I can tell, you need to calculate the distance to each point. Find the closest 4, which would form a box, and then do something with the SST.

To calculate the distance, you can use the pythagorean theorem. Then you can select the top 4 sorted by the distance. Then you can run whatever SST calculation you need to do.
Feb 15 '11 #7
@Stewart
This is not assignment, this is my project (i am working in Central govt dept).

@Rabbit
Thanks for your answer, i am finding the distances and i can make a box but second point (in my problem) needs interpolation formula to calculate, here i need help.
Feb 16 '11 #8
Rabbit
12,516 Expert Mod 8TB
I have no idea what you mean by interpolation. You haven't given us a formula to work with nor have you described in detail what you're trying to do.
Feb 16 '11 #9
Dear All,

I write a JAVA Program for this Interpolation formula ...
Here iam providing that program ... Please give me reply whether am i correct or not ??

import java.lang.Math.*;

public class Interpolation {

public Interpolation() {
}
public static void main (String[] args) {
double x=10.70, x1=10.20, x2=11.20, x3=10.20, x4=11.20, y=80.70, y1=80.20, y2=80.20, y3=81.20, y4=81.20;
double A=10.0, B=8.0, C=12.0, D=14.0, xyz=0.0;
if(x==x1)
System.out.print("SP");
else
{
xyz=Math.sqrt( (Math.sqrt ((x-x1)*(x-x1)+(y-y1)*(y-y1))* A )+(Math.sqrt ((x-x2)*(x-x2)+(y-y2)*(y-y2))* B )+(Math.sqrt ((x-x3)*(x-x3)+(y-y3)*(y-y3))* C )+(Math.sqrt ((x-x4)*(x-x4)+(y-y4)*(y-y4))* D ) )/(Math.sqrt ((x-x1)*(x-x1)+(y-y1)*(y-y1)) )+(Math.sqrt ((x-x2)*(x-x2)+(y-y2)*(y-y2)))+(Math.sqrt ((x-x3)*(x-x3)+(y-y3)*(y-y3)))+(Math.sqrt ((x-x4)*(x-x4)+(y-y4)*(y-y4)) );
//xyz= Math.Math.sqrt((x-x1)*(x-x1)+(y-y1)*(y-y1))*A;
//xyz = Math.Math.sqrt(0.25);
System.out.println("MAD : " +xyz);
}

}

}
Feb 16 '11 #10
This same i want to implement using SQL, because here i given the values x, y and a b c d randomly ... but these values i want to supply from table (above posted)...

For this i need your help to write these queries in optimized way.
Feb 16 '11 #11
Rabbit
12,516 Expert Mod 8TB
If the formula is correct, then yes, you could probably implement it in SQL. You would have to use multiple queries but it could be done. Off the top of my head you would probably have to use three aggregate queries.
Feb 16 '11 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

14 posts views Thread by Charles Banas | last post: by
3 posts views Thread by Jonas Ernst | last post: by
8 posts views Thread by Jerry | last post: by
5 posts views Thread by xandra | last post: by
2 posts views Thread by webhead74 | last post: by
2 posts views Thread by Colin McKinnon | last post: by
reply views Thread by MonkeeSage | last post: by
10 posts views Thread by John Passaniti | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.