/** * Copyright (C) 2007-2010 CVIS * * @file LDMReader.java * @author Steve PECHBERTI * @version 1.0.1.6 * @date 2007/01/15 * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. * * See the GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. * */ package org.cvisproject.cint.eda.provider.digitalmap.db.reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.HashMap; import java.util.Set; import java.util.StringTokenizer; import org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition; import org.cvisproject.cint.eda.sdk.gis.interfaces.IMapPOI; import org.cvisproject.cint.eda.sdk.gis.interfaces.IRoadElement; import org.cvisproject.cint.eda.sdk.gis.interfaces.position.IRAWPosition; import org.cvisproject.cint.eda.sdk.gis.mapformat.MapPOI; import org.cvisproject.cint.eda.sdk.gis.mapformat.RoadElement; public class DBReader { protected IRAWPosition m_Pos; private HashMap<String, IRoadElement> m_Elements; private HashMap<String, IRoadElement> m_Elements_bis; private HashMap m_POI; static final private String driver = "com.mysql.jdbc.Driver"; static final private String url_db = "jdbc:mysql://localhost/platadbosm"; //static final private String url_db = "jdbc:mysql://localhost/plataDBBALI"; static final private String user = "root"; static final private String passwd = ""; //static final private String passwd = "livic"; String tableSpeed="speed"; Connection con=null; Statement s = null; int i=0; public DBReader() { //ouverture de base ConnectBase(); m_Pos = null; m_Elements = new HashMap<String, IRoadElement>(); m_Elements_bis = new HashMap<String, IRoadElement>(); m_POI=new HashMap(); } public void ConnectBase() { try { /** Etape 1: charger le pilote */ Class.forName(driver); DriverManager.registerDriver(new com.mysql.jdbc.Driver()); /** Etape 2: Etablissement de la connexion à la base de données */ con = DriverManager.getConnection(url_db, user, passwd); /** Etape 3: produire le Statement */ s = con.createStatement(); System.out.println("driver load successfully"); } catch (Exception e) { System.out.println("echec pilote : " + e); } } public void DeConnectBase() { try { con.close(); } catch (Exception e) { } } public synchronized Collection getRoadElementsIn(IRAWPosition _pt, double _rayon) { Date eDate=new Date(); int k=0,l=0,m=0; String attributes; String table; String clause; ResultSet res; String ID; String LENGTH; String NAME; String SENS; String COMMON; String FW; String REF; String slatlon; int all_geo_seg; double LATITUDE; double LONGITUDE; double latmin,latmax,lonmin,lonmax; StringTokenizer tokenizer,tokenizer2; ArrayList<String> eltToRemove= new ArrayList<String>(); if(s == null) return null; if(m_Pos != null ) { if( (m_Pos.get_long() _pt.get_long()) && (m_Pos.get_lat() _pt.get_lat()) && m_Elements_bis != null) return null; } //System.out.println("nbre elements depart"+m_Elements_bis.size() ); //on tag les elements pour ne pas les remplir plusieurs fois for (String selt: m_Elements_bis.keySet()) { m_Elements_bis.get(selt).setiupdate(0); } m_Pos = _pt; latmin=_pt.get_lat()- _rayon; latmax=_pt.get_lat()+ _rayon; lonmin=_pt.get_long()- _rayon; lonmax=_pt.get_long()+ _rayon; //récupération de tous les segments attributes = "segment.id_seg,segment.name,segment.nature,segment.ref,segment.sens,segment.common,AsText(geometry)"; table = "segment"; clause = "MBRIntersects(GeomFromText('POLYGON((" + latmin + " " + lonmin + "," + latmin + " " + lonmax + "," + latmax + " " + lonmax + "," + latmax + " " + lonmin + "," + latmin + " " + lonmin + "))'),geometry)"; try { this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { m++; ID = res.getString(1); LENGTH = "100"; NAME = res.getString(2); FW = res.getString(3); REF = res.getString(4); SENS = res.getString(5); COMMON = res.getString(6); slatlon = res.getString(7); if (m_Elements_bis.containsKey(ID)){ m_Elements_bis.get(ID).setiupdate(1); } else { RoadElement elt = new RoadElement(); elt.setiupdate(2); elt.setID(ID); elt.setLength(Double.valueOf(LENGTH)); elt.setName(NAME); elt.setDirection(IRoadElement.Direction.getDirectionForOpenStreetMap(SENS)); elt.setNature(FW); elt.setRef(REF); //segment communs ArrayList allSegCommuns = new ArrayList(); if (COMMON!=null){ tokenizer = new StringTokenizer(COMMON); while (tokenizer.hasMoreTokens()){ allSegCommuns.add(tokenizer.nextToken()); } } elt.setCommon(allSegCommuns); //geometry IRAWPosition[] geoms = null; slatlon=slatlon.substring(11, slatlon.length()-1); tokenizer = new StringTokenizer(slatlon,","); all_geo_seg=tokenizer.countTokens(); geoms = new IRAWPosition[all_geo_seg]; int j=0; while (tokenizer.hasMoreTokens()){ String mlatlon=tokenizer.nextToken(); tokenizer2 = new StringTokenizer(mlatlon); LATITUDE = Double.valueOf(tokenizer2.nextToken()); LONGITUDE = Double.valueOf(tokenizer2.nextToken()); geoms[j++] = new RAWPosition(LONGITUDE, LATITUDE, 0.0); } elt.setGeometry(geoms); m_Elements_bis.put(elt.getID(), elt); } } } catch (Exception e) { System.out.println("Probleme lecture DB Segment "); } //System.out.println("nbre Segments trouvés"+ m); for (String selt: m_Elements_bis.keySet()) { if (m_Elements_bis.get(selt).getiupdate()==2) { k++; getSpeedInDB(selt); } } //on remove les elements non taggés //tous les elements à 0 sont à effacer,on les stocke for (String selt: m_Elements_bis.keySet()) { if (m_Elements_bis.get(selt).getiupdate()==0) { l++; eltToRemove.add(selt); } } for (i=0;i<eltToRemove.size();i++){ m_Elements_bis.remove(eltToRemove.get(i)); } //System.out.println("nbre elements updates"+k ); //System.out.println("nbre elements deletes"+l ); //System.out.println("nbre elements "+m_Elements_bis.size() ); setArrayElement(m_Elements_bis); Date sDate=new Date(); //System.out.println("temps DB = "+ (sDate.getTime()-eDate.getTime())); return m_Elements_bis.values(); } public synchronized Collection getPOIIn(IRAWPosition _beg, IRAWPosition _end) { String attributes; String table; String clause; String order; ResultSet res; double LATITUDE; double LONGITUDE; int VALUE; int ID_POI; RAWPosition rp = null; //récupération panel String typePanel="Panel"; attributes = "id_poi,latitude,longitude,value"; table = "poi"; clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " + "latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + """+ typePanel + """ ; order="id_poi"; try{ this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { ID_POI=res.getInt("ID_POI"); LATITUDE = res.getDouble("latitude"); LONGITUDE = res.getDouble("longitude"); VALUE = res.getInt("value"); MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE); m_POI.put(ID_POI, poi); } } catch (Exception e) { System.out.println("Probleme lecture DB POI "); } //récupération vehicule typePanel="Vehicule"; attributes = "id_poi,latitude,longitude,value"; table = "poi"; clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " + "latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + """+ typePanel + """ ; order="id_poi"; try{ this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { ID_POI=res.getInt("ID_POI"); LATITUDE = res.getDouble("latitude"); LONGITUDE = res.getDouble("longitude"); VALUE = res.getInt("value"); MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE); m_POI.put(ID_POI, poi); } } catch (Exception e) { System.out.println("Probleme lecture DB POI "); } setArrayPOI(m_POI); return m_POI.values(); } public synchronized Collection getPOIVehiculeIn(IRAWPosition _beg, IRAWPosition _end) { String attributes; String table; String clause; String order; ResultSet res; double LATITUDE; double LONGITUDE; int VALUE; int ID_POI; RAWPosition rp = null; //récupération géomètrie String typePanel="Vehicule"; attributes = "id_poi,latitude,longitude,value"; table = "poi"; clause = "(longitude BETWEEN "+ _beg.get_long() + " AND " + _end.get_long() + " AND " + "latitude BETWEEN "+ _beg.get_lat() + " AND " + _end.get_lat() + ")" + " AND type = " + """+ typePanel + """ ; order="id_poi"; try{ this.s.executeQuery("SELECT " + attributes + " FROM " + table + " WHERE " + clause+ " ORDER BY " + order); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { ID_POI=res.getInt("ID_POI"); LATITUDE = res.getDouble("latitude"); LONGITUDE = res.getDouble("longitude"); VALUE = res.getInt("value"); MapPOI poi=new MapPOI(ID_POI, LATITUDE, LONGITUDE,VALUE); m_POI.put(ID_POI, poi); } } catch (Exception e) { System.out.println("Probleme lecture DB POI "); } setArrayPOI(m_POI); return m_POI.values(); } private void setArrayElement(HashMap<String, IRoadElement> _m_Elements){ m_Elements=_m_Elements; } public HashMap<String, IRoadElement> getCurrentRoadElementMap() { return m_Elements; } public Set<String> getCurrentRoadElementKeysCollection() { return m_Elements.keySet(); } public Collection getCurrentRoadElementCollection() { return m_Elements.values(); } private void setArrayPOI(HashMap _m_POI){ m_POI=_m_POI; } public Collection getCurrentPOICollection() { //System.out.println("nbre de poi à afficher "+m_POI.size()); return m_POI.values(); } public void getSpeedInDB(String selt) { String attributes,clause,order; double SPEED,ABSCURDEBUT,ABSCURFIN; ResultSet res; try { //récupération vitesse attributes = "value,abs_cur_beg,abs_cur_end"; clause = "(id_seg LIKE "+ m_Elements_bis.get(selt).getID() + ")"; order="id_speed"; this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause+ " ORDER BY " + order); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { SPEED = res.getDouble("value"); ABSCURDEBUT = res.getDouble("abs_cur_beg"); ABSCURFIN = res.getDouble("abs_cur_end"); m_Elements_bis.get(selt).getMandatorySpeedDistribution().addNewRules(new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}, new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}); } } catch (Exception e) { System.out.println("Probleme lecture DB speed "); } } public void getSpeedInDBforElement(RoadElement elt) { String attributes,clause,order; double SPEED,ABSCURDEBUT,ABSCURFIN; ResultSet res; elt.getMandatorySpeedDistribution("newSLD"); try { //récupération vitesse attributes = "value,abs_cur_beg,abs_cur_end"; clause = "(id_seg LIKE "+ elt.getID() + ")"; order="id_speed"; this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause+ " ORDER BY " + order); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { SPEED = res.getDouble("value"); ABSCURDEBUT = res.getDouble("abs_cur_beg"); ABSCURFIN = res.getDouble("abs_cur_end"); elt.getMandatorySpeedDistribution().addNewRules(new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}, new double[]{ABSCURDEBUT,ABSCURFIN,SPEED}); } } catch (Exception e) { System.out.println("Probleme lecture DB speed "); } } public void setSpeedInDB(String id_seg, Integer id_speed, Double value, Double abs_cur_beg, Double abs_cur_end, String myDate) { String request; request "UPDATE " + tableSpeed + " SET value " + value + ", abs_cur_beg = " + abs_cur_beg + ", abs_cur_end = " + abs_cur_end +", lastmodified = " + """+ myDate + """+ " WHERE id_seg = " + id_seg + " AND id_speed = " + id_speed; //System.out.println("request "+request); try { s.executeUpdate (request); } catch (SQLException e) { System.out.println("echec requete : " + e); } } public void setPartSpeedInDB(String id_seg, Integer id_speed, Double value) { String request; request "UPDATE " + tableSpeed + " SET value " + value + " WHERE id_seg = " + id_seg + " AND id_speed = " + id_speed; //System.out.println("request "+request); try { s.executeUpdate (request); } catch (SQLException e) { System.out.println("echec requete : " + e); } } public void setPOIInDB(String id_seg, Integer id_speed, Double value, Double abs_cur, Double abs_cur_end, String myDate) { String typePanel="Panel"; String request; request "UPDATE poi SET value " + value + " WHERE id_seg = " + id_seg + " AND abs_cur LIKE " + abs_cur+ " AND type = " + """+ typePanel + """ ; System.out.println("request "+request); try { s.executeUpdate (request); } catch (SQLException e) { } } public void setVehiculePOIInDB(String emetteurID, double _lat, double _long) { int iallreadyhere = 0; ResultSet res = null; String request; String typePanel="Vehicule"; int value=0; try { this.s.executeQuery("SELECT COUNT(*) AS vehicule FROM poi WHERE id_poi = " + emetteurID); res.next(); iallreadyhere= res.getInt("vehicule"); } catch (Exception e) {} if (iallreadyhere==0){ if (emetteurID=="AA-000-AA"){ value=1; }else{ value=1; } request = "INSERT INTO poi (id_poi, type,latitude,longitude,value)"; request += ") VALUES (" +emetteurID; request += ", " + typePanel; request += ", " +_lat; request += ", " +_long; request += ", " + value; request += ");"; try { int r = s.executeUpdate (request); } catch (SQLException e) { } }else{ request "UPDATE poi SET latitude " + _lat + " AND longitude = " + _long + " WHERE id_poi = " + emetteurID ; try { s.executeUpdate (request); } catch (SQLException e) {} } } //renvoie la date en milliseconde de la derniere modification dans la table Speed public long getLastUpdateDate() { long lresult=0;; Timestamp result = null; ResultSet res; try { this.s.executeQuery("SELECT MAX(lastmodified) AS maxdate FROM " + tableSpeed ); res =s.getResultSet(); res.first(); result=res.getTimestamp("maxdate"); System.out.println("date DB last modification "+result); lresult=result.getTime(); } catch (SQLException e) { System.out.println("Probleme lecture DB LastUpdateDate"); } catch (Exception e) { System.out.println("Probleme lecture DB LastUpdateDate"); } return lresult; } public ArrayList segmentIDTOUpdateList(long _div) { ArrayList alseglistToUpdate=new ArrayList(); ResultSet res; String attributes,clause; String resultat; String olddate= millisecondesToDateFormatedTimeStamp(_div); try { attributes = "id_seg,id_speed,value,abs_cur_beg,abs_cur_end,lastmodified"; clause = "(lastmodified > "+ """+ olddate + """+ ")"; this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { resultat = res.getString("id_seg"); resultat = resultat + "," +res.getInt("id_speed"); alseglistToUpdate.add(resultat); } } catch (Exception e) { System.out.println("Probleme lecture DB speed "); } return alseglistToUpdate; } public String segmentIDTOUpdate(String _segid) { ResultSet res; String attributes,clause; String resultat = null; StringTokenizer tokenizer = new StringTokenizer(_segid,","); String ID_SEG=tokenizer.nextToken(); Integer ID_SPEED=Integer.valueOf(tokenizer.nextToken()); try { attributes = "id_seg,id_speed,value,abs_cur_beg,abs_cur_end,lastmodified"; clause "id_seg " + ID_SEG + " AND id_speed = " + ID_SPEED; this.s.executeQuery("SELECT " + attributes + " FROM " + tableSpeed + " WHERE " + clause); res =s.getResultSet(); res.beforeFirst(); while (res.next()) { resultat = res.getString("id_seg"); resultat = resultat + "," +res.getInt("id_speed"); resultat = resultat + "," +res.getDouble("value"); resultat = resultat + "," +res.getDouble("abs_cur_beg"); resultat = resultat + "," +res.getDouble("abs_cur_end"); resultat = resultat + "," +res.getTimestamp("lastmodified"); } } catch (Exception e) { System.out.println("Probleme lecture DB speed "); } return resultat; } public String millisecondesToDateFormatedTimeStamp(long _d) { String sD = null; java.text.SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date d=new Date(_d); sD=format.format(d); return sD; } static public void main(String[] _argv) { double EHORIZON_RANGE = 0.002; DBReader dbr = new DBReader(); //Date d=new Date(); //Date d2= new Date(d.getTime()-7200000); //Date d1=dbr.setSpeedInDB("31110866", 0, 30.0, 0.0, 111.333997669694); //long titi=dbr.getLastUpdateDate(); //String al=dbr.segmentIDTOUpdate("31110866,0"); dbr.getRoadElementsIn((org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition) new RAWPosition(2.089261547 , 48.78770688, 0.0 ), EHORIZON_RANGE); //dbr.getPOIIn((org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition)new RAWPosition(2.10241166666667 - EHORIZON_RANGE, 48.782663333333 - EHORIZON_RANGE, 0.0), // (org.cvisproject.cint.eda.sdk.gis.coordinatesystem.positionning.RAWPosition)new RAWPosition(2.10241166666667 + EHORIZON_RANGE, 48.782663333333+ EHORIZON_RANGE, 0.0)); } }
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionpublic void run() {//thread vers la DB if(!isInitiate) init(); while(isActivate) { try { //coordonnees GPS selon les criteres auto et MAP_FREE IRAWPosition p =getRAWPosition(); ResultSet resultatDest = requete.executeQuery("select * from T_destinations"); while (resultat.next()) { System.out.println(resultatDest.getInt(1)+" "+resultatDest.getString(2)); //chargement des elements de la BD boolean bNotStarted=false; if(p != null) if (p.resultatDest.getInt(1)==0.0 && resultatDest.getInt(2)==0.0) bNotStarted=true; if(p != null && !bNotStarted) { map_engine.setMapBoundaries(new RAWPosition(resultatDest.getInt(1)-0.015,resultatDest.getInt(2)-0.010,0.0),new RAWPosition(resultatDest.getInt(1)+0.015,resultatDest.getInt(2)+0.010,0.0)); Collection network = getFormattedRoadElementInArea(new RAWPosition(resultatDest.getInt(1)-0.014, resultatDest.getInt(2)-0.009, 0.0), new RAWPosition(resultatDest.getInt(1)+0.014, resultatDest.getInt(1)+0.009,0.0)); map_engine.setMapElementsNetwork(network); Collection poi = getFormattedPOIInArea(new RAWPosition(resultatDest.getInt(1)-0.014,resultatDest.getInt(1)-0.009,0.0),new RAWPosition(resultatDest.getInt(1)+0.014,resultatDest.getInt(2)+0.009,0.0)); map_engine.setPOI(poi); } //_________________ } } catch(Exception e) { System.out.println("Exception"); } finally { DeConnectBase(); } Thread.sleep(10000); } catch (InterruptedException e) { interrupt(); } } }
import java.net.*; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.resultatDest; import java.io.*; public class client { public static void main(String argv[]) throws SQLException { Socket s = null; resultatDest requete =null; ResultSet resultatDest = requete.executeQuery("select * from T_destinations"); try { // on crée une connexion vers le service port entré en parametre sur la machine s = new Socket("localhost", 12001); System.out.println(resultatDest.getDouble(1)+" "+resultatDest.getDouble(2)); BufferedReader sin = new BufferedReader(new InputStreamReader(s.getInputStream())); PrintWriter sout = new PrintWriter(s.getOutputStream()); System.out.println(s.getPort()); // on envoie la lattitude sout.println(resultatDest.getDouble(1)); sout.flush(); //on envoie la longitude sout.println(resultatDest.getDouble(2)); sout.flush(); } catch (UnknownHostException e) { System.err.println("La machine est inconnue"); } catch (IOException e) { System.err.println("Erreur d'entrée-sortie"); }; } }
Installed: file:./bundles.d/org.cvisproject.cint.eda.service.speedlimit.iv-1.0.1.6.jar (id#635) Installed: file:./bundles.d/org.cvisproject.cint.eda.service.ghostdriver.iv-1.0.1.6.jar (id#636) Installed: file:./bundles.d/org.cvisproject.cint.eda.service.breakalert.iv-1.0.1.6.jar (id#637) Installed: file:./bundles.d/org.cvisproject.cint.eda.service.spy.iv-1.0.1.6.jar (id#638) Installed: file:./bundles.d/org.cvisproject.cint.eda.debugger-1.0.1.6.jar (id#639) Installed: file:./bundles.d/org.cvisproject.cint.eda.service.rtmaps.iv-1.0.1.6.jar (id#640) Started: file:./bundles.d/org.cvisproject.cint.eda.host.iv-1.0.1.6.jar (id#630) Started: file:./bundles.d/org.cvisproject.cint.eda.provider.communication-1.0.1.6.jar (id#631) Started: file:./bundles.d/org.cvisproject.cint.eda.provider.sensordata-1.0.1.6.jar (id#632) Started: file:./bundles.d/org.cvisproject.cint.eda.provider.digitalmap.db-1.0.1.6.jar (id#633) Started: file:./bundles.d/org.cvisproject.cint.eda.provider.graphicuserinterface-1.0.1.6.jar Started: file:./bundles.d/org.cvisproject.cint.eda.service.speedlimit.iv-1.0.1.6.jar (id#635) [stdout] Framework launched [stdout] BA-IV service is on [stdout] SPY-IV service is on [stdout] RTMAPS-IV service is on [stderr] java.net.ConnectException: Connection refused: connect [stderr] at java.net.PlainSocketImpl.socketConnect(Native Method) [stderr] at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333) [stderr] at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195) [stderr] at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182) [stderr] at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366) [stderr] at java.net.Socket.connect(Socket.java:529) [stderr] at java.net.Socket.connect(Socket.java:478) [stderr] at java.net.Socket.(Socket.java:375) [stderr] at java.net.Socket.(Socket.java:189) [stderr] at