Package ch.epfl.lbd.database.objects

Examples of ch.epfl.lbd.database.objects.RelationalTable

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
      String tableName_space_dim = "trdw_space_dimension";
     
      String[] columns_fact = {"id","geom","type","name","m_obj_id","speed","direction"};
      String tableName_fact = "trdw_space_dimension";
     
      RelationalTable fact = new RelationalTable(tableName_fact,columns_fact);
      RelationalTable spaceDimension = new RelationalTable(tableName_space_dim,columns_space_dim);

      //load the tables used to create dimensions and cubes
      spaceDimension.loadObject(connection);
      fact.loadObject(connection);
     
      //TODO: load data inside table using FileReader and RelationalTable classes

      RelationalTable episodeFacts   = new RelationalTable("trdw_episode_facts");
     
      spaceDimension.loadObject(connection);
      episodeFacts.loadObject(connection);
     
      //define the space dimension
     
      Level environmentLvl = new MondrianLevel("Environment",spaceDimension,"environment_name");
      LevelProperty envNameProp = new MondrianLevelProperty(  "Environment Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "environment_name");
      environmentLvl.addProperty(envNameProp);
      LevelProperty envGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "environment_geom");
      environmentLvl.addProperty(envGeomProp);
     
      Level regionLvl = new MondrianLevel("Region",spaceDimension,"region_name");
      LevelProperty regNameProp = new MondrianLevelProperty(  "Region Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "region_name");
      regionLvl.addProperty(regNameProp);
      LevelProperty regGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "region_geom");
      regionLvl.addProperty(regGeomProp);
     
      Level areaLvl = new MondrianLevel("Area",spaceDimension,"area_name");
      LevelProperty areaNameProp = new MondrianLevelProperty(  "Area Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "area_name");
      areaLvl.addProperty(areaNameProp);
      LevelProperty areaGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "area_geom");
      areaLvl.addProperty(areaGeomProp);
     
      Hierarchy spaceHier = new MondrianHierarchy("spaceHier",spaceDimension);
      spaceHier.addLevel(environmentLvl);
      spaceHier.addLevel(regionLvl);
      spaceHier.addLevel(areaLvl);
     
      Dimension spaceDim = new MondrianDimension("Space",spaceHier);

     
      //define episode presence measure
     
      String sql = "case when get_trj_space_area_intersections(trdw_episode_facts.geom) > 0 then  ceil(1/get_trj_space_area_intersections(trdw_episode_facts.geom)) else 0 end";
     
      Measure episodePresence = new MondrianVirtualMeasure(  "Episode Presence",
                                  episodeFacts,
                                  Measure.DATATYPE_NUMERIC,
                                  Measure.AGGR_SUM,
                                  sql);
     
      //define cube
     
      Cube presenceCube = new MondrianCube("Presences",episodeFacts);
      presenceCube.addDimension(spaceDim);
      presenceCube.addMeasure(episodePresence);
     
      //define data warehouse
     
      DataWarehouse dw = new MondrianDataWarehouse("simone","test");
      dw.addCube(presenceCube);

      //logger.info(((MondrianDataWarehouse)dw).getSchema());

      //define a query
     
      //query the database using mondrian
     
      spaceDimension.releaseObject();
      episodeFacts.releaseObject();
     
      //closing the database connection
      connection.closeConnection();
    }
    catch (Exception e) {
View Full Code Here
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
              "TAG"};
   
    String tableName = "MILAN_GMD3";
   
    //SOURCE TABLE
    RelationalTable source = new RelationalTable(tableName,columns);
   
    //SOURCE DATABASE
    OracleConnection sourceConnection = new OracleConnection("connections.properties","connection1");
    sourceConnection.openConnection();
   
    //DESTINATION TABLE
    RelationalTable destination = new RelationalTable(tableName,columns);
    destination.setForced(true);
   
    //DESTINATION DATABASE
    PostgreSqlConnection destinationConnection = new PostgreSqlConnection("connections.properties","connection4");
    destinationConnection.openConnection();
   
    //LOAD TABLES
    source.loadObject(sourceConnection);
    destination.setTypes(source.getTypes());
    destination.loadObject(destinationConnection);
   
    double size = source.size();
    logger.info("source table size is "+size);
   
    logger.info("populating destination table");
    DecimalFormat twoPlaces = new DecimalFormat("0.00");

    double i = 0;
    while(true){
      i++;
      boolean success = destination.insertRow(source.getNextRow());
      if(!success) break;
      logger.info(twoPlaces.format((i/size)*100)+"% done...");
    }
   
    logger.info("destination table size is "+destination.size());
   
    //RELEASE OBJECT LOCKS AND MEMORY
    source.releaseObject();
    destination.releaseObject();
   
    //CLOSE DATABASE CONNECTIONS
    sourceConnection.closeConnection();
    destinationConnection.closeConnection();
  }
