summaryrefslogtreecommitdiffstats
path: root/Parse
diff options
context:
space:
mode:
Diffstat (limited to 'Parse')
-rw-r--r--Parse/CalcVariance.java65
-rw-r--r--Parse/MapInverse.java115
-rw-r--r--Parse/NMEAParse.java224
-rw-r--r--Parse/SqlPoller.java47
-rw-r--r--Parse/SqlPollerDate.java406
-rw-r--r--Parse/SqlPollerUnThreaded.java370
-rw-r--r--Parse/sqlreader.java483
7 files changed, 1710 insertions, 0 deletions
diff --git a/Parse/CalcVariance.java b/Parse/CalcVariance.java
new file mode 100644
index 0000000..1aaccc9
--- /dev/null
+++ b/Parse/CalcVariance.java
@@ -0,0 +1,65 @@
+// OLD! no longer used! 06.06.2011
+
+package Parse;
+
+import java.io.BufferedWriter;
+import java.io.File;
+import java.io.FileWriter;
+import java.io.IOException;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+
+import DataStructure.SingleBTS;
+
+public class CalcVariance {
+ static SingleBTS[] btsarray;
+
+ public static void Calc(sqlreader sql, Timestamp start, Timestamp end,
+ int ARFCN, int place) throws ClassNotFoundException, SQLException,
+ IOException {
+ btsarray = sql.btsarray;
+
+ // create output File
+ File outputUL = new File("varianz/Ort " + place + " UL-" + sql.IMSI
+ + ".txt");
+ File outputDL = new File("varianz/Ort " + place + " DL-" + sql.IMSI
+ + ".txt");
+ FileWriter streamUL = new FileWriter(outputUL);
+ FileWriter streamDL = new FileWriter(outputDL);
+ BufferedWriter UL = new BufferedWriter(streamUL);
+ BufferedWriter DL = new BufferedWriter(streamDL);
+
+ for (SingleBTS current : btsarray) {
+ if (current.time.getTime() >= start.getTime()
+ && current.time.getTime() <= end.getTime()
+ && current.ARFCN == ARFCN) {
+ if (current.getUldB() != 0)
+ UL.write((int) current.getUldB() + ";");
+ if (current.getDldB() != 0)
+ DL.write((int) current.getDldB() + ";");
+
+ }
+ }
+ UL.close();
+ DL.close();
+ streamUL.close();
+ streamDL.close();
+
+ Class.forName("com.mysql.jdbc.Driver");
+ // open connection
+ Connection cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/logging", "richard",
+ "uh237Aug.ad7");
+ cn.setReadOnly(true);
+ System.out.println("Connected to SQL");
+ // prepare a statement to the connection cn
+ // Statement st_MR = cn.createStatement();
+
+ // Timestamp start = new Timestamp(2011, 04, 05, 13, 42, 28, 0);
+ // Timestamp end = new Timestamp(2011, 04, 05, 13, 42, 28, 0);
+
+ }
+
+}
diff --git a/Parse/MapInverse.java b/Parse/MapInverse.java
new file mode 100644
index 0000000..aa462e4
--- /dev/null
+++ b/Parse/MapInverse.java
@@ -0,0 +1,115 @@
+package Parse;
+
+import java.util.ArrayList;
+
+import DataStructure.GPScoordinate;
+import DataStructure.GSMMap;
+import DataStructure.SingleBTS;
+
+//do stuff without inverse Map. Just filter out the BTSs that might match
+
+public class MapInverse {
+ private ArrayList<iLut>[] receive;
+
+ public MapInverse(GSMMap map) {
+ // traverse every receive strength
+ // receive array (-47 ... -115) mit allen gefundenen Werten (68
+ // Elemente)
+ // coordinates ListBTS
+ // Funktion, um Verhältnisse zu prüfen (z.B. BTS877 zu 880, etc.)
+
+ // initialize receive
+
+ // SingleBTS[] mapcontent = map.getUniqueBTSlist();
+ // receive = new ArrayList[68];
+ for (int i = 0; i < receive.length; i++) {
+ receive[i] = new ArrayList<iLut>();
+ }
+
+ // traverse GSMmap, fill received
+
+ // ArrayList<Double> receive = new ArrayList<Double>();
+
+ // or maybe: have only two Objekts: one with Strength and one with
+ // Ratio. Ask Both and compare the result
+
+ }
+
+}
+
+/**
+ * Stores information about signalstrength and ratios and coordinates where this
+ * occured.
+ *
+ * @author richy
+ *
+ */
+class BtsLut implements iLut {
+ public int ARFCN;
+ double[] strength = new double[68];
+ ArrayList<GPScoordinate> DL = new ArrayList<GPScoordinate>();
+ ArrayList<GPScoordinate> UL = new ArrayList<GPScoordinate>();
+
+ /**
+ * Define for which ARFCN this GSMLut is for. It will then only take
+ * measurements for this specific ARFCN
+ *
+ * @param ARFCN
+ * Set ARFCN
+ */
+ public BtsLut(SingleBTS ARFCN) {
+ this.ARFCN = ARFCN.ARFCN;
+
+ // create dBm Array
+ for (int i = 0; i < 69; i++) {
+ strength[i] = -115 + i;
+ }
+ }
+
+ public BtsLut(int ARFCN) {
+ this(new SingleBTS(ARFCN, "arfcn set"));
+ }
+
+ public void addMR(SingleBTS MR, GPScoordinate gps) {
+ // do DL first
+ // int hit = Arrays.binarySearch(strength,
+ // (int) (Math.round(MR.getDldB())));
+ // hit is the index where this Coordinate should be added to
+
+ }
+
+ @Override
+ public GPScoordinate getCoord(SingleBTS measuremet) {
+ // TODO Auto-generated method stub
+ return null;
+ }
+
+}
+
+class RatioLut extends BtsLut implements iLut {
+
+ public RatioLut(int ARFCN) {
+ super(ARFCN);
+ // TODO Auto-generated constructor stub
+ }
+
+ @Override
+ public GPScoordinate getCoord(SingleBTS measuremet) {
+ // TODO Auto-generated method stub
+ return null;
+ }
+
+}
+
+/**
+ * Stores information about signalstrength and ratios and coordinates where this
+ * occured. Null when no information is present
+ *
+ * @author richy
+ *
+ */
+
+interface iLut {
+ GPScoordinate getCoord(SingleBTS measuremet);
+
+}
diff --git a/Parse/NMEAParse.java b/Parse/NMEAParse.java
new file mode 100644
index 0000000..0f88cd3
--- /dev/null
+++ b/Parse/NMEAParse.java
@@ -0,0 +1,224 @@
+package Parse;
+
+import java.io.BufferedReader;
+import java.io.FileReader;
+import java.io.IOException;
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import java.util.Iterator;
+import java.util.LinkedList;
+import java.util.TimeZone;
+
+import DataStructure.GPScoordinate;
+
+// Parse GPS textual file
+public class NMEAParse {
+ private BufferedReader br;
+ private FileReader fr;
+ // private DateFormat time = new SimpleDateFormat("HHmmss.SSS");
+ private DateFormat date = new SimpleDateFormat("HHmmssddMMyy");
+ private DateFormat dateWithMillis = new SimpleDateFormat("HHmmss.SSSddMMyy");
+ private boolean QualityGood;
+ private LinkedList<GPScoordinate> GPSData = new LinkedList<GPScoordinate>();
+ private int valid;
+
+ // Constructor
+ public NMEAParse(String fileName) {
+
+ // Try open file read-only
+ try {
+ this.fr = new FileReader(fileName);
+ this.br = new BufferedReader(this.fr);
+ } catch (IOException e) {
+ System.out.println("Cannot open file");
+ e.printStackTrace();
+ System.exit(-1);
+ }
+ try {
+ Parse();
+ } catch (NumberFormatException e) {
+ System.out.println("Cannot parse time");
+ e.printStackTrace();
+ } catch (IOException e) {
+ System.out.println("Cannot read file");
+ e.printStackTrace();
+ }
+
+ }
+
+ private int getTimeDiff(Date UTCtime) {
+
+ TimeZone tz = TimeZone.getDefault();
+ return tz.getOffset(UTCtime.getTime());
+
+ }
+
+ private void Parse() throws NumberFormatException, IOException {
+ Date time = new Date();
+ String content;
+ // TimeZone tz = TimeZone.getDefault();
+ int utcDifference = 0;
+ boolean timeSet = false;
+
+ while ((content = br.readLine()) != null) {
+
+ // check if something is wrong, for example more than one & is
+ // inside
+ if (StringIsWrong(content)) {
+ continue;
+ }
+
+ // parse current Date
+ if (content.contains("GPRMC")) {
+ String[] Array = content.split(",");
+ try {
+ // DateFormat time = DateFormat.getTimeInstance();
+ String timeString = Array[1].concat(Array[9]);
+ if (timeString.contains(".")) {
+ time = dateWithMillis.parse(timeString);
+ } else {
+ time = this.date.parse(Array[1].concat(Array[9]));
+ }
+
+ // time.parse(Array[1].concat(Array[9]);
+ // to current TimeZone
+ // if TimeZone not yet checked, do it
+ if (!timeSet) {
+ utcDifference = getTimeDiff(time);
+ // System.out.println("Found time difference: "
+ // + utcDifference / (60 * 60 * 1000));
+ // timeSet = true; //time is set
+ }
+ time.setTime(time.getTime() + utcDifference);
+ } catch (ParseException e) {
+ // System.out.println("Cannot read time or date");
+ time = new Date();
+ // e.printStackTrace();
+ // continue;
+ }
+ // TODO: compare to current time. Add Time-Zone offset
+ }
+
+ // parse coordinates
+ if (content.contains("$GPGGA")) {
+ String[] Array = content.split(",");
+
+ // Parse coordinate 1 in DEZ (GoogleEarth style coordinate)
+ // see http://www.kowoma.de/gps/zusatzerklaerungen/NMEA.htm
+ double coordNS = Double.parseDouble(Array[2].substring(0, 2))
+ + Double.parseDouble(Array[2].substring(2,
+ Array[2].length())) / 60;
+ char NS = Array[3].charAt(0);
+ if (NS == 'S')
+ coordNS = coordNS * (-1);
+
+ // Parse coordinate 2 in DEZ (GoogleEarth style coordinate)
+ double coordEW = Double.parseDouble(Array[4].substring(0, 3))
+ + Double.parseDouble(Array[4].substring(3,
+ Array[4].length())) / 60;
+
+ char EW = Array[5].charAt(0);
+ if (EW == 'W')
+ coordEW = coordEW * (-1);
+
+ // check quality
+ // String quality = "bad";
+ QualityGood = false;
+ if (Integer.parseInt(Array[6]) == 1) {
+ // quality = "GPS";
+ QualityGood = true;
+ }
+ if (Integer.parseInt(Array[6]) == 2) {
+ // quality = "DGPS";
+ QualityGood = true;
+ }
+ // String justSeconds = "HH:mm:ss";
+ // String secondsAndMilliSeconds = "HH:mm:ss.SSS";
+ // return (new SimpleDateFormat(justSeconds).format(time) + ";"
+ // + coordNS + ";" + NS + ";" + coordEW + ";" + EW + ";" +
+ // quality);
+ GPScoordinate currentGPS = new GPScoordinate(time, coordNS, NS,
+ coordEW, EW, QualityGood);
+ // currentGPS.coord1S = coordNSString;
+ // currentGPS.coord2S = coordEWString;
+
+ // check if currentGPS is valid
+ if (currentGPS.isValid()) {
+ GPSData.add(currentGPS);
+ valid++;
+ }
+ }
+ }
+ System.out.println("Valid NMEA-Lines: " + valid);
+ }
+
+ /**
+ * Returns true if something in this NMEA line is wrong
+ *
+ * @param content
+ * @return
+ */
+ private boolean StringIsWrong(String content) {
+ // check if more than one $ is inside
+ if (content.lastIndexOf('$') > 1) {
+ return true;
+ }
+ return false;
+ }
+
+ public LinkedList<GPScoordinate> getGPSList() {
+ // remove first coordinate. Date might be set wrong;
+ GPSData.remove();
+ return GPSData;
+ }
+
+ public double getMinX() {
+ Iterator<GPScoordinate> itr = GPSData.iterator();
+ GPScoordinate current;
+ double min = 181;
+ while (itr.hasNext()) {
+ current = itr.next();
+ if (current.coord2 < min)
+ min = current.coord2;
+ }
+ return min;
+ }
+
+ public double getMaxX() {
+ Iterator<GPScoordinate> itr = GPSData.iterator();
+ GPScoordinate current;
+ double max = -181;
+ while (itr.hasNext()) {
+ current = itr.next();
+ if (current.coord2 > max)
+ max = current.coord2;
+ }
+ return max;
+ }
+
+ public double getMinY() {
+ Iterator<GPScoordinate> itr = GPSData.iterator();
+ GPScoordinate current;
+ double min = 181;
+ while (itr.hasNext()) {
+ current = itr.next();
+ if (current.coord1 < min)
+ min = current.coord1;
+ }
+ return min;
+ }
+
+ public double getMaxY() {
+ Iterator<GPScoordinate> itr = GPSData.iterator();
+ GPScoordinate current;
+ double max = -181;
+ while (itr.hasNext()) {
+ current = itr.next();
+ if (current.coord1 > max)
+ max = current.coord1;
+ }
+ return max;
+ }
+}
diff --git a/Parse/SqlPoller.java b/Parse/SqlPoller.java
new file mode 100644
index 0000000..2d528dd
--- /dev/null
+++ b/Parse/SqlPoller.java
@@ -0,0 +1,47 @@
+package Parse;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.concurrent.Callable;
+
+import DataStructure.MobilePhone;
+
+public class SqlPoller implements Callable<ArrayList<MobilePhone>> {
+ static long last_id;
+ static Connection cn;
+
+ /**
+ * @param args
+ */
+ public static void main(String[] args) {
+ // TODO Auto-generated method stub
+
+ }
+
+ public SqlPoller() {
+ try {
+ if (cn == null || cn.isClosed()) {
+ Class.forName("com.mysql.jdbc.Driver");
+ cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/logging", "richard",
+ "uh237Aug.ad7");
+ }
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ } catch (ClassNotFoundException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ }
+
+ @Override
+ public ArrayList<MobilePhone> call() throws Exception {
+ // make a statement
+
+ // TODO Auto-generated method stub
+ return null;
+ }
+}
diff --git a/Parse/SqlPollerDate.java b/Parse/SqlPollerDate.java
new file mode 100644
index 0000000..e71e66b
--- /dev/null
+++ b/Parse/SqlPollerDate.java
@@ -0,0 +1,406 @@
+package Parse;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Date;
+
+import DataStructure.GPScoordinate;
+import DataStructure.MobilePhone;
+import DataStructure.SingleBTS;
+
+public class SqlPollerDate {
+ public Connection cn;
+ public Connection cnForName;
+
+ /**
+ * @param args
+ * @throws SQLException
+ * @throws ClassNotFoundException
+ */
+ public static void main(String[] args) throws ClassNotFoundException,
+ SQLException {
+ SqlPollerDate sql = new SqlPollerDate();
+ MobilePhone phone = sql.getActionRefID(146024l);
+ phone.toString();
+
+ }
+
+ public SqlPollerDate() throws ClassNotFoundException, SQLException {
+
+ cn = getConnection();
+ cnForName = getNameConnection();
+
+ }
+
+ private Connection getNameConnection() {
+ // catch Exceptions as Name lookup is not needed for operation
+ try {
+ Class.forName("com.mysql.jdbc.Driver");
+ } catch (ClassNotFoundException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ try {
+ Connection cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/hlr", "richard",
+ "uh237Aug.ad7");
+ cn.setReadOnly(true);
+ return cn;
+ } catch (SQLException e) {
+ return null;
+ }
+
+ }
+
+ public SqlPollerDate(SqlPollerUnThreaded sql) {
+ cn = sql.cn;
+ }
+
+ /**
+ * Returns last occured MRs of phone with given IMSI.
+ *
+ * @param IMSI
+ * @return
+ * @throws SQLException
+ */
+ public MobilePhone getLastMRs(long IMSI) throws SQLException {
+ // TODO
+ MobilePhone result = new MobilePhone();
+ result.IMSI = IMSI;
+ result.MR = getIMSIMRs(IMSI);
+ result.name = getName(IMSI);
+
+ return result;
+ }
+
+ private ArrayList<SingleBTS> getIMSIMRs(long IMSI) throws SQLException {
+ String query = imsiQuery(IMSI);
+ ResultSet rs = cn.createStatement().executeQuery(query);
+ // get current assigned BTS-arfcn
+ int usedBTS = getUsedBTSIMSI(IMSI);
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+ while (rs.next()) {
+ // parse first BTS
+ SingleBTS assigned = new SingleBTS(usedBTS, "lastUsed");
+ assigned.addDl(rs.getInt("RXL_FULL_dl"));
+ Date timestamp = rs.getTimestamp("cur_timestamp");
+ assigned.time = timestamp;
+ result.add(assigned);
+ // now, add neighbor BTSs
+ int neigh = rs.getInt("NUM_NEIGH");
+ if (neigh != 7) {
+ for (int i = 0; i < neigh; i++) {
+ int ARFCN = rs.getInt("ARFCN_" + i);
+ int rxdl = rs.getInt("rxlev_" + i);
+ // int reason = rs.getInt("actionID");
+ SingleBTS neighbor = new SingleBTS(ARFCN, 0, rxdl, false,
+ timestamp, "Lookup Record");
+ // neighbor.reason = reason;
+ result.add(neighbor);
+ }
+ }
+ }
+ return result;
+ }
+
+ private int getUsedBTSIMSI(long iMSI) throws SQLException {
+ String query = "SELECT arfcn FROM arfcn "
+ + "JOIN usedBTS ON usedBTS.bts = "
+ + "arfcn.bts JOIN action ON imsi = " + iMSI
+ + " AND usedBTS.actionRefID = action.ID "
+ + "ORDER BY usedBTS.cur_timestamp DESC LIMIT 0,1";
+ ResultSet rs = cn.createStatement().executeQuery(query);
+ rs.first();
+ int arfcn = rs.getInt("arfcn");
+ // return new SingleBTS(arfcn, "last Used BTS");
+ return arfcn;
+ }
+
+ private String imsiQuery(long iMSI) {
+ // TODO Auto-generated method stub
+ return "SELECT MR.cur_timestamp, RXL_FULL_dl, RXL_SUB_dl, "
+ + "RXQ_FULL_dl, RXQ_SUB_dl, NUM_NEIGH, "
+ + "IDX_0, ARFCN_0, BSIC_0, rxlev_0, IDX_1, "
+ + "ARFCN_1, BSIC_1, rxlev_1, IDX_2, ARFCN_2, "
+ + "BSIC_2, rxlev_2, IDX_3, ARFCN_3, BSIC_3, "
+ + "rxlev_3, IDX_4, ARFCN_4, BSIC_4, rxlev_4, "
+ + "IDX_5, ARFCN_5, BSIC_5, rxlev_5 "
+ + "FROM measurementResults MR JOIN action ON " + "IMSI = "
+ + iMSI + " AND MR.actionRefID = action.ID "
+ + "ORDER BY MR.id DESC LIMIT 0,6";
+
+ }
+
+ public MobilePhone getActionRefID(long actionRefID) throws SQLException {
+
+ // get MRs
+ ArrayList<SingleBTS> MRs = getActionRefMRs(actionRefID);
+
+ // get IMSI of actionRefID
+ long IMSI = getIMSI(actionRefID);
+ MobilePhone phone = new MobilePhone();
+ phone.IMSI = IMSI;
+ // phone.MR = filterMRTime(MRs, 9000l);
+ // phone.MR = getMiddle(MRs);
+ phone.MR = MRs;
+ // phone.MR = ListBTS.generateAveragedList(phone.MR);
+
+ System.out.println("ActionRefID " + actionRefID + " fertig");
+
+ // get Coordinates of that phone if possible
+ phone.correctCoordinate = getCoord(actionRefID);
+ phone.name = getName(IMSI);
+
+ return phone;
+ }
+
+ private String getName(long iMSI) {
+ try {
+ Statement st = cnForName.createStatement();
+ ResultSet rs = st
+ .executeQuery("SELECT surname, User.name FROM Subscriber"
+ + " JOIN UserWatch ON imsi = '" + iMSI
+ + "' AND Subscriber.id = subscriber_id "
+ + "JOIN User ON user_id = User.id");
+ rs.first();
+ return rs.getString("surname") + " " + rs.getString("name");
+ } catch (SQLException e) {
+ // e.printStackTrace();
+ } catch (NullPointerException e) {
+
+ }
+ return null;
+ }
+
+ /**
+ * Searches the logging table coordinates for the correct coordinate for
+ * this measure
+ *
+ * @param actionRefID
+ * @return
+ * @throws SQLException
+ */
+ public GPScoordinate getCoord(long actionRefID) {
+ String query = "SELECT * FROM coordinates WHERE ActionID = "
+ + actionRefID;
+ try {
+ ResultSet rs = cn.createStatement().executeQuery(query);
+ while (rs.next()) {
+ GPScoordinate coordinate = new GPScoordinate(new Date(),
+ rs.getDouble("CoordNS"), 'N', rs.getDouble("CoordEW"),
+ 'E', true);
+ return coordinate;
+ }
+ } catch (SQLException e) {
+
+ }
+ return null;
+ }
+
+ /**
+ * Returns all BTS that are l seconds before last Measurement
+ *
+ * @param mRs
+ * @param l
+ * @return
+ */
+ @SuppressWarnings("unused")
+ private ArrayList<SingleBTS> filterMRTime(ArrayList<SingleBTS> mRs, long l) {
+ // get max time
+ long time = getMaxTime(mRs);
+
+ // make new list. copy every entry that is l milliseconds before time
+ ArrayList<SingleBTS> newMR = new ArrayList<SingleBTS>();
+ for (int i = 0; i < mRs.size(); i++) {
+ if ((time - mRs.get(i).time.getTime()) <= l) {
+ newMR.add(mRs.get(i));
+ }
+ }
+ return newMR;
+ }
+
+ /**
+ * Returns Middle +/- 1/2 second!
+ *
+ * @param MR
+ * @return
+ */
+ @SuppressWarnings("unused")
+ private ArrayList<SingleBTS> getMiddle(ArrayList<SingleBTS> MR) {
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+ long time = (getMinTime(MR) + getMaxTime(MR)) / 2;
+ for (SingleBTS current : MR) {
+ if (Math.abs(current.time.getTime() - time) <= 1000) {
+ result.add(current);
+ }
+ }
+ return result;
+ }
+
+ private long getMinTime(ArrayList<SingleBTS> MR) {
+ long time = MR.get(0).time.getTime();
+ for (int i = 1; i < MR.size(); i++) {
+ if (MR.get(i).time.getTime() < time) {
+ time = MR.get(i).time.getTime();
+ }
+ }
+ return time;
+ }
+
+ private long getMaxTime(ArrayList<SingleBTS> MR) {
+ long time = MR.get(0).time.getTime();
+ for (int i = 1; i < MR.size(); i++) {
+ if (MR.get(i).time.getTime() > time) {
+ time = MR.get(i).time.getTime();
+ }
+ }
+ return time;
+ }
+
+ /**
+ * Returns IMSI from given ActionRefID
+ *
+ * @param actionRefID
+ * @return
+ * @throws SQLException
+ */
+ private long getIMSI(long actionRefID) throws SQLException {
+ String query = "Select IMSI from action WHERE ID = " + actionRefID;
+ ResultSet rs = cn.createStatement().executeQuery(query);
+ rs.first();
+
+ return rs.getLong("IMSI");
+ }
+
+ /**
+ * Gets all MR with given actionRefID
+ *
+ * @param actionRefID
+ * @param usedBTS
+ * @return
+ * @throws SQLException
+ */
+ private ArrayList<SingleBTS> getActionRefMRs(long actionRefID)
+ throws SQLException {
+ SingleBTS[] usedBTS = getUsedBTS(actionRefID);
+ String query = "SELECT * FROM measurementResults MR "
+ + "WHERE actionRefID = " + actionRefID;
+ Statement st = cn.createStatement();
+ ResultSet rs = st.executeQuery(query);
+
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+
+ while (rs.next()) {
+ // add current assigned BTS
+ Date timestamp = rs.getTimestamp("cur_timestamp");
+ // get name of the assigned BTS
+ SingleBTS search = searchForUsedBTS(usedBTS, timestamp);
+ // transfer name to current
+ SingleBTS current = new SingleBTS(search.ARFCN, search.name);
+ current.clear();
+ // fill current with its receive level
+ int RXdl = rs.getInt("RXL_FULL_dl");
+ current.addDl(RXdl);
+ current.time = timestamp;
+ result.add(current);
+
+ // now: neighbors!
+ result.addAll(readNeighborList(rs, timestamp));
+ }
+
+ return result;
+ }
+
+ /**
+ * Parses all BTSs from neighbor list fo given ResultSet. Assigned date
+ * provided in timestamp
+ *
+ * @param rs
+ * @param timestamp
+ * @return
+ * @throws SQLException
+ */
+ private ArrayList<SingleBTS> readNeighborList(ResultSet rs, Date timestamp)
+ throws SQLException {
+ int numOfNeigh = rs.getInt("NUM_NEIGH");
+ if (numOfNeigh == 7 || numOfNeigh == 0) {
+ return new ArrayList<SingleBTS>();
+ }
+ ArrayList<SingleBTS> result = new ArrayList<SingleBTS>();
+ for (int i = 0; i < numOfNeigh; i++) {
+ int ARFCN = rs.getInt("ARFCN_" + i);
+ int RXdl = rs.getInt("rxlev_" + i);
+ SingleBTS neighbor = new SingleBTS(ARFCN, "neighbor");
+ neighbor.clear();
+ neighbor.addDl(RXdl);
+ neighbor.time = timestamp;
+ result.add(neighbor);
+
+ }
+ return result;
+ }
+
+ /**
+ * Returns index of currently used BTS
+ *
+ * @param btsnames
+ * @param timestamp
+ * @return
+ */
+ private SingleBTS searchForUsedBTS(SingleBTS[] btsnames, Date timestamp) {
+ if (btsnames == null || btsnames.length == 0 || timestamp == null) {
+ return null;
+ }
+ // System.out.print("searchInBetween called...");
+ SingleBTS searchelement = new SingleBTS(0, 0, 0, false, timestamp,
+ "name");
+ int between = Arrays.binarySearch(btsnames, searchelement);
+ if (between < 0) {
+ between = Math.abs(between);
+ between = between - 2;
+ }
+ // if between < 0 it means that the call would be invalid because there
+ // is no usedBTS assignable. A call was before a BTS got assigned
+ // through OpenBSC. Check if there is a x second margin!
+
+ // System.out.println("ended!");
+
+ return btsnames[between];
+ }
+
+ private SingleBTS[] getUsedBTS(long actionRefID) throws SQLException {
+ String query = "select bts.cur_timestamp, " + "bts.bts, "
+ + "arfcn.arfcn, " + "arfcn.location " + "from usedBTS bts "
+ + "join action on bts.actionRefID = " + actionRefID + " "
+ + "AND bts.actionRefID = action.ID "
+ + "JOIN arfcn ON bts.bts = arfcn.bts " + "ORDER BY bts.ID";
+ Statement st = cn.createStatement();
+ ResultSet rs = st.executeQuery(query);
+ ArrayList<SingleBTS> usedBTS = new ArrayList<SingleBTS>();
+ while (rs.next()) {
+ int ARFCN = rs.getInt("arfcn.arfcn");
+ String name = rs.getString("arfcn.location");
+ Date time = rs.getTimestamp("bts.cur_timestamp");
+ SingleBTS current = new SingleBTS(ARFCN, name);
+ current.time = time;
+ usedBTS.add(current);
+ }
+ return usedBTS.toArray(new SingleBTS[1]);
+ }
+
+ private Connection getConnection() throws ClassNotFoundException,
+ SQLException {
+ Class.forName("com.mysql.jdbc.Driver");
+ cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/logging", "richard",
+ "uh237Aug.ad7");
+ cn.setReadOnly(true);
+ return cn;
+ }
+
+}
diff --git a/Parse/SqlPollerUnThreaded.java b/Parse/SqlPollerUnThreaded.java
new file mode 100644
index 0000000..e5136c6
--- /dev/null
+++ b/Parse/SqlPollerUnThreaded.java
@@ -0,0 +1,370 @@
+package Parse;
+
+import helper.ListBTS;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Date;
+import java.util.HashSet;
+
+import DataStructure.MobilePhone;
+import DataStructure.SingleBTS;
+
+/**
+ * Man könnte auch das Datum des aktuellsten Eintrags nehmen und x Sekunden
+ * davon abziehen! Methoden: gefundene IMSI ausgeben. Zeitspanne angeben, in der
+ * man suchen will. Mitteln immer über eine (x) Sekunde?
+ *
+ * @author richy
+ *
+ */
+public class SqlPollerUnThreaded {
+ /**
+ *
+ */
+ private static final long serialVersionUID = 1L;
+ public Connection cn;
+ public long last_id;
+ public long last_ActionRef;
+ public ArrayList<MobilePhone> results = new ArrayList<MobilePhone>();
+ public int polltime = 3000;
+ public ArrayList<MobilePhone> phones;
+ private Long[] IMSIs;
+
+ // private boolean interrupt = false;
+
+ /**
+ * @param args
+ * @throws InterruptedException
+ */
+ public static void main(String[] args) throws InterruptedException {
+
+ ArrayList<Long> blalist = new ArrayList<Long>();
+ blalist.add(1l);
+ blalist.add(1l);
+ blalist.add(1l);
+ blalist.add(3l);
+ blalist.add(4l);
+ ArrayList<Long> result = new ArrayList<Long>(new HashSet<Long>(blalist));
+ System.out.println(result);
+ SqlPollerUnThreaded bla = new SqlPollerUnThreaded();
+ System.out.println("Init... Wait");
+ Thread.sleep(1300);
+ bla.getMRs();
+ Thread.sleep(1300);
+ bla.getMRs();
+ System.out.println("Done");
+
+ }
+
+ /**
+ * Between creation and use of SqlPoller, some time must pass so that the
+ * SQL table can grow!
+ */
+ public SqlPollerUnThreaded() {
+
+ // mit LIMIT(0,30) die aktuellesten n Einträge anzeigen?
+ // Oder auf cur_timestamp achten? Z.B.: die letzten drei Sekunden?
+ initSQL();
+
+ }
+
+ // public void Interrupt() {
+ // interrupt = true;
+ // }
+
+ // public boolean isInterrupted() {
+ // return interrupt;
+ // }
+
+ public ArrayList<MobilePhone> getMRs() {
+ // formerly known as run()
+ if (cn == null || last_id < 0) {
+ initSQL();
+ }
+
+ // TODO Get Elements here and store it in a public variable
+
+ try {
+ Statement st = cn.createStatement();
+ st.setQueryTimeout(polltime); // -------------------
+ ResultSet rsMR = getAllMR();
+ ArrayList<ResultSet> rsBTS = getUsedBTS(rsMR);
+ ArrayList<MobilePhone> phones = mergeMrBts(rsMR, rsBTS);
+ phones = mergePhones(phones, IMSIs);
+ this.phones = phones;
+ System.out.println(phones);
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ System.out.println("Cannot get a Statement from SQL in SqlPoller");
+ e.printStackTrace();
+ }
+ /*
+ * try { Thread.sleep(polltime); } catch (InterruptedException e) {
+ * System.out.println("sqlPoller closed"); // e.printStackTrace(); try {
+ * System.out.println("Closing Connection to SQL Server...");
+ * cn.close(); System.out.println("Closed successfully"); } catch
+ * (SQLException e1) { System.out.println(""); // e1.printStackTrace();
+ * } }
+ */
+
+ // at the end, get new last_id: prepare for next run
+ try {
+ last_id = getLastID();
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ return phones;
+ }
+
+ /**
+ * Traverses IMSIs and takes all phones from phones that match each IMSI.
+ * All MRs are joined and averaged
+ *
+ * @param phones
+ * @param IMSIs
+ * @return
+ */
+ private ArrayList<MobilePhone> mergePhones(ArrayList<MobilePhone> phones,
+ Long[] IMSIs) {
+ ArrayList<MobilePhone> result = new ArrayList<MobilePhone>();
+ for (long imsi : IMSIs) {
+ MobilePhone current = new MobilePhone();
+ current.IMSI = imsi;
+ for (int i = 0; i < phones.size(); i++) {
+ if (phones.get(i).IMSI == imsi) {
+ current.MR.addAll(phones.get(i).MR);
+ phones.remove(i);
+ i--;
+ }
+ }
+ result.add(current);
+
+ }
+ result = averagePhones(result);
+ return result;
+ }
+
+ private ArrayList<MobilePhone> averagePhones(ArrayList<MobilePhone> phones) {
+ ArrayList<MobilePhone> result = new ArrayList<MobilePhone>();
+ for (int i = 0; i < phones.size(); i++) {
+ MobilePhone current = new MobilePhone();
+ current.IMSI = phones.get(i).IMSI;
+ SingleBTS[] content = ListBTS.content(phones.get(i).MR);
+ current.MR = ListBTS
+ .generateAveragedList(phones.get(i).MR, content);
+ result.add(current);
+ }
+ return result;
+ }
+
+ private ArrayList<MobilePhone> mergeMrBts(ResultSet rsMR,
+ ArrayList<ResultSet> rsBTS) throws SQLException {
+ ArrayList<MobilePhone> phones = new ArrayList<MobilePhone>();
+ rsMR.beforeFirst();
+ while (rsMR.next()) {
+ MobilePhone current = new MobilePhone();
+ current.IMSI = rsMR.getLong("IMSI");
+ int neigh = rsMR.getInt("NUM_NEIGH");
+ if (neigh != 7) {
+ for (int i = 0; i < neigh; i++) {
+ int ARFCN = rsMR.getInt("ARFCN_" + i);
+ int rxdl = rsMR.getInt("rxlev_" + i);
+ int reason = rsMR.getInt("actionID");
+ SingleBTS new_elem = new SingleBTS(ARFCN, 0, rxdl, false,
+ new Date(), "Lookup Record");
+ new_elem.reason = reason;
+ current.MR.add(new_elem);
+ }
+ }
+ // now, search currently connected BTS for this phone's IMSI
+ int ARFCN = getARFCNforIMSI(current.IMSI, rsBTS);
+ int reason = rsMR.getInt("actionID");
+ int rxul = rsMR.getInt("RXL_SUB_ul");
+ int rxdl = rsMR.getInt("RXL_SUB_dl");
+ SingleBTS new_elem = new SingleBTS(ARFCN, rxul, rxdl, false,
+ new Date(), "Lookup Record");
+ new_elem.reason = reason;
+ current.MR.add(new_elem);
+ phones.add(current);
+
+ }
+
+ // put phones together
+ return phones;
+ }
+
+ private int getARFCNforIMSI(long IMSI, ArrayList<ResultSet> rsBTS)
+ throws SQLException {
+ for (ResultSet rs : rsBTS) {
+ rs.beforeFirst();
+ while (rs.next()) {
+ if (rs.getLong("IMSI") == IMSI) {
+ return rs.getInt("arfcn");
+ }
+ }
+ }
+ return 0;
+ }
+
+ /**
+ * Matches entries from rs with usedBTS table
+ *
+ * @param rs
+ * @throws SQLException
+ */
+ private ArrayList<ResultSet> getUsedBTS(ResultSet rs) throws SQLException {
+ // get lowest actionRefID
+ long actionRefID = getLowestRefID(rs);
+ rs.beforeFirst();
+ Long[] IMSIs = getDistinctIMSIs(rs);
+ this.IMSIs = IMSIs;
+ // now, get the usedBTS part for each IMSI that is found in rs
+ ArrayList<ResultSet> result = new ArrayList<ResultSet>();
+ for (long IMSI : IMSIs) {
+ Statement st = cn.createStatement();
+ ResultSet rs2 = st.executeQuery(usedBTSQuery(IMSI, actionRefID));
+ result.add(rs2);
+
+ // merge this stuff
+ }
+ return result;
+
+ }
+
+ private String usedBTSQuery(long IMSI, long RefID) {
+ String query = "SELECT * FROM usedBTS"
+ + " JOIN action ON usedBTS.actionRefID >= " + RefID
+ + " AND action.IMSI=" + IMSI
+ + " AND usedBTS.actionRefID = action.id"
+ + " JOIN arfcn ON arfcn.bts = usedBTS.bts"
+ + " ORDER BY usedBTS.cur_timestamp desc";
+ System.out.println("Query for usedBTS: " + query);
+ return query;
+ }
+
+ /**
+ * Traverses ResultSet from getAllMR(). Outputs all IMSIs that are inside
+ * this Set
+ *
+ * @param rs
+ * @return
+ * @throws SQLException
+ */
+ private Long[] getDistinctIMSIs(ResultSet rs) throws SQLException {
+ // make ArrayList with all IMSIs inside
+ Collection<Long> IMSIs = new ArrayList<Long>();
+ rs.beforeFirst();
+ while (rs.next()) {
+ IMSIs.add(rs.getLong("IMSI"));
+ }
+ // make Hashset
+ ArrayList<Long> result = new ArrayList<Long>(new HashSet<Long>(IMSIs));
+ return result.toArray(new Long[1]);
+
+ }
+
+ /**
+ * Takes a ResultSet with MRs. Gets the lowest actionRefID
+ *
+ * @param rs
+ * @return
+ * @throws SQLException
+ */
+ private long getLowestRefID(ResultSet rs) throws SQLException {
+ long actionRefID = Long.MAX_VALUE;
+ rs.beforeFirst();
+ while (rs.next()) {
+ if (actionRefID > rs.getLong("MR.actionRefID")) {
+ actionRefID = rs.getLong("actionRefID");
+ }
+ }
+ return actionRefID;
+ }
+
+ private void initSQL() {
+ try {
+ Class.forName("com.mysql.jdbc.Driver");
+ cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/logging", "richard",
+ "uh237Aug.ad7");
+ cn.setReadOnly(true);
+ // Connection is ready
+ // get current ID
+ // Statement getCurrID = cn.createStatement();
+ last_id = getLastID();
+ } catch (ClassNotFoundException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ }
+
+ /**
+ * Gets a resultset that contains all MeasurementReports since last_id.
+ * MIN(actionRefID is at the end of this ResultSet)
+ *
+ * @return
+ * @throws SQLException
+ * when last_id <= 0 or Connection is broken
+ */
+ private ResultSet getAllMR() throws SQLException {
+ // e.g. last id = 1702898
+ if (last_id <= 0) {
+ throw new SQLException(
+ "seems it is not inilialized. Last_id points to 0");
+ }
+ String query = "SELECT * FROM measurementResults MR "
+ + "JOIN action ON " + "MR.id >= " + last_id + " AND "
+ + "MR.actionRefID = action.ID "
+ // + "ORDER BY MR.cur_timestamp DESC";
+ + "ORDER BY MR.actionRefID";
+ System.out.println("Query for all MR is: " + query);
+ Statement st = cn.createStatement();
+ ResultSet result = st.executeQuery(query);
+ result.first();
+ // last_ActionRef = result.getLong("ActionRefID");
+ result.beforeFirst();
+ return result;
+
+ /*
+ * SELECT MR.id, MR.cur_timestamp, action.IMSI, MR.RXL_FULL_ul,
+ * MR.RXL_FULL_dl, BS_POWER, MS_TO, L1_MS_PWR, L1_TA,NUM_NEIGH, IDX_0,
+ * ARFCN_0,BSIC_0, rxlev_0, IDX_1, ARFCN_1,BSIC_1, rxlev_1, IDX_2,
+ * ARFCN_2,BSIC_2, rxlev_2, IDX_3, ARFCN_3,BSIC_3, rxlev_3, IDX_4,
+ * ARFCN_4,BSIC_4, rxlev_4, IDX_5, ARFCN_5,BSIC_5, rxlev_5, actionID
+ * FROM measurementResults MR JOIN action ON MR.id >= last_id AND
+ * MR.actionRefID = action.ID;
+ */
+
+ }
+
+ private int getLastID() throws SQLException {
+ String query = "SELECT MAX( id ) FROM measurementResults ";
+ Statement st = cn.createStatement();
+ ResultSet result = st.executeQuery(query);
+ if (result.first()) {
+ return result.getInt(1);
+ } else {
+ return -1;
+ }
+ }
+
+ public void close() {
+ try {
+ cn.close();
+ } catch (SQLException e) {
+ // TODO Auto-generated catch block
+ e.printStackTrace();
+ }
+ }
+
+}
diff --git a/Parse/sqlreader.java b/Parse/sqlreader.java
new file mode 100644
index 0000000..8268810
--- /dev/null
+++ b/Parse/sqlreader.java
@@ -0,0 +1,483 @@
+package Parse;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Date;
+import java.util.LinkedList;
+
+import DataStructure.GPScoordinate;
+import DataStructure.SingleBTS;
+
+public class sqlreader {
+ private LinkedList<GPScoordinate> gpslist;
+ public SingleBTS[] btsarray;
+ public GPScoordinate[] gpsarray;
+ private ResultSet MR_rs;
+ private ResultSet usedBTS_rs;
+ private Statement st;
+ private Date start;
+ private Date end;
+ public long IMSI;
+ public SingleBTS[] BtsNames;
+
+ /**
+ * Builds a list of MR and Coordinates. Entry x of the first list reflects
+ * element x of the second list
+ *
+ * @param nmea
+ * NMEAParse Object
+ * @param IMSI
+ * which phone
+ * @param matchtime
+ * Time in milliseconds a coordinate may be away from a
+ * measurement result
+ * @throws ClassNotFoundException
+ * when no sql driver is found
+ * @throws SQLException
+ * when no connection to databse is possible
+ */
+ // Constructor
+ public sqlreader(NMEAParse nmea, long IMSI, long matchtime)
+ throws ClassNotFoundException, SQLException {
+ this.IMSI = IMSI;
+ gpslist = nmea.getGPSList();
+ // if a call was done before a gps fix, it would crash while searching
+ // for the usedBTS. So, minus 1 minute or so
+ this.start = new Date(gpslist.getFirst().time.getTime() - 60000);
+ this.end = new Date(gpslist.getLast().time.getTime() + 60000);
+
+ openSQL();
+ // ask sql for all known BTS in database (table arfcn)
+ BtsNames = getBTSnames();
+ // get (starting)timestamps when BTS got used (during a call). Nothing
+ // else
+ SingleBTS[] usedBTStimestamps = getBTSlist();
+ // get all Measurement Reports
+ SingleBTS[] BTSarray = parseMR(usedBTStimestamps, BtsNames);
+ System.out.println("Valid SQL-Entries: " + BTSarray.length);
+ searchCorrespondingCoords(gpslist, BTSarray, matchtime);
+
+ // now, get corresponding gps-coordinates
+
+ // Date[] mrDate = openSQL(gpslist.getFirst().time,
+ // gpslist.getLast().time, IMSI);
+ // match usedBTS to MRlist (max. valid arfcn = 1023)
+ // SingleBTS[] usedBTS = getBTSlist();
+ }
+
+ // fills the MR_rs ResultSet
+ public void openSQL() throws ClassNotFoundException, SQLException {
+ // select driver
+ Class.forName("com.mysql.jdbc.Driver");
+ // open connection
+ Connection cn = DriverManager.getConnection(
+ "jdbc:mysql://132.230.4.13:3306/logging", "richard",
+ "uh237Aug.ad7");
+ cn.setReadOnly(true);
+ System.out.println("Connected to SQL");
+ st = cn.createStatement();
+ // query Timeout
+ st.setQueryTimeout(20);// ---------------------------
+
+ }
+
+ // queries the Database and fills MR_rs
+ private void fillMR_rs() throws SQLException {
+ // System.out.println("fillMR_rs called");
+ String query = queryMR();
+ try {
+ MR_rs = st.executeQuery(query);
+ } catch (SQLException e) {
+ System.out
+ .println("Timeout. SQL query hasn't finished within time");
+ e.printStackTrace();
+ }
+
+ }
+
+ /**
+ * reads the MR from SQL database. Also takes Information from
+ * btsTimestamps(usedBTS table) and the btsNames(arfcn table) to get the
+ * name and arfcn of each SingleBTS
+ *
+ * @param btsTimestamp
+ * Array: SQL table usedBTS
+ * @param btsNames
+ * Array: SQL table arfcn. Information about location and name
+ */
+ private SingleBTS[] parseMR(SingleBTS[] btsTimestamp, SingleBTS[] btsNames)
+ throws SQLException {
+ // System.out.println("parseMR called with btsTimestamp of size "
+ // + btsTimestamp.length + " and btsNames of size "
+ // + btsNames.length);
+
+ // fill SQL buffer first -> query
+ fillMR_rs();
+ MR_rs.beforeFirst();
+
+ // prepare variables
+ SingleBTS current;
+ LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
+ // traverse sql output
+ int result = 0;
+ while (MR_rs.next()) {
+ // System.out.println("getting SQL row number " + result);
+ result++;
+ // search for timestamp when this MR was created
+ Date timestamp = MR_rs.getTimestamp("cur_timestamp");
+
+ // to which BTS was the phone connected when this MR got created
+ int hit = searchForUsedBTS(btsTimestamp, timestamp);
+ if (hit < 0) {
+ System.out.println("corresponding BTS not found for " + IMSI
+ + " !");
+ hit = 0;
+ }
+ // parse SQL input for FullBTS
+ int ARFCN = btsTimestamp[hit].ARFCN;
+ int RXul = MR_rs.getInt("RXL_FULL_ul");
+ int RXdl = MR_rs.getInt("RXL_FULL_dl");
+ boolean interpolated = false;
+ String name = btsTimestamp[hit].name;
+ int TA = MR_rs.getInt("L1_TA");
+ int BS_POWER = MR_rs.getInt("BS_POWER");
+ int MS_TO = MR_rs.getInt("MS_TO");
+ int MS_PWR = MR_rs.getInt("L1_MS_PWR");
+ int reason = MR_rs.getInt("actionID");
+ int rxQualsubDL = MR_rs.getInt("RXQ_FULL_dl");
+ // int rxQualfullDL = MR_rs.getInt("RXQ_SUB_dl");
+ int rxQualsubUL = MR_rs.getInt("RXQ_SUB_ul");
+ // int rxQualfullUL = MR_rs.getInt("RXQ_FULL_ul");
+ // only add if source was a phone call!
+ if (reason == 2) {
+ // store the FullBTS details
+ current = new SingleBTS(ARFCN, RXul, RXdl, interpolated,
+ timestamp, name, TA, BS_POWER, MS_TO, MS_PWR);
+ current.fullBTS = true;
+ current.reason = reason;
+ // current.dlQfull.add((double) rxQualfullDL);
+ // current.dlQsub.add((double) rxQualsubDL);
+ // current.ulQfull.add((double) rxQualfullUL);
+ current.ulQsub.add((double) rxQualsubUL);
+ current.dlQsub.add((double) rxQualsubDL);
+ btslist.add(current);
+
+ // parse other BTS from the measurement report
+ // parse SQL input for SingleBTS. Number of neighbors: neigh
+ int neigh = MR_rs.getInt("NUM_NEIGH");
+ if (neigh == 7) {
+ neigh = 0;
+ // no more BTS available. Actually, the MR is not valid!
+ // Maybe
+ // check for this before adding the FullBTS?
+ } else {
+ for (int i = 0; i < neigh; i++) {
+ RXdl = MR_rs.getInt("rxlev_" + i);
+ // throw out BTS that are in neighbor list but not
+ // received
+ if (RXdl > -116) {
+ ARFCN = MR_rs.getInt("ARFCN_" + i);
+ // get The Name (String) of this ARFCN
+ name = searchBTSName(btsNames, ARFCN);
+ current = new SingleBTS(ARFCN, 0, RXdl, false,
+ timestamp, name);
+ current.reason = reason;
+ btslist.add(current);
+ }
+
+ }
+ }
+ }
+
+ }
+ SingleBTS[] btsarray = new SingleBTS[btslist.size()];
+ btsarray = btslist.toArray(btsarray);
+ return btsarray;
+
+ }
+
+ /**
+ * Gets the table usedBTS. It contains Timestamps when which BTS got used.
+ * This is needed to when a MS was connected to which BTS
+ *
+ * @return Array of SingleBTS with Timestamp. Timestamp indicates since when
+ * this BTS was used by the MS
+ * @throws SQLException
+ * when SQL connection is unavailable or some problem occurs
+ */
+ private SingleBTS[] getBTSlist() throws SQLException {
+ // System.out.println("getBtslist called");
+ // LinkedList<SingleBTS> list = new LinkedList<SingleBTS>();
+ usedBTS_rs = st.executeQuery(queryusedBTS());
+ int y = getRowCount(usedBTS_rs);
+ int n = 0;
+ SingleBTS[] BTSarray = new SingleBTS[y];
+ // array mit usedBTS erstellen
+ while (usedBTS_rs.next()) {
+ String name = usedBTS_rs.getString("location");
+ Date time = usedBTS_rs.getTimestamp("cur_timestamp");
+ int arfcn = usedBTS_rs.getInt("arfcn");
+ // use SingleBTS to represent usedBTS
+ BTSarray[n] = new SingleBTS(arfcn, 0, 0, false, time, name);
+ n++;
+ }
+ return BTSarray;
+ // für jeden MR einen Binary Search machen. Das Ergebnis ist die
+ // benutzte Zelle (weil binary search den insert point findet)
+
+ }
+
+ // queries for all measurementResults for given IMSI and time
+ private String queryMR() {
+ String beginning = dateToString(start);
+ String ending = dateToString(end);
+ String query = "SELECT MR.cur_timestamp, MR.RXL_FULL_ul, MR.RXL_FULL_dl, BS_POWER, "
+ + "MS_TO, L1_MS_PWR, L1_TA,NUM_NEIGH,"
+ + "IDX_0, ARFCN_0,BSIC_0, rxlev_0,"
+ + "IDX_1, ARFCN_1,BSIC_1, rxlev_1,"
+ + "IDX_2, ARFCN_2,BSIC_2, rxlev_2,"
+ + "IDX_3, ARFCN_3,BSIC_3, rxlev_3,"
+ + "IDX_4, ARFCN_4,BSIC_4, rxlev_4,"
+ + "IDX_5, ARFCN_5,BSIC_5, rxlev_5,"
+ + "actionID, RXQ_FULL_dl, RXQ_SUB_dl, RXQ_FULL_ul, RXQ_SUB_ul "
+ + "FROM measurementResults MR "
+ + "JOIN action ON MR.cur_timestamp BETWEEN "
+ + "\""
+ + beginning
+ + "\""
+ + " AND "
+ + "\""
+ + ending
+ + "\""
+ + " AND action.IMSI = "
+ + IMSI + " AND MR.actionRefID = action.ID";
+ System.out.println("Query for MR is: " + query);
+ return query;
+ }
+
+ // queries usedBTS
+ private String queryusedBTS() {
+ String beginning = dateToString(start);
+ String ending = dateToString(end);
+ String query = "select bts.cur_timestamp, " + "bts.bts, "
+ + "arfcn.arfcn, " + "arfcn.location " + "from usedBTS bts "
+ + "join action on " + "imsi = " + IMSI + " and "
+ + "bts.cur_timestamp BETWEEN \"" + beginning + "\"" + "AND "
+ + "\"" + ending + "\" " + "AND bts.actionRefID = action.ID "
+ + "JOIN arfcn ON bts.bts = arfcn.bts " + "ORDER BY bts.ID";
+ System.out.println("BTSQuery is: " + query);
+ return query;
+ }
+
+ // returns date string in mySQL-fashion
+ public static String dateToString(Date time) {
+ return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S").format(time);
+ // 2011-01-13 19:51:55.0
+ }
+
+ // parses mySQL date to java date
+ public static Date stringToDate(String s) {
+ try {
+ return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S").parse(s);
+ } catch (ParseException e) {
+ System.out.println("Cannot parse SQL timestamp");
+ e.printStackTrace();
+ System.exit(-1);
+ return null;
+ }
+ }
+
+ // finds usedBTS index for a measured BTS
+ // btsnames: SQL table "usedBTS"
+ private int searchForUsedBTS(SingleBTS[] btsnames, Date timestamp) {
+
+ // System.out.print("searchInBetween called...");
+ SingleBTS searchelement = new SingleBTS(0, 0, 0, false, timestamp,
+ "name");
+ int between = Arrays.binarySearch(btsnames, searchelement);
+ if (between < 0) {
+ between = Math.abs(between);
+ between = between - 2;
+ }
+ // if between < 0 it means that the call would be invalid because there
+ // is no usedBTS assignable. A call was before a BTS got assigned
+ // through OpenBSC. Check if there is a x second margin!
+
+ // System.out.println("ended!");
+
+ return between;
+ }
+
+ // not as speedy as it could be
+ /**
+ * gets name of bts for given arfcn. If name is not found this might
+ * indicate that the logging databse is out of date or this is a foreign
+ * BTS, for example from E-Plus
+ */
+ private String searchBTSName(SingleBTS[] btsnames, int arfcn) {
+ // System.out.println("searchBTSName called");
+ for (int i = 0; i < btsnames.length; i++) {
+ if (btsnames[i].ARFCN == arfcn)
+ return btsnames[i].name;
+ }
+ // System.out.println("arfcn Database is out of Date!");
+ return "unknown - possible foreign BTS";
+
+ }
+
+ // would be faster with a "sweep" approach! For example: get bts counter.
+ // start searching from specific point
+
+ private void searchCorrespondingCoords(LinkedList<GPScoordinate> gpslist,
+ SingleBTS[] btsarray, long matchtime) {
+ // create gpsarray out of gpslist to search efficiently
+ GPScoordinate[] gpsarray = new GPScoordinate[1];
+ gpsarray = gpslist.toArray(gpsarray);
+ // create BTS list
+ LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
+ // recycle gpslist. Clear it
+ gpslist.clear();
+ boolean insert = false;
+ GPScoordinate bestfit = new GPScoordinate(null, 0, 'N', 0, 'W', false);
+ int discarded = 0;
+ // traverse every bts, find best gps coordinate
+ for (SingleBTS singleBTS : btsarray) {
+ // check for FullBTS. After a FullBTS, other BTS follow from the
+ // Neighbor list!
+ if (singleBTS.fullBTS) {
+ bestfit = getBestCoord(gpsarray, singleBTS);
+ // check for matchtime
+ if (Math.abs(bestfit.time.getTime() - singleBTS.time.getTime()) < matchtime) {
+ insert = true;
+ } else
+ insert = false;
+ }
+ if (insert) {
+ // if insert is true, bestfit is securely initlialized!
+
+ gpslist.add(bestfit);
+ btslist.add(singleBTS);
+
+ } else
+ // do not insert any following SingleBTS. GPS coordinat and BTS
+ // time are too far away!
+ discarded++;
+
+ }
+ System.out.println("Measurements without GPS point: " + discarded);
+ // save lists to arrays
+ this.btsarray = btslist.toArray(new SingleBTS[0]);
+ this.gpsarray = gpslist.toArray(new GPScoordinate[0]);
+ if (this.btsarray.length != this.gpsarray.length)
+ System.out.println("Array sizes not correct: btsarray"
+ + btsarray.length + "Elements, gpsarray: "
+ + gpsarray.length + " Elements");
+
+ }
+
+ private GPScoordinate getBestCoord(GPScoordinate[] gpsarray,
+ SingleBTS singleBTS) {
+ int hit = Arrays.binarySearch(gpsarray, singleBTS.time);
+ // search for best fit!
+ if (hit < 0) {
+ hit++;
+ hit = Math.abs(hit);
+ }
+ // if hit is at start of the array, there's nothing more to do
+ if (hit == 0) {
+ return gpsarray[hit];
+ }
+ // hit is the last element. Take it
+ else if (hit == gpsarray.length) {
+ return gpsarray[hit - 1];
+ }
+ // hit is in between the array bounds. Check hit and hit-1 for best fit
+ else {
+ long left = gpsarray[hit - 1].time.getTime();
+ long right = gpsarray[hit].time.getTime();
+ long element = singleBTS.time.getTime();
+ if (Math.abs(left - element) < Math.abs(right - element)) {
+ return gpsarray[hit - 1];
+ } else
+ return gpsarray[hit];
+
+ }
+
+ }
+
+ private int getRowCount(ResultSet rs) throws SQLException {
+ rs.last();
+ int i = rs.getRow();
+ rs.beforeFirst();
+ return i;
+
+ }
+
+ /**
+ *
+ * @return Names and Coordinates of BTS stored in the sql database
+ * @throws SQLException
+ */
+ private SingleBTS[] getBTSnames() throws SQLException {
+ // System.out.println("getBTSNames called");
+ ResultSet btsNames = st.executeQuery("Select * from arfcn");
+ LinkedList<SingleBTS> btslist = new LinkedList<SingleBTS>();
+ SingleBTS currentBTS = null;
+ while (btsNames.next()) {
+ int ARFCN = btsNames.getInt("arfcn");
+ String name = btsNames.getString("location");
+ currentBTS = new SingleBTS(ARFCN, 0, 0, false, null, name);
+ // btslist.add(new SingleBTS(ARFCN, 0, 0, false, null, name));
+ double coord1 = btsNames.getDouble("CoordNS");
+ double coord2 = btsNames.getDouble("CoordEW");
+ currentBTS.coordinate = new GPScoordinate(null, coord1, 'N',
+ coord2, 'E', true);
+ btslist.add(currentBTS);
+ }
+ // random part, just to initiate a SingleBTS array
+ SingleBTS[] instancearray = new SingleBTS[1];
+ instancearray[0] = new SingleBTS(123, 0, 0, false, null, "unknown");
+ return btslist.toArray(instancearray);
+
+ }
+
+ /**
+ * Adds sql entries from another sqlreader to this one. BTS names stored in
+ * SQLreader must be the same!
+ *
+ * @param item
+ */
+ public void merge(sqlreader item) {
+ // only merge arrays!
+ int totalsizeGPS = item.gpsarray.length + this.gpsarray.length + 10;
+ int totalsizeBTS = item.btsarray.length + this.btsarray.length + 10;
+ ArrayList<GPScoordinate> tempGPS = new ArrayList<GPScoordinate>(
+ totalsizeGPS);
+ for (int i = 0; i < gpsarray.length; i++) {
+ tempGPS.add(gpsarray[i]);
+ }
+ for (int i = 0; i < item.gpsarray.length; i++) {
+ tempGPS.add(item.gpsarray[i]);
+ }
+
+ ArrayList<SingleBTS> tempBTS = new ArrayList<SingleBTS>(totalsizeBTS);
+ for (int i = 0; i < btsarray.length; i++) {
+ tempBTS.add(btsarray[i]);
+ }
+ for (int i = 0; i < item.btsarray.length; i++) {
+ tempBTS.add(item.btsarray[i]);
+ }
+
+ // back to array again
+ gpsarray = tempGPS.toArray(gpsarray);
+ btsarray = tempBTS.toArray(btsarray);
+ IMSI = 0;
+ }
+}