Monday, March 20, 2006

SQL Interpolation Techniques

First off I have just returned from the ESRI developers summit and I had a great time. It was so nice to meet all the great developers out there. ESRI did a great job in organizing it. If you ever get a chance to meet Brian Flood take advantage of it. Brian is an amazing developer and I really enjoyed our coversations. Brian gets it...

I file this one under, stop me before I try to do too much with SQL! I wrote this a couple of years ago when I wanted have an interpolation routine that was straight SQL (No Oracle Spatial and No SDE). I would not do this technique now (ESRI Spatial Type for Oracle or straight to ArcGIS Server for my interpolations) but it illustrates some good Analytical Function practices. Enjoy...

ORIGINAL QUESTION:

I have a table that represents one mile polygons (sections) that has locational
information, center latitude and center longitude. I have another table that is
made up of point data that has for every point the latitude, longitude, and some
value (lets say it is a water level depth). What I want to do is build a view
that has the section polygon ID and an average water level value for the closest
10 wells that are at least within 10 square miles. This involves computing the
distance of all wells within a 10 square mile of the polygon cell, sorting those
wells to find the closest 10, and then averaging the results (actually I would
perform some kind of inverse distance calculation, but for simplicity sake I
would like to see how you would efficiently select and average the 10 wells
values).


I HAVE PASTED MY ATTEMPT BELOW...

HERE IS INFORMATION ABOUT THE TWO TABLES THAT QUERY USES.


SQL> DESC ACRES640_XYCENTER
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTID NOT NULL NUMBER(38)
TRS VARCHAR2(10)
RECNMBR NUMBER(11)
CENTER_X NUMBER(23,11)
CENTER_Y NUMBER(23,11)
SHAPE NUMBER(38)

SQL> select trs, recnmbr, center_x, center_y from ACRES640_XYCENTER where rownum < 10;

TRS RECNMBR CENTER_X CENTER_Y
---------- ---------- ---------- ----------
02S22W08 2816 -133815 431037
02S05E07 2817 114097 430710
02S12E07 2818 181440 431992
02S03W08 2819 48505.5 430018
02S23W12 2820 -136969 431089
02S05W08 2821 29424.6992 429929
02S04E12 2822 112494 430684
02S25W10 2823 -159307 431515
02S10E07 2824 162297 431573

9 rows selected.


SQL> SELECT COUNT(*) FROM ACRES640_XYCENTER;

COUNT(*)
----------
81803


SQL> desc WELLHEADERS_25MAY2004_PRJ
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTID NOT NULL NUMBER(38)
KID NUMBER(10)
LATITUDE NUMBER(17,6)
LONGITUDE NUMBER(17,6)
X_COORD NUMBER(23,11)
Y_COORD NUMBER(23,11)
SHAPE NUMBER(38)



SQL> select kid, latitude, longitude, x_coord, y_coord from WELLHEADERS_25MAY2004_PRJ;