View Full Code Here
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
   
    String[] columns = {"ID","LATITUDE","LONGITUDE","NORTHING","EASTING","NAME","TYPE"};
    String tableName = "MILAN_MAP_POI";
   
    //SOURCE TABLE
    RelationalTable source = new RelationalTable(tableName,columns);
   
    //SOURCE DATABASE
    OracleConnection sourceConnection = new OracleConnection("connections.properties","connection1");
    sourceConnection.openConnection();
   
    //DESTINATION TABLE
    RelationalTable destination = new RelationalTable(tableName,columns);
    destination.setForced(true);
   
    //DESTINATION DATABASE
    PostgreSqlConnection destinationConnection = new PostgreSqlConnection("connections.properties","connection4");
    destinationConnection.openConnection();
   
    //LOAD TABLES
    source.loadObject(sourceConnection);
    destination.setTypes(source.getTypes());
    destination.loadObject(destinationConnection);
   
    double size = source.size();
    logger.info("source table size is "+size);
   
    logger.info("populating destination table");
    double i = 0;
    while(true){
      i++;
      boolean success = destination.insertRow(source.getNextRow());
      if(!success) break;
      logger.info((i/size)+"% done...");
    }
   
    logger.info("destination table size is "+destination.size());
   
    //RELEASE OBJECT LOCKS AND MEMORY
    source.releaseObject();
    destination.releaseObject();
   
    //CLOSE DATABASE CONNECTIONS
    sourceConnection.closeConnection();
    destinationConnection.closeConnection(); 
  }
View Full Code Here
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
   
    String[] columns = {"LATITUDE","LONGITUDE","DATETIME","ID","TAG"};
    String tableName = "MILAN_GMD3";
    int ROWS_TO_FETCH = 1;
    //RelationalTable table = new RelationalTable(tableName,columns);
    RelationalTable table = new RelationalTable(tableName);

    OracleConnection connection = new OracleConnection("src/connections.properties","connection1");
    connection.openConnection();
    table.loadObject(connection);
   
    logger.info("table size is "+table.size());
   
    for(int count = 0 ; count < ROWS_TO_FETCH ; count ++ ){
      Object[] data = table.getNextRow();
      logger.info(" ---- ROW "+count+" ---- ");
      for(int i = 0 ; i<columns.length ; i++ ){
        logger.info("data: "+columns[i]+" - "+data[i].toString());
      }
    }
   
    table.releaseObject();
   
    String[] columns2 = {"NAME","TIMEDATE","VALUE"};
    String tableName2 = "TEST";
   
    RelationalTable table2 = new RelationalTable(tableName2,columns2);
    table2.setForced(true);
    table2.loadObject(connection);
    Object[] values = new Object[3];
    values[0] = "name1";
    DateFormat formatter = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss");
        Date date = (Date)formatter.parse("2002.01.29.08.36.33");

    values[1] = date;
    values[2] = 1.992;
   
    table2.insertRow(values);
    table2.releaseObject();
    connection.closeConnection();
  }
