Thanks for the answers to my last posthttps://community.oracle.com/message/15481722#15481722
When I inspected the results, I found some results unreasonable, an example as follows:
TIME_ LATITUDE LONGITUDE MAG PLACE EQ_TYPE
2007-08-08 17:04:57:840000 -5.926 107.681 6.1 Java, Indonesia MAIN EARTHQUAKE
2007-08-08 17:05:04:920000 -5.859 107.419 7.5 Java, Indonesia AFTERSHOCK
the latter earthquake should be main shock, and the former should be foreshock.
so I want to add a step to determine foreshocks
Practically, Mark foreshocks and aftershocks from earthquake lists is done by associating an area of 2° × 2° centered on the epicenter of all earthquakes in the list. the list is then processed in chronological order as follows: for a given earthquake, characterized by its occurrence time and its associated area,
1) any other earthquake if its magnitude less than or equal the given earthquake occurring inside the area up to less than 15 days after the occurrence time is remarked as aftershock.
2) any other earthquake if its magnitude greater than the given earthquake remarked as mainshock, and the given earthquake is remarked as foreshock.
Here is the earthquake lists example, magtype '0' mens 'minshock', '-1' means foreshock, '1' means aftershock
TIME_ LATITUDE LONGITUDE MagType Magnitude
2013-10-15 00:12:32:050000 9.880 124.1167000 0 7.1
2013-10-25 17:10:19:710000 9.156 124.6611000 0 6.1
2014-04-11 07:07:23:130000 -6.586 155.0485000 0 5.1
2014-04-12 20:14:39:300000 -6.270 156.1481000 0 7.3
2014-04-19 13:28:00:810000 -6.755 155.0241000 0 6.3
Here is the expected result
TIME_ LATITUDE LONGITUDE MagType Magnitude
2013-10-15 00:12:32:050000 9.880 124.1167000 0 7.1
2013-10-25 17:10:19:710000 9.156 124.6611000 1 6.1
2014-04-11 07:07:23:130000 -6.586 155.0485000 -1 5.1
2014-04-12 20:14:39:300000 -6.270 156.1481000 0 7.3
2014-04-19 13:28:00:810000 -6.755 155.0241000 1 6.3
Here is the earthquakes lists table, the version of ORACLE is 12c.
create table AFTERSHOCKEVENTS
(
time_ TIMESTAMP(6),
latitude NUMBER(7,3),
longitude NUMBER(12,7),
magtype NUMBER,
magnitude NUMBER
)
insert into AFTERSHOCKEVENTS (TIME_, LATITUDE, LONGITUDE, MAGTYPE, MAGNITUDE)
values ('2013-10-15 00:12:32:050000', 9.880, 124.1167000, 0, 7.1);
insert into AFTERSHOCKEVENTS (TIME_, LATITUDE, LONGITUDE, MAGTYPE, MAGNITUDE)
values ('2013-10-25 17:10:19:710000', 9.156, 124.6611000, 0, 6.1);
insert into AFTERSHOCKEVENTS (TIME_, LATITUDE, LONGITUDE, MAGTYPE, MAGNITUDE)
values ('2014-04-11 07:07:23:130000', -6.586, 155.0485000, 0, 5.1);
insert into AFTERSHOCKEVENTS (TIME_, LATITUDE, LONGITUDE, MAGTYPE, MAGNITUDE)
values ('2014-04-12 20:14:39:300000', -6.270, 156.1481000, 0, 7.3);
insert into AFTERSHOCKEVENTS (TIME_, LATITUDE, LONGITUDE, MAGTYPE, MAGNITUDE)
values ('2014-04-19 13:28:00:810000', -6.755, 155.0241000, 0, 6.3);