KID LATITUDE LONGITUDE X_COORD Y_COORD
---------- ---------- ---------- ---------- ----------
1027937589 37.00094 -95.77342 219360 113565
1027937587 37.00094 -95.77342 219360 113565
1027937585 37.00094 -95.77342 219360 113565
1027937583 37.00094 -95.77342 219360 113565
1027937567 37.00094 -95.77342 219360 113565
1027937237 37.00094 -95.77342 219360 113565
1027937235 37.00094 -95.77342 219360 113565
1027935075 37.00094 -95.77342 219360 113565
1027803979 37.00094 -95.77342 219360 113565
1027936869 37.00094 -95.77342 219360 113565
1027936867 37.00094 -95.77342 219360 113565
1027936865 37.00094 -95.77342 219360 113565
1027936805 37.00094 -95.77342 219360 113565
1027937057 37.00094 -95.77342 219360 113565
1027934581 37.00094 -95.77342 219360 113565
1027934579 37.00094 -95.77342 219360 113565
1027934577 37.00094 -95.77342 219360 113565
1027934575 37.00094 -95.77342 219360 113565
1027934573 37.00094 -95.77342 219360 113565
1027934571 37.00094 -95.77342 219360 113565
1027934569 37.00094 -95.77342 219360 113565
1027804273 37.00094 -95.77342 219360 113565
1027804269 37.00094 -95.77342 219360 113565
1006790617 37.00133 -96.08868 191441 112895
1002895443 37.00133 -96.21841 179952 112630
1006790402 37.00133 -96.21841 179952 112630
1001257052 37.00132 -101.97293 -329700 117372
1006094323 37.00132 -101.97293 -329700 117372
1006094606 37.00132 -101.97746 -330101 117389
1020067001 37.00132 -101.97746 -330101 117389
1005449837 37.00132 -101.97746 -330101 117389
1026090216 37.0013 -100.57895 -206286 113259
1006093808 37.0013 -100.57895 -206286 113259
1026525325 37.0013 -100.57895 -206286 113259
1027805483 37.00128 -95.84634 212901 113429
1027937579 37.00128 -95.84634 212901 113429
1027807657 37.00128 -95.84634 212901 113429
1027807573 37.00128 -95.84634 212901 113429
1027807185 37.00128 -95.84634 212901 113429
1027703233 37.00128 -98.83632 -51938.301 110781
1027796075 37.00128 -98.83632 -51938.301 110781
1002901280 37.00127 -97.07416 104158 111286
1008415306 37.00127 -101.51646 -289294 115812
1027807735 37.00122 -95.82817 214511 113465
1027805485 37.00122 -95.82817 214511 113465
1027807499 37.00122 -95.82817 214511 113465
1027806799 37.00122 -95.82817 214511 113465
1027806797 37.00122 -95.82817 214511 113465
1027806517 37.00122 -95.82817 214511 113465
1027805877 37.00122 -95.82817 214511 113465
1006143725 37.00122 -100.71236 -218100 113562
1002932550 37.0012 -95.81286 215866 113499
1006790987 37.00119 -95.80991 216128 113505
1005456974 37.00119 -95.80991 216128 113505
1027805555 37.00119 -95.80992 216127 113505
1006790391 37.00111 -96.19579 181955 112651
1002895619 37.00111 -96.41649 162409 112233
1006790787 37.0011 -95.9109 207185 113260
1027805141 37.00109 -95.79157 217752 113538
1027805077 37.00109 -95.79157 217752 113538
1027931973 37.00109 -95.79157 217752 113538
1027931971 37.00109 -95.79157 217752 113538
1027806551 37.00109 -95.79157 217752 113538
1004751365 37.00159 -101.81951 -316120 116857
1002876182 37.00159 -101.82404 -316521 116873
1002895677 37.00158 -96.26826 175536 112560
1002952960 37.00158 -97.65121 53042.6992 110821

68 rows selected.



SQL> SELECT COUNT(*) FROM WELLHEADERS_25MAY2004_PRJ;

COUNT(*)
----------
375571


SQL>
SQL> COLUMN COLUMN_NAME FORMAT A15 TRUNC
SQL> ;
1 SELECT INDEX_NAME, COLUMN_NAME, TABLE_NAME FROM USER_IND_COLUMNS
2* WHERE TABLE_NAME IN ('WELLHEADERS_25MAY2004_PRJ','ACRES640_XYCENTER')
SQL> /

INDEX_NAME COLUMN_NAME TABLE_NAME
------------------------------ --------------- ------------------------------
A1411_IX1 SHAPE ACRES640_XYCENTER
R2067_SDE_ROWID_UK OBJECTID ACRES640_XYCENTER
ACRES640_XYCENTER_RECNMBR RECNMBR ACRES640_XYCENTER
ACRES640_XYCENTER_X_YCENT CENTER_X ACRES640_XYCENTER
ACRES640_XYCENTER_X_YCENT CENTER_Y ACRES640_XYCENTER
ACRES640_XYCENTER_CENTX CENTER_X ACRES640_XYCENTER
ACRES640_XYCENTER_CENTY CENTER_Y ACRES640_XYCENTER
A1409_IX1 SHAPE WELLHEADERS_25MAY2004_PRJ
R2065_SDE_ROWID_UK OBJECTID WELLHEADERS_25MAY2004_PRJ
WELLHEADERS_25_MAY2004KID KID WELLHEADERS_25MAY2004_PRJ
WELLHEADERS_25_MAY2004_LATLONG Y_COORD WELLHEADERS_25MAY2004_PRJ
WELLHEADERS_25_MAY2004_LATLONG X_COORD WELLHEADERS_25MAY2004_PRJ
WELLHEADERS_25_MAY2004_YCOORD Y_COORD WELLHEADERS_25MAY2004_PRJ
WELLHEADERS_25_MAY2004_XCOORD X_COORD WELLHEADERS_25MAY2004_PRJ

