472,371 Members | 1,351 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,371 software developers and data experts.

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 6314
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:


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

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.

Feb 15 '11 #6
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
This is not assignment, this is my project (i am working in Central govt dept).

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

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

Similar topics

by: Charles Banas | last post by:
I'm not sure if this is the right place to ask about this, but I've seen several posts in the past regarding Akima's Bivariate Interpolations routines, and i'm wondering if someone can give me some...
by: Jonas Ernst | last post by:
Hi, Can somebody give me some hints how to do a line interpolation without using floating point arithemtics? The function shall do a linear interpolation between 2 points (line interp?) and...
by: Regan | last post by:
Hello, I have done tons of searching on this topic but have yet to find something relavent to the problem I am experiencing so I am hoping someone can help me. The problem I am having is that...
by: Kun | last post by:
I have an html form that takes dates and inserts them into a mysql file. Currently, users have to type in dates in the yyyy-mm-dd format. As of now, this process works with the sql. However, I...
by: Jerry | last post by:
I am a MySQL and PHP newbie. I am having trouble getting the $w variable in my code below passed to mysql. When I use the value of $w directly in the Where clause, the correct rows are returned....
by: xandra | last post by:
i understood the concept of interpolation search. but i couldn't understand what would be the steps for that search. for example, if i'm searching for J in this file A A B E F H J M N N N N O P P...
by: webhead74 | last post by:
Hi, I'm having intermittent problems with queries from my php script to a postgresql database. I have a form where I can enter a search query - for instance a last name. This leads to a...
by: Colin McKinnon | last post by:
Hi all, I'm wondering if this is possible with PHP: $query = "SELECT * FROM table WHERE afield='$something'"; has quite a different meaning from $query = 'SELECT * FROM table WHERE...
by: MonkeeSage | last post by:
There are several string interpolation functions, as well as string.Template. But here's yet another. This one emulates ruby's inline interpolation syntax (using #{}), which interpolates strings as...
by: John Passaniti | last post by:
(Note: This is not the same message I posted a week or so ago. The problem that prevented my previous attempt to work was a silly error in the template system I was using. This is a problem...
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
by: jack2019x | last post by:
hello, Is there code or static lib for hook swapchain present? I wanna hook dxgi swapchain present for dx11 and dx9.
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.