Package ch.epfl.lbd.database.providers.postgresql.connection

Examples of ch.epfl.lbd.database.providers.postgresql.connection.PostgreSqlConnection

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
public class TestPostgreSqlConnection extends Tester {
 
  @Test
  public void run()throws Exception{
   
    PostgreSqlConnection connection = new PostgreSqlConnection("src/connections.properties","connection4");
   
    try{
      //opening the database connection
      connection.openConnection();
     
      //get user tables TEST
      ArrayList<String> tbls = connection.getUserTables();
      for(String table : tbls)logger.info("Table Found: "+table);

      //select not existing table TEST
      connection.getSQLQueryResults("SELECT * FROM notexisting");
     
      //select an existing table
      ResultSet results = connection.getSQLQueryResults("SELECT * FROM spatial_ref_sys",10);
      while (results.next())logger.info(results.getString(1));
      results.close();
     
      //closing the database connection
      connection.closeConnection();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
View Full Code Here
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
    //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
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
    //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
25
26
27
28
29
30
31
32
33
34
35
    super("unknown","Trajectory Data Warehouse");
   
    openTables = new ArrayList<RelationalTable>();
    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");
   
View Full Code Here
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
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
137
138
139
public class TestInstance extends Tester {
 
  @Test
  public void run() throws Exception{
   
    PostgreSqlConnection connection = new PostgreSqlConnection("src/connections.properties","connection4");
   
    try{
      //opening the database connection
      connection.openConnection();
     
      //create the relational tables
     
      String[] columns_space_dim = {"id","environment_name","environment_geom","region_name","region_geom","area_name","area_geom"};
      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) {
      e.printStackTrace();
    }
  }
View Full Code Here
17
18
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
public class TestMondrianDimension extends Tester {
 
  @Test
  public void run()throws Exception{
   
    PostgreSqlConnection connection = new PostgreSqlConnection("src/connections.properties","connection4");
   
    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();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
View Full Code Here
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
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
public class TestMondrianDataWarehouse extends Tester {
 
  @Test
  public void run() throws Exception{
   
    PostgreSqlConnection connection = new PostgreSqlConnection("src/connections.properties","connection4");
   
    try{
      //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) {
      e.printStackTrace();
    }
  }
View Full Code Here
36
37
38
39
40
41
42
43
44
45
46
    try{
      //open source connection
      sourceConnection = new OracleConnection("connections.properties","connection1");
      sourceConnection.openConnection();
      //open destination connection
      destinationConnection = new PostgreSqlConnection("connections.properties","connection4");
      destinationConnection.openConnection();
    }
    catch(IOException e){
      e.printStackTrace();
    }
View Full Code Here
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
  }
 
  @Test
  public void testOneTrajectory() throws Exception {
   
    PostgreSqlConnection conn = new PostgreSqlConnection("connections.properties","connection4");
    conn.openConnection();
   
    String query = "SELECT * " +
        "  FROM milan_gmd3" +
        "  WHERE userid = 288437 " +
        "    AND date_trunc('day', datetime) = date('2007-04-05') " +
        "    AND id < 1100000" +
        "  ORDER BY datetime  " +
        "  LIMIT 200";
   
    ResultSet results = conn.getSQLQueryResults(query);
   
    Trajectory trj = new Trajectory();
    GPSPoint lastPt = null;
    GPSPoint pt = null;
    while (results.next()){
       Episode newEpisode;
       String tag     = results.getString("tag");
       double lat     = results.getDouble("latitude");
       double lng     = results.getDouble("longitude");
       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(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);
    }
 
    logger.info("MOVES");
    ArrayList<Move> moves = trj.getMoves();
    for(Move move : moves)logger.info(move.getAvgSpeed());
    logger.info("STOPS");
    ArrayList<Stop> stops = trj.getStops();
    for(Stop stop : stops)logger.info(stop.getAvgSpeed());

    conn.closeConnection();
  }
View Full Code Here
TOP

Related Classes of ch.epfl.lbd.database.providers.postgresql.connection.PostgreSqlConnection

  • ch.epfl.lbd.applications.trdw.datawarehouses.TrajectoryDataWarehouse
  • 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.postgresql.connection.test.TestPostgreSqlConnection
  • ch.epfl.lbd.database.spatial.LatLngPoint
  • ch.epfl.lbd.database.spatial.PolyLine
  • ch.epfl.lbd.etl.extractors.TrajectoryExtractor
  • ch.epfl.lbd.etl.loaders.POILoader
  • ch.epfl.lbd.etl.loaders.RAWGPSLoader
    • 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.