14 rows selected.




HERE IS THE QUERY THAT I CAME UP WITH....



FIRST I WANT TO SELECT THE SECTION ID, WELL ID, THE DISTANCE FROM THE WELL TO THE
CENTER OF THE SECTION, & THE QUADRANT OF THE SECTION (NW,NE,SW,SE) THAT THE WELL
IS IN ALL WITHIN A 7200 X 7200 METER BOUDING BOX (CENTERED ON THE SECTION CENTER)
FOR EVERY SECTION IN MY STUDY AREA.

select a.recnmbr recnmbr, b.kid kid,
case
when b.x_coord < a.center_x and b.Y_COORD < a.center_y then 'SW'
when b.x_coord < a.center_x and b.Y_COORD > a.center_y then 'NW'
when b.x_coord > a.center_x and b.Y_COORD < a.center_y then 'SE'
when b.x_coord > a.center_x and b.Y_COORD > a.center_y then 'NE'
end QUADRANT,
sqrt( ( (b.x_coord - a.center_x) * (b.x_coord - a.center_x) ) +
( (b.Y_COORD - a.center_y) * (b.Y_COORD - a.center_y) ) ) distance_to_cell
from ACRES640_XYCENTER a, WELLHEADERS_25MAY2004_PRJ b
where b.y_coord between a.center_y - 3600 and a.center_y + 3600
and b.x_coord between a.center_x - 3600 and a.center_x + 3600
and a.center_x >= -320688
and a.center_x <= -284100
and a.center_y >= 169518
and a.center_y <= 242855




RECNMBR KID QU DISTANCE_TO_CELL
---------- ---------- -- ----------------
49950 1006068633 SW 4129.70144
49950 1006068137 SW 3580.94959
49950 1002919963 SW 4519.08055
49950 1006068032 SE 3446.22605
49950 1006068206 SW 2316.18501
49950 1022362561 SW 3422.35124
49950 1006068139 SW 1594.49365
49950 1001235397 SW 3128.24951
49950 1022009618 SW 2460.49934
49950 1006068479 SW 1646.93169
49950 1006068208 SW 2039.86985
49950 1026642145 NW 2022.24281
49950 1025778509 NW 2062.54042
49950 1025686933 NW 3158.56803
49950 1022362563 NW 3747.38909
49950 1006068200 NW 3791.15352
49950 1022009617 NW 2596.05951
49950 1020066904 NW 3337.37142
49950 1006068395 NW 4568.36393
49952 1006068633 SW 3628.02563
49952 1006068437 SW 4142.8273
49952 1006068137 SW 3201.38033
49952 1002919963 SW 3479.8352
49952 1008378808 SW 4545.26149
49952 1002920004 SW 4531.73697
49952 1008764319 SW 3459.29299
49952 1006068206 SE 2021.65502
49952 1022362561 SW 2330.793
49952 1006067823 SW 2810.31617
49952 1002919902 SW 3859.54661
49952 1006068139 SE 2240.00223
49952 1001235397 SW 1855.94854
49952 1022009618 SW 1463.18591
49952 1006068585 SW 2491.16539
49952 1006068479 SE 1237.73503
49952 1006068381 SW 3055.63512
49952 1027708907 SW 3536.07494
49952 1006067716 SW 2673.85303
49952 1006068208 SW 558.660004
49952 1006067854 SW 2193.10054
49952 1026642145 NW 435.910541
49952 1025778509 NW 603.927976
49952 1002919979 NW 2058.37266
49952 1025686933 NW 1719.64764
49952 1022362563 NW 2248.00022
49952 1006068200 NW 2332.77196
49952 1006068683 NW 3040.36527
49952 1006067983 NW 3812.09299
49952 1022009617 NW 1515.13201
49952 1020066904 NW 2175.32802

