Here I have two tables and want to select data with some constrained conditions, thanks in advance
Here is the earthquakes list table EQLIST
TIME_ LATITUDE LONGITUDE MAG EQ_NUM
31-12月-10 04.30.54.520000 下午 0.663 -26.0450000 5.5 1
31-12月-10 04.11.03.180000 上午 -19.209 167.9020000 5.1 2
30-12月-10 11.47.03.930000 下午 -31.830 -178.1350000 5.0 3
30-12月-10 09.22.30.350000 下午 -19.984 168.3530000 5.0 4
30-12月-10 02.36.54.560000 下午 -10.819 165.3760000 5.6 5
... ...
Here is the data table BIGDATA
UT GEOC_LAT GEOC_LONG LOCAL_TIME KP
01-1月 -06 03.12.51.708000 下午 -32.3847389221191 110.833557128906 22.6032676696777 1.66666666667
01-1月 -06 04.59.07.351000 下午 -3.26855063438416 79.5669708251953 22.2898406982422 1.66666666667
01-1月 -06 05.10.00.245000 下午 36.1917686462402 70.4237747192383 21.8616523742676 1.66666666667
01-1月 -06 11.30.08.354000 上午 -50.4215278625488 331.457702636719 9.59950065612793 1
01-1月 -06 09.07.51.459000 下午 -8.55202579498291 195.695846557617 10.1773509979248 1.66666666667
... ...
and I want to select data from BIGDATA which meeting the following conditions from each earthquake in EQLIST:
- 2 days (48h) before time of earthquke (EQLIST.TIME_ (TIMESTAMP(6)) )
and 2) distance within the radius, and the radius can be calculated from the following equation
where M is the magnitude (EQLIST.MAG (NUMBER))
and 3) night time data, ( BIGDATA.LOCAL_TIME (NUMBER) is from 6pm to 6am of next day)
and 4) BIGDATA.KP (NUMBER) < 3
and I also want to
-
compute disance between the epicenter (EQLIST.LATITUDE (NUMBER), EQLIST.LONGITUDE (NUMBER) ) and location of the data (BIGDATA.GEOC_LAT (NUMBER), BIGDATA.GEOC_LONG (NUMBER))
-
compute interval time (hours) between time of earthquak (EQLIST.TIME_ (TIMESTAMP(6)) ) and data (BIGDATA.UT (TIMESTAMP(6)))
-
keep the number of eqs (EQLIST.EQ_NUM (NUMBER))
Hopefully you can grasp what I mean, thankså
Oracle Spatial or Oracle Locator may be helpful, and I believe I can use them. Any suggestions in that direction will be appreciated.