View Full Code Here
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
    cubes = new ArrayList<Cube>();
   
    connection = new PostgreSqlConnection("WEB-INF/src/connections.properties","connection4");
    connection.openConnection();
   
    RelationalTable facts = new RelationalTable("trdw_episode_facts");
    RelationalTable spaceDimension = new RelationalTable("trdw_space_dimension");
   
    facts.loadObject(connection);
    spaceDimension.loadObject(connection);
       
    openTables.add(spaceDimension);
    openTables.add(facts);
   
    PresenceCube presenceCube = new PresenceCube(facts);
View Full Code Here
25
26
27
28
29
30
31
32
33
34
35
36
     
      OracleDataWarehouse dw = new OracleDataWarehouse("SIMONE","DW_2");
      dw.loadObject(connection);
     
      //create oracle dimension
      RelationalTable timeDimension = new RelationalTable("TIMEDIMENSIONTEST");
      timeDimension.loadObject(connection);
     
      OracleLevel yearLevel = new OracleLevel("YEAR 3",timeDimension,"YEAR");
      yearLevel.setDescription("the year of shipment");
      OracleLevel monthLevel = new OracleLevel("MONTH 3",timeDimension,"MONTH");
      monthLevel.setDescription("the month of shipment");
View Full Code Here
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
   
    try{
      //opening the database connection
      connection.openConnection();
     
      RelationalTable timeDimension = new RelationalTable("trdw_time_dimension");
     
      timeDimension.loadObject(connection);
     
      Level yearLevel = new MondrianLevel("Year",timeDimension,"year");
      LevelProperty timeStamp = new MondrianLevelProperty("TimeStamp",LevelProperty.TYPE_TIMESTAMP,timeDimension,"timestamp");
      yearLevel.addProperty(timeStamp);
     
      Hierarchy timeHier = new MondrianHierarchy("timeHier",timeDimension);
     
      Dimension timeDim = new MondrianDimension("Time",timeHier);
     
      timeDimension.releaseObject();
     
      logger.info(((MondrianDimension)timeDim).addToMondrianSchema());

      //closing the database connection
      connection.closeConnection();
View Full Code Here
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
      //opening the database connection
      connection.openConnection();
     
      //load the tables used to create dimensions and cubes
     
      RelationalTable spaceDimension   = new RelationalTable("trdw_space_dimension");
      RelationalTable episodeFacts   = new RelationalTable("trdw_episode_facts");
     
      spaceDimension.loadObject(connection);
      episodeFacts.loadObject(connection);
     
      //define the space dimension
     
      Level environmentLvl = new MondrianLevel("Environment",spaceDimension,"environment_name");
      LevelProperty envNameProp = new MondrianLevelProperty(  "Environment Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "environment_name");
      environmentLvl.addProperty(envNameProp);
      LevelProperty envGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "environment_geom");
      environmentLvl.addProperty(envGeomProp);
     
      Level regionLvl = new MondrianLevel("Region",spaceDimension,"region_name");
      LevelProperty regNameProp = new MondrianLevelProperty(  "Region Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "region_name");
      regionLvl.addProperty(regNameProp);
      LevelProperty regGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "region_geom");
      regionLvl.addProperty(regGeomProp);
     
      Level areaLvl = new MondrianLevel("Area",spaceDimension,"area_name");
      LevelProperty areaNameProp = new MondrianLevelProperty(  "Area Name",
                                  LevelProperty.TYPE_STRING,
                                  spaceDimension,
                                  "area_name");
      areaLvl.addProperty(areaNameProp);
      LevelProperty areaGeomProp = new MondrianLevelProperty(  "geom",
                                  LevelProperty.TYPE_GEOMETRY,
                                  spaceDimension,
                                  "area_geom");
      areaLvl.addProperty(areaGeomProp);
     
      Hierarchy spaceHier = new MondrianHierarchy("spaceHier",spaceDimension);
      spaceHier.addLevel(environmentLvl);
      spaceHier.addLevel(regionLvl);
      spaceHier.addLevel(areaLvl);
     
      Dimension spaceDim = new MondrianDimension("Space",spaceHier);

     
      //define episode presence measure
     
      String sql = "case when get_trj_space_area_intersections(trdw_episode_facts.geom) > 0 then  ceil(1/get_trj_space_area_intersections(trdw_episode_facts.geom)) else 0 end";
     
      Measure episodePresence = new MondrianVirtualMeasure(  "Episode Presence",
                                  episodeFacts,
                                  Measure.DATATYPE_NUMERIC,
                                  Measure.AGGR_SUM,
                                  sql);
     
      //define cube
     
      Cube presenceCube = new MondrianCube("Presences",episodeFacts);
      presenceCube.addDimension(spaceDim);
      presenceCube.addMeasure(episodePresence);
     
      //define data warehouse
     
      DataWarehouse dw = new MondrianDataWarehouse("simone","test");
      dw.addCube(presenceCube);

      logger.info(((MondrianDataWarehouse)dw).getSchema());

      spaceDimension.releaseObject();
      episodeFacts.releaseObject();
     
      //closing the database connection
      connection.closeConnection();
    }
    catch (Exception e) {
View Full Code Here
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
                      RelationalTable.DOUBLE,
                      RelationalTable.INTEGER,
                      RelationalTable.LINESTRING};
   
    String       trj_tableName   = "MILAN_TRAJECTORIES";
    RelationalTable trj_table     = new RelationalTable(trj_tableName,trj_columns);
    trj_table.setTypes(trj_types);
    trj_table.setForced(true);
    trj_table.loadObject(destinationConnection);
   
    //open episodes table
    String[]     epi_columns   = { "ID",
                      "START",
                      "END",
                      "MOVING_ENTITY",
                      "TYPE",
                      "AVG_SPEED",
                      "VAR_SPEED",
                      "TRJ_ID",
                      "geom"};
   
    Integer[]    epi_types    = {  RelationalTable.INTEGER,
                      RelationalTable.TIMESTAMP,
                      RelationalTable.TIMESTAMP,
                      RelationalTable.INTEGER,
                      RelationalTable.VARCHAR,
                      RelationalTable.DOUBLE,
                      RelationalTable.DOUBLE,
                      RelationalTable.INTEGER,
                      RelationalTable.LINESTRING};
   
    String        epi_tableName   = "MILAN_EPISODES";
    RelationalTable epi_table     = new RelationalTable(epi_tableName,epi_columns);
    epi_table.setTypes(epi_types);
    epi_table.setForced(true);
    epi_table.loadObject(destinationConnection);
   
    //open episode table
   
    //populate trajectory list
    //for every row
    try{
      String query = "SELECT * " +
      "  FROM milan_gmd3" +
      "  order by userid, datetime";
     
      ResultSet results = sourceConnection.getSQLQueryResults(query);
      int i = 0;
      int trj_id = 1;
      int eps = 1;
      GPSPoint lastPt = null;
      GPSPoint pt = null;
     
      Trajectory trj = new Trajectory();
      while(results.next()){
         Episode newEpisode;
         String tag     = results.getString("tag");
         double lat     = results.getDouble("longitude"); //inverted on purpose
         double lng     = results.getDouble("latitude");
         double northing   = results.getDouble("northing"); 
         double easting   = results.getDouble("easting");
         int userid     = results.getInt("userid");
         Timestamp datetime = results.getTimestamp("datetime");
         String name = "";
         String desc = "";
        if(tag.equalsIgnoreCase("B")){
          if(trj.getLastEpisode() != null){
            Object[] values = null;
            values = new Object[9];
            values[0] = (Integer)trj_id;        //ID
            values[1] = trj.getLifeSpan().getStart();  //START
            values[2] = trj.getLifeSpan().getEnd();    //END
            values[3] = trj.getMovingObject().getId();  //MOVING_ENTITY
            values[4] = "";                //TYPE
            values[5] = trj.getAvgSpeed();        //AVG_SPEED
            values[6] = trj.getAvgTravelTime();      //AVG_TRAVEL_TIME
            values[7] = 0;                //GROUP_ID
            values[8] = trj.getGeometry();//GEOM

            if(values[8] != null){
              //populate values from trj obj;
              trj_table.insertRow(values);
             
              //populate the list of episodes
              ArrayList<Episode> episodes = trj.getEpisodes();
              for(int j = 0 ; j < episodes.size() ; j++ ){
                Episode episode = episodes.get(j);
                Object[] vals = new Object[9];
                String type = "";
                if(episode instanceof Move ) type = "MOVE";
                else type = "STOP";
                vals[0] = (Integer)eps;            //ID
                vals[1] = episode.getLifeSpan().getStart();  //START
                vals[2] = episode.getLifeSpan().getEnd();  //END
                vals[3] = trj.getMovingObject().getId();  //MOVING_ENTITY
                vals[4] = type;                //TYPE
                vals[5] = episode.getAvgSpeed();      //AVG_SPEED
                vals[6] = episode.getVarSpeed();      //AVG_TRAVEL_TIME
                vals[7] = (Integer)trj_id;          //GROUP_ID
                vals[8] = episode.getGeometry();      //GEOM
                epi_table.insertRow(vals);
                eps++;
              }
             
              logger.info(trj_id+" trajectories inserted");
              trj_id++;
            }
          }
          trj = new Trajectory();
          trj.setMovingObject(new MovingObject(userid));
        }
         if(pt != null)lastPt = pt;
         pt = new GPSPoint(lat,lng,northing,easting,userid,datetime,name,desc);
         pt.comparePrePoint(lastPt);
         Episode lastEpisode = trj.getLastEpisode();
         if(tag.equalsIgnoreCase("B") || tag.equalsIgnoreCase("S")){
           if(lastEpisode != null && lastEpisode instanceof Move){
             lastEpisode.merge(new Move(pt));
           }
           newEpisode = new Stop(pt);
         }
         else{
           if(lastEpisode != null && lastEpisode instanceof Stop){
             newEpisode = new Move(lastPt);
             newEpisode.merge(new Move(pt));
           }
           else newEpisode = new Move(pt);
         }
         trj.addEpisode(newEpisode);
         i++;
      }
    }
    catch(SQLException e){
      e.printStackTrace();
    }
   
    //close tables and connections
    trj_table.releaseObject();
    epi_table.releaseObject();
   
   
    try{
      sourceConnection.closeConnection();
      destinationConnection.closeConnection();
View Full Code Here
TOP

Related Classes of ch.epfl.lbd.database.objects.RelationalTable

  • ch.epfl.lbd.applications.trdw.datawarehouses.TrajectoryDataWarehouse
  • ch.epfl.lbd.database.objects.test.TestRelationalTable
  • ch.epfl.lbd.database.providers.mondrian.olap.test.TestInstance
  • ch.epfl.lbd.database.providers.mondrian.olap.test.TestMondrianDataWarehouse
  • ch.epfl.lbd.database.providers.mondrian.olap.test.TestMondrianDimension
  • ch.epfl.lbd.database.providers.oracle.olap.test.TestAddDimension
  • ch.epfl.lbd.etl.extractors.TrajectoryExtractor
  • ch.epfl.lbd.etl.loaders.POILoader
  • ch.epfl.lbd.etl.loaders.RAWGPSLoader
  • java.sql.ResultSetMetaData
    • Copyright © 2018 www.massapicom. All rights reserved.
    All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.