THEN I USE THE ANALYTICAL FUNCTION DENSE_RANK TO ASSIGN A RANKING VALUE FOR
THE DISTANCE BY SECTION NUMBER (RECNMBR) TO EACH WELL

select recnmbr, kid, quadrant, distance_to_cell,
dense_rank()
over (partition by recnmbr order by distance_to_cell ) TOPN_TOTAL
from (
select a.recnmbr recnmbr, b.kid kid,
case
when b.x_coord < a.center_x and b.Y_COORD < a.center_y then 'SW'
when b.x_coord < a.center_x and b.Y_COORD > a.center_y then 'NW'
when b.x_coord > a.center_x and b.Y_COORD < a.center_y then 'SE'
when b.x_coord > a.center_x and b.Y_COORD > a.center_y then 'NE'
end QUADRANT,
sqrt( ( (b.x_coord - a.center_x) * (b.x_coord - a.center_x) ) +
( (b.Y_COORD - a.center_y) * (b.Y_COORD - a.center_y) ) ) distance_to_cell
from ACRES640_XYCENTER a, WELLHEADERS_25MAY2004_PRJ b
where b.y_coord between a.center_y - 3600 and a.center_y + 3600
and b.x_coord between a.center_x - 3600 and a.center_x + 3600
and a.center_x >= -320688
and a.center_x <= -284100
and a.center_y >= 169518
and a.center_y <= 242855
)




RECNMBR KID QU DISTANCE_TO_CELL TOPN_TOTAL
---------- ---------- -- ---------------- ----------
49948 1006068139 SW 2280.82989 1
49948 1006068032 SE 2320.57859 2
49948 1006068479 SW 3008.3173 3
49948 1006068206 SW 3434.70581 4
49948 1006068198 SE 3514.10543 5
49948 1006067819 NE 4519.37982 6
49948 1006068137 SW 4533.81749 7
49948 1006068188 SE 4542.85417 8
49950 1006068139 SW 1594.49365 1
49950 1006068479 SW 1646.93169 2
49950 1026642145 NW 2022.24281 3
49950 1006068208 SW 2039.86985 4
49950 1025778509 NW 2062.54042 5
49950 1006068206 SW 2316.18501 6
49950 1022009618 SW 2460.49934 7
49950 1022009617 NW 2596.05951 8
49950 1001235397 SW 3128.24951 9
49950 1025686933 NW 3158.56803 10
49950 1020066904 NW 3337.37142 11
49950 1022362561 SW 3422.35124 12
49950 1006068032 SE 3446.22605 13
49950 1006068137 SW 3580.94959 14
49950 1022362563 NW 3747.38909 15
49950 1006068200 NW 3791.15352 16
49950 1006068633 SW 4129.70144 17
49950 1002919963 SW 4519.08055 18
49950 1006068395 NW 4568.36393 19
49952 1026642145 NW 435.910541 1
49952 1006068208 SW 558.660004 2
49952 1025778509 NW 603.927976 3
49952 1006068479 SE 1237.73503 4
49952 1022009618 SW 1463.18591 5
49952 1022009617 NW 1515.13201 6
49952 1025686933 NW 1719.64764 7
49952 1001235397 SW 1855.94854 8
49952 1006068206 SE 2021.65502 9
49952 1002919979 NW 2058.37266 10
49952 1020066904 NW 2175.32802 11
49952 1006067854 SW 2193.10054 12
49952 1006068139 SE 2240.00223 13
49952 1022362563 NW 2248.00022 14
49952 1022362561 SW 2330.793 15
49952 1006068200 NW 2332.77196 16
49952 1006068585 SW 2491.16539 17
49952 1006067716 SW 2673.85303 18
49952 1006067823 SW 2810.31617 19
49952 1006068683 NW 3040.36527 20
49952 1006068381 SW 3055.63512 21
49952 1006068137 SW 3201.38033 22
49952 1006068395 NW 3454.67292 23
49952 1008764319 SW 3459.29299 24
49952 1002919963 SW 3479.8352 25
49952 1027708907 SW 3536.07494 26
49952 1006068633 SW 3628.02563 27
49952 1006067983 NW 3812.09299 28
49952 1002919902 SW 3859.54661 29
49952 1013358575 NW 3955.73204 30
49952 1006068437 SW 4142.8273 31
49952 1002920004 SW 4531.73697 32
49952 1008378808 SW 4545.26149 33


