Hi,
I changed the fields name to be more general: Site, x, y, and u and called
the table xyData.
xyData
Site x y u
ena 35 430 4.5
ena 35 410 3.2
ena 35 390 1.8
ena 31 430 3.5
ena 31 410 2.9
ena 31 390 1.7
A possible SQL statement then look like:
=====================================
SELECT 0.25*((1-[xi])*(1-[eta])*[a].
+(1+[xi])*(1-[eta])*.
+(1+[xi])*(1+[eta])*[c].
+(1-[xi])*(1+[eta])*[d].) AS uw,
a.Site,
2*([xw]-[a].[x])/(.[x]-[a].[x])-1 AS xi,
2*([yw]-[a].[y])/([d].[y]-[a].[y])-1 AS eta
FROM ((((((xyData AS a INNER JOIN xyData AS b
ON (a.y = b.y) AND (a.Site = b.Site))
INNER JOIN xyData AS d
ON (a.x = d.x) AND (a.Site = d.Site))
INNER JOIN xyData AS c
ON (d.y = c.y) AND (b.x = c.x) AND (a.Site = c.Site))
INNER JOIN xyData AS xmin
ON a.Site = xmin.Site)
INNER JOIN xyData AS ymin
ON a.Site = ymin.Site)
INNER JOIN xyData AS xmax
ON a.Site = xmax.Site)
INNER JOIN xyData AS ymax
ON a.Site = ymax.Site
WHERE (((a.x)<.[x])
AND ((a.y)<[d].[y])
AND ((xmin.x)<=[xw])
AND ((ymin.y)<=[yw])
AND ((xmax.x)>[xw])
AND ((ymax.y)>[yw]))
GROUP BY a.Site, a.x, a.y, a.u, b.x, b.y, b.u, c.x, c.y, c.u, d.x, d.y, d.u
HAVING (((2*([xw]-[a].[x])/(.[x]-[a].[x])-1) Between -1 And 1)
AND ((2*([yw]-[a].[y])/([d].[y]-[a].[y])-1) Between -1 And 1)
AND ((a.x)=Max([xmin].[x]))
AND ((a.y)=Max([ymin].[y]))
AND ((c.x)=Min([xmax].[x]))
AND ((c.y)=Min([ymax].[y])));
==========================================
If you run the query, you would be ask for xw ( x-wanted) and for yw (
y-wanted). I supplied, as example, 33 and 420, respectively, and got:
uw Site xi eta
3.525 ena 0 0
uw, the interpollated result, is thus 3.525. xi and eta are just
intermediate result (they should be selected else Jet barks). Note that the
query does NOT work in MS SQL Server ( because MS SQL Server does more, as
usual, ... here, it does more than just barking, it does not allow to build
an expression upon alias at all).
Some limitations: You cannot extrapollate. The data has to be "tabular" (
by opposition to be a "cloud" of points). The x and y data type should
preferably be Decimal rather than Float.
How it works? There is the math and there is the SQL expression of the math.
Mathematically, it is quite easy, in fact, once we get the "method". First,
since the data is tabular, we can assume we interpollate between four
points, a, b, c and d, like here below:
^ eta
d | c
o------|------o
| | |
| | |
---+------+------+----> xi
| | |
| | |
o-------|------o
a b
where xi and eta axis are somehow, axis telling us "where we are", at what
intermediate point we want the interpollation. That simplifies greatly the
equations if we assume that xi varies from -1 to +1 ( ie, points a and d
are at xi = -1 ) and eta too, from -1 to +1 ( so point c is at xi=1,
eta=1 ). As example, point xi=0, eta=0 implies we are exactly at the
centroid of the four points.
So, knowing where we are, at a point (xi, eta), the u value is then
interpollated with the Lagrangian formula in 2 dimensions (after
simplications from our case algebrically included):
u = ( 1/4 ) * ( (1-xi)(1-eta) Ua
+ (1+xi)(1-eta) Ub
+ (1+xi)(1+eta) Uc
+ (1-xi)(1+eta) Ud )
with Ua, Ub, Uc and Ud the value of the function at points a, b, c and d
(ie, the value we read from the table).
Since we know how to do it mathematically, how can we do it with SQL? quite
easy. I bring a reference to the table eight times, but four of those are
aliased as a, b, c and d. Indeed, those are the four "points", nothing more.
I also use inner join between those references to position them among
themseves: ON a.x = d.x, as example, means I wish a and d be two points on
the same vertical ( having the same x value). I also use conditions like
a.x < b.x to specify that I wish point a to be at the left of point b.
Having my four tables (points) properly positionned, knowing xw and yw, I
can compute xi, eta, and u-wanted.
Now, since we have more than four points in the table, we have to limit the
result to the one that uniquely position our point "a" in the whole table.
That is the role of the four other references of the table. Let us position
point a uniquely in the table, that is the role of xmin and ymin. Limiting
xmin.x values to be <= xw, the x where we want the interpolation, means that
xmin reference would refer to only the points, in the table, at the left of
xw. We just then need to impose a.x to be that maximum of those xmin.x to
get the unique a.x value that is the characteristic of the point a. I do the
same about the y direction, implying ymin.y, yw, and finally, a.y. Since SQL
does not know the data is a table, we also have to repeat the job to
position exactly the point c, as being the first up and right of the point
(xw, yw). We do it exactly as we did it for imposing point a to bhe the
first left and down of (xw, yw).
That's all.
To convert that SQL statement into MS SQL Server, because that statement
works in Jet but not in MS SQL Server, a possible solution is to replace xi
by 2*([xw]-[a].[x])/(.[x]-[a].[x])-1 everywhere, and do something
similar for eta, that is not to simplify the "look" of the statement,
neither nothing to give it a friendly smile, but those are limitations of
syntax... the overall process is still ... elementary...
Hoping it may help,
Vanderghast, Access MVP
