SQL Interpolation query

[ideaboy]

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.

[debasisdas]

Kindly post your exact table structure with some sample data. and expected output.

[ideaboy]

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.

[Stewart Ross]

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

[ideaboy]

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.

[Stewart Ross]

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

[Rabbit]

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.

[ideaboy]

@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.

[Rabbit]

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.

[ideaboy]

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);
}

}

}

[ideaboy]

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.

[Rabbit]

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.