NEXT WE SELECT THE 8 CLOSEST WELLS FOR EACH SECTION, WE ALSO NEED TO COMPUTE
THE COUNT OF THE DISTINCT QUADRANTS. WE NEED THIS BECAUSE SOME INTERPOLATION
TECHNIQUES WORK BETTER IF YOU FORCE THE INTERPOLATED VALUE TO HAVE AT LEAST X
NUMBER OF POINTS IN EACH QUADRANT. IN THIS CASE WE ARE GOING TO WANT TO HAVE
AT LEAST ONE WELL IN EACH QUADRANT.

select recnmbr, kid, distance_to_cell, QUADRANT, TOPN_TOTAL,
count(distinct QUADRANT) over (partition by recnmbr) QUAD_COUNT
from (
select recnmbr, kid, distance_to_cell, QUADRANT, TOPN_TOTAL
from (
select recnmbr, kid, quadrant, distance_to_cell,
dense_rank()
over (partition by recnmbr order by distance_to_cell ) TOPN_TOTAL
from (
select a.recnmbr recnmbr, b.kid kid,
case
when b.x_coord < a.center_x and b.Y_COORD < a.center_y then 'SW'
when b.x_coord < a.center_x and b.Y_COORD > a.center_y then 'NW'
when b.x_coord > a.center_x and b.Y_COORD < a.center_y then 'SE'
when b.x_coord > a.center_x and b.Y_COORD > a.center_y then 'NE'
end QUADRANT,
sqrt( ( (b.x_coord - a.center_x) * (b.x_coord - a.center_x) ) +
( (b.Y_COORD - a.center_y) * (b.Y_COORD - a.center_y) ) ) distance_to_cell
from ACRES640_XYCENTER a, WELLHEADERS_25MAY2004_PRJ b
where b.y_coord between a.center_y - 3600 and a.center_y + 3600
and b.x_coord between a.center_x - 3600 and a.center_x + 3600
and a.center_x >= -320688
and a.center_x <= -284100
and a.center_y >= 169518
and a.center_y <= 242855
)
)
where TOPN_TOTAL <= 8
)


RECNMBR KID DISTANCE_TO_CELL QU TOPN_TOTAL QUAD_COUNT
---------- ---------- ---------------- -- ---------- ----------
49934 1006068645 1800.1 NE 1 3
49934 1006068218 2281.36122 NE 3 3
49934 1006068541 3524.63587 NW 6 3
49934 1006068571 3722.9722 NW 7 3
49934 1006068190 1808.89607 SW 2 3
49934 1006068182 2839.38479 SW 4 3
49934 1006068198 3833.55879 SW 8 3
49934 1006068545 2948.63036 SW 5 3
49935 1006068645 513.778162 NE 1 4
49935 1006079840 4155.58817 NE 7 4
49935 1006068218 1812.26185 NW 2 4
49935 1006068541 4270.07283 NW 8 4
49935 1027709052 3397.06712 SE 3 4
49935 1023930739 3397.06712 SE 3 4
49935 1006068190 3402.91713 SW 4 4
49935 1006068545 3581.14074 SW 5 4
49935 1006068182 4043.25834 SW 6 4
49937 1001243091 2834.38124 NE 1 4
49937 1006344036 3447.8283 NE 6 4
49937 1006068645 3079.22019 NW 3 4
49937 1006079840 3140.74704 NW 4 4
49937 1006068218 3822.73018 NW 8 4
49937 1006078290 3037.51165 SE 2 4
49937 1006078651 3798.06859 SE 7 4
49937 1027709052 3203.50433 SW 5 4
49937 1023930739 3203.50433 SW 5 4
49938 1006079840 3328.76974 NE 4 3
49938 1006344036 4377.68249 NE 7 3
49938 1001243091 3437.66912 NE 5 3
49938 1006068645 1535.64091 NW 1 3
49938 1006068218 2537.84357 NW 2 3
49938 1027709052 2893.72839 SE 3 3
49938 1006078290 3944.43317 SE 6 3
49938 1023930739 2893.72839 SE 3 3


FINALLY WE SELECT ALL SECTIONS THAT HAVE A DISTINCT QUAD COUNT OF 4.
THE 8 CLOSEST WELLS THAT HAVE AT LEAST ONE WELL IN EACH QUADRANT.

select recnmbr, kid, distance_to_cell, QUADRANT, TOPN_TOTAL
from (
select recnmbr, kid, distance_to_cell, QUADRANT, TOPN_TOTAL,
count(distinct QUADRANT) over (partition by recnmbr) QUAD_COUNT
from (
select recnmbr, kid, distance_to_cell, QUADRANT, TOPN_TOTAL
from (
select recnmbr, kid, quadrant, distance_to_cell,
dense_rank()
over (partition by recnmbr order by distance_to_cell ) TOPN_TOTAL
from (
select a.recnmbr recnmbr, b.kid kid,
case
when b.x_coord < a.center_x and b.Y_COORD < a.center_y then 'SW'
when b.x_coord < a.center_x and b.Y_COORD > a.center_y then 'NW'
when b.x_coord > a.center_x and b.Y_COORD < a.center_y then 'SE'
when b.x_coord > a.center_x and b.Y_COORD > a.center_y then 'NE'
end QUADRANT,
sqrt( ( (b.x_coord - a.center_x) * (b.x_coord - a.center_x) ) +
( (b.Y_COORD - a.center_y) * (b.Y_COORD - a.center_y) ) ) distance_to_cell
from ACRES640_XYCENTER a, WELLHEADERS_25MAY2004_PRJ b
where b.y_coord between a.center_y - 3600 and a.center_y + 3600
and b.x_coord between a.center_x - 3600 and a.center_x + 3600
and a.center_x >= -320688
and a.center_x <= -284100
and a.center_y >= 169518
and a.center_y <= 242855
)
)
where TOPN_TOTAL <= 8
)
)
where QUAD_COUNT = 4

RECNMBR KID DISTANCE_TO_CELL QU TOPN_TOTAL
---------- ---------- ---------------- -- ----------
49935 1006068645 513.778162 NE 1
49935 1006079840 4155.58817 NE 7
49935 1006068218 1812.26185 NW 2
49935 1006068541 4270.07283 NW 8
49935 1027709052 3397.06712 SE 3
49935 1023930739 3397.06712 SE 3
49935 1006068190 3402.91713 SW 4
49935 1006068545 3581.14074 SW 5
49935 1006068182 4043.25834 SW 6
49937 1001243091 2834.38124 NE 1
49937 1006344036 3447.8283 NE 6
49937 1006068645 3079.22019 NW 3
49937 1006079840 3140.74704 NW 4
49937 1006068218 3822.73018 NW 8
49937 1006078290 3037.51165 SE 2
49937 1006078651 3798.06859 SE 7
49937 1027709052 3203.50433 SW 5
49937 1023930739 3203.50433 SW 5
49942 1006068571 3908.5835 NE 7
49942 1006067819 3588.20693 NW 6
49942 1006068190 1423.03127 SE 1
49942 1006068545 3960.37902 SE 8
49942 1006068182 2597.02849 SE 3
49942 1006068198 1792.30578 SW 2
49942 1006068032 2809.95267 SW 4
49942 1006068188 3394.99411 SW 5
49960 1025686933 1021.17188 NE 3
49960 1025778509 1262.06537 NE 8
49960 1026642145 1187.63041 NE 6
49960 1002919979 575.591869 NW 1
49960 1022362563 1078.89063 NW 4
49960 1006068208 1259.78292 SE 7
49960 1006067854 595.014286 SW 2
49960 1006067716 1167.87414 SW 5
49965 1006068379 573.098595 NE 3
49965 1006067673 1086.42993 NE 6
49965 1022362558 591.472738 NW 4
49965 1026642147 1239.35548 NW 7
49965 1021121940 1074.38029 SE 5
49965 1006068265 1253.18953 SE 8
49965 1002919999 276.06521 SW 1
49965 1025777349 300.026665 SW 2
49966 1006068143 574.381406 NE 5
49966 1006067881 1014.58563 NE 7
49966 1002919986 572.856876 NW 4
49966 1002919921 277.200289 SE 1
49966 1008378793 280.189222 SE 2
49966 1001235373 589.896601 SE 6
49966 1006068265 557.917557 SW 3
49966 1021121940 1121.31396 SW 8
49969 1002919979 1253.72166 NE 8
49969 1022362560 608.152941 NW 3
49969 1006067716 855.977803 SE 4
49969 1006067854 1024.65311 SE 5
49969 1008378866 566.39209 SW 1
49969 1027708907 1095.42777 SW 6
49969 1001235373 1251.9976 SW 7
49969 1027625616 591.959458 SW 2
49979 1006068577 531.497883 NE 3
49979 1006068477 1251.4128 NE 6
49979 1006068591 1255.48755 NW 7
49979 1013358584 614.18564 SE 4
49979 1019411297 1270.18739 SE 8
49979 1006812097 280.189222 SW 1
49979 1006067545 282.242803 SW 2
49979 1006068726 1042.14826 SW 5
AND ON AND ON....
68143 1028445209 1044.73585 NE 2
68143 1002950389 1708.98391 NE 5
68143 1006155639 2714.99411 NE 8
68143 1025830097 1073.93203 NE 3
68143 1006154723 574.464968 NW 1
68143 1002950475 2615.97898 SE 7
68143 1006155021 1442.30718 SW 4
68143 1028445211 1994.58467 SW 6
68167 1006155879 1637.48099 NE 5
68167 1001322873 1704.43246 NE 6
68167 1022009791 1951.95722 NW 8
68167 1006154897 1160.19007 SE 1
68167 1002950448 1427.05431 SE 3
68167 1006154895 1501.003 SE 4
68167 1006155257 1385.55873 SW 2
68167 1025830091 1778.85188 SW 7
68184 1006155899 618.684087 NE 2
68184 1006155931 1399.38915 NE 3
68184 1006154883 1695.66329 NE 8
68184 1006154895 1690.6274 NW 6
68184 1004752885 1492.82618 SE 4
68184 1027709638 1693.48664 SE 7
68184 1004752884 13.892444 SW 1
68184 1004752837 1620.26448 SW 5
68187 1006154875 1567.44537 NE 3
68187 1006155899 2200.32952 NE 7
68187 1006154895 101.828287 NW 1
68187 1006155879 1575.13079 NW 4
68187 1001322873 2265.00618 NW 8
68187 1006154897 423.398158 NW 2
68187 1004752884 1595.49271 SE 5
68187 1002950448 1993.30304 SW 6

4396 rows selected.

Elapsed: 00:00:25.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1402 Card=2330 Bytes
=160770)

1 0 VIEW (Cost=1402 Card=2330 Bytes=160770)
2 1 WINDOW (SORT) (Cost=1402 Card=2330 Bytes=130480)
3 2 VIEW (Cost=1329 Card=2330 Bytes=130480)
4 3 WINDOW (SORT PUSHED RANK) (Cost=1329 Card=2330 Bytes
=69900)

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'WELLHEADERS_25MA
Y2004_PRJ' (Cost=2 Card=2 Bytes=32)

6 5 NESTED LOOPS (Cost=1264 Card=2330 Bytes=69900)
7 6 TABLE ACCESS (FULL) OF 'ACRES640_XYCENTER' (Co
st=72 Card=993 Bytes=13902)

8 6 INDEX (RANGE SCAN) OF 'WELLHEADERS_25_MAY2004_
LATLONG' (NON-UNIQUE) (Cost=6 Card=1690)





Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
65267 consistent gets
142 physical reads
0 redo size
183474 bytes sent via SQL*Net to client
2326 bytes received via SQL*Net from client
295 SQL*Net roundtrips to/from client
0 sorts (memory)
2 sorts (disk)
4396 rows processed

SQL>


THE NEXT STEP WOULD BE ACTUALLY DO THE INTERPOLATION....AVERAGE SOME VALUE WHERE
WELL KID = QUERY FROM ABOVE....


Questions/Comments?

Cheers,

Jeremy

Comments: Post a Comment



<< Home

This page is powered by Blogger. Isn't yours?