001    /*
002     * $Id: STIStormDataSource.java,v 1.1 2012/01/04 20:40:51 tommyj Exp $
003     *
004     * This file is part of McIDAS-V
005     *
006     * Copyright 2007-2012
007     * Space Science and Engineering Center (SSEC)
008     * University of Wisconsin - Madison
009     * 1225 W. Dayton Street, Madison, WI 53706, USA
010     * https://www.ssec.wisc.edu/mcidas
011     * 
012     * All Rights Reserved
013     * 
014     * McIDAS-V is built on Unidata's IDV and SSEC's VisAD libraries, and
015     * some McIDAS-V source code is based on IDV and VisAD source code.  
016     * 
017     * McIDAS-V is free software; you can redistribute it and/or modify
018     * it under the terms of the GNU Lesser Public License as published by
019     * the Free Software Foundation; either version 3 of the License, or
020     * (at your option) any later version.
021     * 
022     * McIDAS-V is distributed in the hope that it will be useful,
023     * but WITHOUT ANY WARRANTY; without even the implied warranty of
024     * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
025     * GNU Lesser Public License for more details.
026     * 
027     * You should have received a copy of the GNU Lesser Public License
028     * along with this program.  If not, see http://www.gnu.org/licenses.
029     */
030    
031    package edu.wisc.ssec.mcidasv.data.cyclone;
032    
033    import java.io.File;
034    import java.sql.Connection;
035    import java.sql.DriverManager;
036    import java.sql.ResultSet;
037    import java.sql.SQLException;
038    import java.sql.Statement;
039    import java.util.ArrayList;
040    import java.util.Calendar;
041    import java.util.GregorianCalendar;
042    import java.util.HashMap;
043    import java.util.Hashtable;
044    import java.util.List;
045    import java.util.Map;
046    
047    import ucar.unidata.data.BadDataException;
048    import ucar.unidata.data.DataSourceDescriptor;
049    import ucar.unidata.data.DataUtil;
050    import ucar.unidata.sql.SqlUtil;
051    import ucar.unidata.util.DateUtil;
052    import ucar.unidata.util.IOUtil;
053    import ucar.unidata.util.Misc;
054    import ucar.visad.Util;
055    import visad.CommonUnit;
056    import visad.DateTime;
057    import visad.Real;
058    import visad.RealType;
059    import visad.VisADException;
060    import visad.georef.EarthLocation;
061    import visad.georef.EarthLocationLite;
062    
063    /**
064     * Created by IntelliJ IDEA. User: yuanho Date: Apr 9, 2008 Time: 4:58:27 PM To
065     * change this template use File | Settings | File Templates.
066     */
067    public class STIStormDataSource extends StormDataSource {
068    
069            /** _more_ */
070            private static final Way DEFAULT_OBSERVATION_WAY = new Way("babj");
071    
072            /* Use this for mysql: */
073    
074            /** _more_ */
075            private static final String DEFAULT_URL = "jdbc:mysql://localhost:3306/typhoon?zeroDateTimeBehavior=convertToNull&user=yuanho&password=password";
076    
077            // private static final String DEFAULT_URL =
078            // "jdbc:mysql://localhost:3306/typhoon?zeroDateTimeBehavior=convertToNull&user=yuanho&password=password";
079    
080            /** _more_ */
081            private static final String DEFAULT_DERBY_URL = "jdbc:derby:test;create=true";
082    
083            /** _more_ */
084            private static final String COL_DERBY_HOUR = "hh";
085    
086            /** _more_ */
087            private static final String COL_DERBY_YEAR = "yyyy";
088    
089            /**
090             * _more_
091             * 
092             * @return _more_
093             */
094            private boolean useDerby() {
095                    if ((dbUrl != null) && (dbUrl.indexOf("derby") >= 0)) {
096                            return true;
097                    }
098                    return false;
099            }
100    
101            /**
102             * _more_
103             * 
104             * @return _more_
105             */
106            public String getId() {
107                    return "sti";
108            }
109    
110            /**
111             * _more_
112             * 
113             * @return _more_
114             */
115            private String getColHour() {
116                    if (useDerby()) {
117                            return COL_DERBY_HOUR;
118                    }
119                    return COL_TYPHOON_HOUR;
120            }
121    
122            /**
123             * _more_
124             * 
125             * @return _more_
126             */
127            private String getColYear() {
128                    if (useDerby()) {
129                            return COL_DERBY_YEAR;
130                    }
131                    return COL_TYPHOON_YEAR;
132            }
133    
134            /** _more_ */
135            public static StormParam PARAM_MAXWINDSPEED;
136    
137            /** _more_ */
138            public static StormParam PARAM_RADIUSMODERATEGALE;
139    
140            /** _more_ */
141            public static StormParam PARAM_RADIUSWHOLEGALE;
142    
143            /** _more_ */
144            public static StormParam PARAM_PROBABILITY10RADIUS;
145    
146            /** _more_ */
147            public static StormParam PARAM_PROBABILITY20RADIUS;
148    
149            /** _more_ */
150            public static StormParam PARAM_PROBABILITY30RADIUS;
151    
152            /** _more_ */
153            public static StormParam PARAM_PROBABILITY40RADIUS;
154    
155            /** _more_ */
156            public static StormParam PARAM_PROBABILITY50RADIUS;
157    
158            /** _more_ */
159            public static StormParam PARAM_PROBABILITY60RADIUS;
160    
161            /** _more_ */
162            public static StormParam PARAM_PROBABILITY70RADIUS;
163    
164            /** _more_ */
165            public static StormParam PARAM_PROBABILITY80RADIUS;
166    
167            /** _more_ */
168            public static StormParam PARAM_PROBABILITY90RADIUS;
169    
170            /** _more_ */
171            public static StormParam PARAM_DISTANCE_ERROR;
172    
173            /** _more_ */
174            public static StormParam PARAM_PROBABILITY100RADIUS;
175    
176            /** _more_ */
177            public static StormParam PARAM_PROBABILITYRADIUS;
178    
179            /** _more_ */
180            public static StormParam PARAM_MOVEDIRECTION;
181    
182            /** _more_ */
183            public static StormParam PARAM_MOVESPEED;
184    
185            /** _more_ */
186            private static float MISSING = 9999.0f;
187    
188            /** _more_ */
189            private static final String ZEROHOUR = "0";
190    
191            /** _more_ */
192            private static final String TABLE_TRACK = "typhoon";
193    
194            /** _more_ */
195            private static final String COL_TYPHOON_YEAR = "year";
196    
197            /** _more_ */
198            private static final String COL_TYPHOON_HOUR = "hour";
199            /**/
200    
201            /** _more_ */
202            private static final String COL_TYPHOON_STORMID = "nno";
203    
204            /** _more_ */
205            private static final String COL_TYPHOON_TIME = "time";
206    
207            /** _more_ */
208            private static final String COL_TYPHOON_LATITUDE = "lat";
209    
210            /** _more_ */
211            private static final String COL_TYPHOON_LONGITUDE = "lon";
212    
213            /** _more_ */
214            private static final String COL_TYPHOON_MONTH = "mon";
215    
216            /** _more_ */
217            private static final String COL_TYPHOON_DAY = "day";
218    
219            /** _more_ */
220            private static final String COL_TYPHOON_FHOUR = "fhour";
221    
222            /** _more_ */
223            private static final String COL_TYPHOON_WAY = "way";
224    
225            /** _more_ */
226            private static final String COL_TYPHOON_PRESSURE = "pressure";
227    
228            /** _more_ */
229            private static final String COL_TYPHOON_WINDSPEED = "wind";
230    
231            /** _more_ */
232            private static final String COL_TYPHOON_RADIUSMG = "xx1";
233    
234            /** _more_ */
235            private static final String COL_TYPHOON_RADIUSWG = "xx2";
236    
237            /** _more_ */
238            private static final String COL_TYPHOON_MOVEDIR = "xx3";
239    
240            /** _more_ */
241            private static final String COL_TYPHOON_MOVESPEED = "xx4";
242    
243            /** _more_ */
244            private static final String TABLE_PROBILITY = "probility";
245    
246            /** _more_ */
247            private static final String COL_PROBILITY_WAYNAME = "wayname";
248    
249            /** _more_ */
250            private static final String COL_PROBILITY_FHOUR = "fhour";
251    
252            /** _more_ */
253            private static final String COL_PROBILITY_P10 = "p10";
254    
255            /** _more_ */
256            private static final String COL_PROBILITY_P20 = "p20";
257    
258            /** _more_ */
259            private static final String COL_PROBILITY_P30 = "p30";
260    
261            /** _more_ */
262            private static final String COL_PROBILITY_P40 = "p40";
263    
264            /** _more_ */
265            private static final String COL_PROBILITY_P50 = "p50";
266    
267            /** _more_ */
268            private static final String COL_PROBILITY_P60 = "p60";
269    
270            /** _more_ */
271            private static final String COL_PROBILITY_P70 = "p70";
272    
273            /** _more_ */
274            private static final String COL_PROBILITY_P80 = "p80";
275    
276            /** _more_ */
277            private static final String COL_PROBILITY_P90 = "p90";
278    
279            /** _more_ */
280            private static final String COL_PROBILITY_P100 = "p100";
281    
282            /** _more_ */
283            private static final String COL_DISTANCE_ERROR = "error";
284    
285            /** _more_ */
286            private static final String COL_PROBILITY_REMARK = "remark";
287    
288            /** _more_ */
289            private String dbUrl;
290    
291            /** the db connection */
292            private Connection connection;
293    
294            /** _more_ */
295            private String fromDate = "-1 year";
296    
297            /** _more_ */
298            private String toDate = "now";
299    
300            /** the stormInfo and track */
301            private List<StormInfo> stormInfos;
302    
303            /** _more_ */
304            private HashMap<String, float[]> wayfhourToRadius;
305    
306            /**
307             * constructor of sti storm data source
308             * 
309             * 
310             * @throws Exception
311             *             _more_
312             */
313    
314            public STIStormDataSource() throws Exception {
315            }
316    
317            /**
318             * _more_
319             * 
320             * @return _more_
321             */
322            public boolean isEditable() {
323                    return true;
324            }
325    
326            static {
327                    try {
328                            // TODO: Make sure these are the right units
329                            PARAM_MAXWINDSPEED = new StormParam(makeRealType("maxwindspeed",
330                                            "Max_Windspeed", Util.parseUnit("m/s")));
331                            PARAM_RADIUSMODERATEGALE = new StormParam(makeRealType(
332                                            "radiusmoderategale", "Radius_of_Beaufort_Scale7", DataUtil
333                                                            .parseUnit("km")));
334                            PARAM_RADIUSWHOLEGALE = new StormParam(makeRealType(
335                                            "radiuswholegale", "Radius_of_Beaufort_Scale10", DataUtil
336                                                            .parseUnit("km")));
337                            PARAM_MOVEDIRECTION = new StormParam(makeRealType("movedirection",
338                                            "Storm_Direction", CommonUnit.degree));
339                            PARAM_MOVESPEED = new StormParam(makeRealType("movespeed",
340                                            "Storm_Speed", Util.parseUnit("m/s")));
341    
342                            PARAM_PROBABILITY10RADIUS = new StormParam(makeRealType(
343                                            "probabilityradius10", "Probability_10%_Radius", DataUtil
344                                                            .parseUnit("km")), true, false, false);
345                            PARAM_PROBABILITY20RADIUS = new StormParam(makeRealType(
346                                            "probabilityradius20", "Probability_20%_Radius", DataUtil
347                                                            .parseUnit("km")), true, false, false);
348                            PARAM_PROBABILITY30RADIUS = new StormParam(makeRealType(
349                                            "probabilityradius30", "Probability_30%_Radius", DataUtil
350                                                            .parseUnit("km")), true, false, false);
351                            PARAM_PROBABILITY40RADIUS = new StormParam(makeRealType(
352                                            "probabilityradius40", "Probability_40%_Radius", DataUtil
353                                                            .parseUnit("km")), true, false, false);
354                            PARAM_PROBABILITY50RADIUS = new StormParam(makeRealType(
355                                            "probabilityradius50", "Probability_50%_Radius", DataUtil
356                                                            .parseUnit("km")), true, false, false);
357                            PARAM_PROBABILITY60RADIUS = new StormParam(makeRealType(
358                                            "probabilityradius60", "Probability_60%_Radius", DataUtil
359                                                            .parseUnit("km")), true, false, false);
360                            PARAM_PROBABILITY70RADIUS = new StormParam(makeRealType(
361                                            "probabilityradius70", "Probability_70%_Radius", DataUtil
362                                                            .parseUnit("km")), true, false, false);
363                            PARAM_PROBABILITY80RADIUS = new StormParam(makeRealType(
364                                            "probabilityradius80", "Probability_80%_Radius", DataUtil
365                                                            .parseUnit("km")), true, false, false);
366                            PARAM_PROBABILITY90RADIUS = new StormParam(makeRealType(
367                                            "probabilityradius90", "Probability_90%_Radius", DataUtil
368                                                            .parseUnit("km")), true, false, false);
369                            PARAM_PROBABILITY100RADIUS = new StormParam(makeRealType(
370                                            "probabilityradius100", "Probability_100%_Radius", DataUtil
371                                                            .parseUnit("km")), true, false, false);
372                            PARAM_DISTANCE_ERROR = new StormParam(makeRealType(
373                                            "meanDistanceError", "Mean_Distance_Error", DataUtil
374                                                            .parseUnit("km")), true, false, false);
375                    } catch (Exception exc) {
376                            System.err.println("Error creating storm params:" + exc);
377                            exc.printStackTrace();
378    
379                    }
380            }
381    
382            /**
383             * _more_
384             * 
385             * @throws VisADException
386             *             _more_
387             */
388            protected void initParams() throws VisADException {
389                    super.initParams();
390    
391                    obsParams = new StormParam[] { PARAM_MAXWINDSPEED, PARAM_MINPRESSURE,
392                                    PARAM_RADIUSMODERATEGALE, PARAM_RADIUSWHOLEGALE,
393                                    PARAM_MOVEDIRECTION, PARAM_MOVESPEED };
394    
395                    forecastParams = new StormParam[] {
396                                    PARAM_MAXWINDSPEED,
397                                    PARAM_MINPRESSURE,
398                                    PARAM_RADIUSMODERATEGALE,
399                                    PARAM_RADIUSWHOLEGALE,
400                                    PARAM_MOVEDIRECTION,
401                                    PARAM_MOVESPEED, // PARAM_DISTANCEERROR,
402                                    PARAM_PROBABILITY10RADIUS, PARAM_PROBABILITY20RADIUS,
403                                    PARAM_PROBABILITY30RADIUS, PARAM_PROBABILITY40RADIUS,
404                                    PARAM_PROBABILITY50RADIUS, PARAM_PROBABILITY60RADIUS,
405                                    PARAM_PROBABILITY70RADIUS, PARAM_PROBABILITY80RADIUS,
406                                    PARAM_PROBABILITY90RADIUS, PARAM_PROBABILITY100RADIUS,
407                                    PARAM_DISTANCE_ERROR };
408            }
409    
410            /**
411             * _more_
412             * 
413             * @param descriptor
414             *            _more_
415             * @param url
416             *            _more_
417             * @param properties
418             *            _more_
419             * 
420             * @throws Exception
421             *             _more_
422             */
423            public STIStormDataSource(DataSourceDescriptor descriptor, String url,
424                            Hashtable properties) throws Exception {
425                    super(descriptor, "STI Storm Data", "STI Storm Data", properties);
426                    if ((url != null) && url.trim().equals("test")) {
427                            url = DEFAULT_DERBY_URL;
428                    }
429                    if ((url == null) || url.trim().equalsIgnoreCase("default")
430                                    || (url.trim().length() == 0)) {
431                            url = (useDerby() ? DEFAULT_DERBY_URL : DEFAULT_URL);
432                    }
433                    dbUrl = url;
434            }
435    
436            /**
437             * _more_
438             */
439            protected void initializeStormData() {
440                    try {
441                            initParams();
442                            File userDir = getDataContext().getIdv().getObjectStore()
443                                            .getUserDirectory();
444                            String derbyDir = IOUtil.joinDir(userDir, "derbydb");
445                            IOUtil.makeDirRecursive(new File(derbyDir));
446                            System.setProperty("derby.system.home", derbyDir);
447    
448                            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
449                            Class.forName("com.mysql.jdbc.Driver");
450                            if (!initConnection()) {
451                                    setInError(true, true,
452                                                    "Unable to initialize database connection:" + dbUrl);
453                            } else {
454                                    stormInfos = getAllStormInfos();
455                            }
456                    } catch (Exception exc) {
457                            logException("Error initializing STI database: " + dbUrl, exc);
458                    }
459            }
460    
461            /**
462             * _more_
463             * 
464             * @return _more_
465             */
466            public List<StormInfo> getStormInfos() {
467                    List<StormInfo> sInfos = new ArrayList();
468                    sInfos.addAll(stormInfos);
469                    return sInfos;
470            }
471    
472            /**
473             * _more_
474             * 
475             * @param stormInfo
476             *            _more_
477             * @param waysToUse
478             *            _more_
479             * @param observationWay
480             *            _more_
481             * 
482             * @return _more_
483             * 
484             * @throws Exception
485             *             _more_
486             */
487    
488            public StormTrackCollection getTrackCollectionInner(StormInfo stormInfo,
489                            Hashtable<String, Boolean> waysToUse, Way observationWay)
490                            throws Exception {
491                    if (observationWay == null) {
492                            observationWay = DEFAULT_OBSERVATION_WAY;
493                    }
494    
495                    long t1 = System.currentTimeMillis();
496                    StormTrackCollection trackCollection = new StormTrackCollection();
497                    List<Way> forecastWays = getForecastWays(stormInfo);
498    
499                    getWayProbabilityRadius();
500                    for (Way forecastWay : forecastWays) {
501                            if ((waysToUse != null) && (waysToUse.size() > 0)
502                                            && (waysToUse.get(forecastWay.getId()) == null)) {
503                                    continue;
504                            }
505                            List forecastTracks = getForecastTracks(stormInfo, forecastWay);
506                            if (forecastTracks.size() > 0) {
507                                    trackCollection.addTrackList(forecastTracks);
508                            }
509                    }
510                    StormTrack obsTrack = getObservationTrack(stormInfo, observationWay);
511                    // (Way) forecastWays.get(0));
512                    if (obsTrack != null) {
513                            List<StormTrack> tracks = trackCollection.getTracks();
514                            // for (StormTrack stk : tracks) {
515                            // addDistanceError(obsTrack, stk);
516                            // }
517                            long t2 = System.currentTimeMillis();
518                            // System.err.println("time:" + (t2 - t1));
519                            trackCollection.addTrack(obsTrack);
520                    }
521                    return trackCollection;
522            }
523    
524            /**
525             * _more_
526             * 
527             * 
528             * 
529             * @param stormInfo
530             *            _more_
531             * @param forecastWay
532             *            _more_
533             * 
534             * @return _more_
535             * @throws Exception
536             *             _more_
537             */
538            private List<StormTrack> getForecastTracks(StormInfo stormInfo,
539                            Way forecastWay) throws Exception {
540    
541                    List<StormTrack> tracks = new ArrayList<StormTrack>();
542                    List<DateTime> startDates = getForecastTrackStartDates(stormInfo,
543                                    forecastWay);
544    
545                    int nstarts = startDates.size();
546                    for (int i = 0; i < nstarts; i++) {
547                            DateTime dt = (DateTime) startDates.get(i);
548                            StormTrack tk = getForecastTrack(stormInfo, dt, forecastWay);
549                            if (tk != null) {
550                                    int pn = tk.getTrackPoints().size();
551                                    // Why > 1???
552                                    if (pn > 1) {
553                                            tracks.add(tk);
554                                    }
555                            }
556                    }
557                    return tracks;
558    
559            }
560    
561            /**
562             * If d is a missing value return NaN. Else return d
563             * 
564             * @param d
565             *            is checked if not missing return same value
566             * @param name
567             *            _more_
568             * 
569             * @return _more_
570             */
571    
572            public double getValue(double d, String name) {
573                    if ((d == 9999) || (d == 999)) {
574                            return Double.NaN;
575                    }
576    
577                    if (name.equalsIgnoreCase(PARAM_MAXWINDSPEED.getName())) {
578                            if ((d < 0) || (d > 60)) {
579                                    return Double.NaN;
580                            }
581                    } else if (name.equalsIgnoreCase(PARAM_MINPRESSURE.getName())) {
582                            if ((d < 800) || (d > 1050)) {
583                                    return Double.NaN;
584                            }
585                    } else if (name.equalsIgnoreCase(PARAM_RADIUSMODERATEGALE.getName())) {
586                            if ((d < 0) || (d > 900)) {
587                                    return Double.NaN;
588                            }
589                    } else if (name.equalsIgnoreCase(PARAM_RADIUSWHOLEGALE.getName())) {
590                            if ((d < 0) || (d > 500)) {
591                                    return Double.NaN;
592                            }
593                    } else if (name.equalsIgnoreCase(PARAM_MOVESPEED.getName())) {
594                            if ((d < 0) || (d > 55)) {
595                                    return Double.NaN;
596                            }
597                    } else if (name.equalsIgnoreCase(PARAM_MOVEDIRECTION.getName())) {
598                            if ((d < 0) || (d > 360)) {
599                                    return Double.NaN;
600                            }
601                    }
602    
603                    return d;
604            }
605    
606            /**
607             * _more_
608             * 
609             * @param d
610             *            _more_
611             * 
612             * @return _more_
613             */
614            public double getLatLonValue(double d) {
615                    if ((d == 9999) || (d == 999)) {
616                            return Double.NaN;
617                    }
618                    return d;
619            }
620    
621            /**
622             * _more_
623             * 
624             * 
625             * 
626             * @param stormInfo
627             *            _more_
628             * @param sTime
629             *            _more_
630             * @param forecastWay
631             *            _more_
632             * 
633             * @return _more_
634             * @throws Exception
635             *             _more_
636             */
637            private StormTrack getForecastTrack(StormInfo stormInfo, DateTime sTime,
638                            Way forecastWay) throws Exception {
639    
640                    // if(true) return getForecastTrackX(stormInfo, sTime, forecastWay);
641                    String columns = SqlUtil.comma(new String[] { getColYear(),
642                                    COL_TYPHOON_MONTH, COL_TYPHOON_DAY, getColHour(),
643                                    COL_TYPHOON_FHOUR, COL_TYPHOON_LATITUDE, COL_TYPHOON_LONGITUDE,
644                                    COL_TYPHOON_WINDSPEED, COL_TYPHOON_PRESSURE,
645                                    COL_TYPHOON_RADIUSMG, COL_TYPHOON_RADIUSWG,
646                                    COL_TYPHOON_MOVEDIR, COL_TYPHOON_MOVESPEED });
647    
648                    List whereList = new ArrayList();
649                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(stormInfo
650                                    .getStormId())));
651                    whereList.add(SqlUtil.eq(COL_TYPHOON_WAY, SqlUtil.quote(forecastWay
652                                    .getId())));
653    
654                    addDateSelection(sTime, whereList);
655    
656                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
657                                    SqlUtil.makeAnd(whereList));
658                    query = query
659                                    + " order by  "
660                                    + SqlUtil.comma(new String[] { getColYear(), COL_TYPHOON_MONTH,
661                                                    COL_TYPHOON_DAY, getColHour(), COL_TYPHOON_FHOUR });
662                    // System.err.println (query);
663                    Statement statement = evaluate(query);
664                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
665                    ResultSet results;
666                    double radius = 0;
667                    List<StormTrackPoint> pts = new ArrayList();
668    
669                    Real altReal = new Real(RealType.Altitude, 0);
670                    while ((results = iter.getNext()) != null) {
671                            // System.err.println ("row " + cnt);
672                            List<Real> attrs = new ArrayList<Real>();
673                            int col = 1;
674                            int year = results.getInt(col++);
675                            int month = results.getInt(col++);
676                            int day = results.getInt(col++);
677                            int hour = results.getInt(col++);
678                            int fhour = results.getInt(col++);
679    
680                            double latitude = getLatLonValue(results.getDouble(col++));
681                            if ((latitude > 90) || (latitude < -90)) {
682                                    continue;
683                            }
684                            double longitude = getLatLonValue(results.getDouble(col++));
685                            if ((longitude > 360) || (longitude < -180)) {
686                                    continue;
687                            }
688                            attrs.add(PARAM_MAXWINDSPEED.getReal(getValue(results
689                                            .getDouble(col++), PARAM_MAXWINDSPEED.getName())));
690                            attrs.add(PARAM_MINPRESSURE.getReal(getValue(results
691                                            .getDouble(col++), PARAM_MINPRESSURE.getName())));
692                            attrs.add(PARAM_RADIUSMODERATEGALE.getReal(getValue(results
693                                            .getDouble(col++), PARAM_RADIUSMODERATEGALE.getName())));
694                            attrs.add(PARAM_RADIUSWHOLEGALE.getReal(getValue(results
695                                            .getDouble(col++), PARAM_RADIUSWHOLEGALE.getName())));
696                            attrs.add(PARAM_MOVEDIRECTION.getReal(getValue(results
697                                            .getDouble(col++), PARAM_MOVEDIRECTION.getName())));
698                            attrs.add(PARAM_MOVESPEED.getReal(getValue(
699                                            results.getDouble(col++), PARAM_MOVESPEED.getName())));
700                            float[] radiuses = getProbabilityRadius(forecastWay, fhour);
701                            DateTime dttm = getDateTime(year, month, day, hour + fhour);
702                            EarthLocation elt = new EarthLocationLite(new Real(
703                                            RealType.Latitude, latitude), new Real(RealType.Longitude,
704                                            longitude), altReal);
705                            if (true) { // radiuses != null) {
706                                    // radius = fhour * 50.0f / 24.0f;
707                                    addProbabilityRadiusAttrs(attrs, radiuses);
708                            }
709                            StormTrackPoint stp = new StormTrackPoint(elt, dttm, fhour, attrs);
710                            if (!elt.isMissing()) {
711                                    pts.add(stp);
712                            }
713                    }
714    
715                    if (pts.size() == 0) {
716                            // We should never be here
717                            System.err.println("found no track data time=" + sTime
718                                            + " from query:" + SqlUtil.makeAnd(whereList));
719                    }
720                    if (pts.size() > 0) {
721                            return new StormTrack(stormInfo, forecastWay, pts, forecastParams);
722                    } else {
723                            return null;
724                    }
725    
726            }
727    
728            /**
729             * _more_
730             * 
731             * @param way
732             *            _more_
733             * @param forecastHour
734             *            _more_
735             * 
736             * @return _more_
737             */
738            private float[] getProbabilityRadius(Way way, int forecastHour) {
739                    String key = way.getId().toUpperCase() + forecastHour;
740                    // System.out.println("get:" + key + " "
741                    // +(wayfhourToRadius.get(key)!=null));
742                    return wayfhourToRadius.get(key);
743            }
744    
745            /**
746             * _more_
747             * 
748             * @param way
749             *            _more_
750             * @param forecastHour
751             *            _more_
752             * @param radiuses
753             *            _more_
754             */
755            private void putProbabilityRadius(Way way, int forecastHour,
756                            float[] radiuses) {
757                    String key = way.getId().toUpperCase() + forecastHour;
758                    // System.out.println("put:" + key);
759                    wayfhourToRadius.put(key, radiuses);
760            }
761    
762            /**
763             * _more_
764             * 
765             * @param attrs
766             *            _more_
767             * @param radiuses
768             *            _more_
769             * 
770             * @throws Exception
771             *             _more_
772             */
773            private void addProbabilityRadiusAttrs(List<Real> attrs, float[] radiuses)
774                            throws Exception {
775                    if (radiuses != null) {
776                            attrs.add(PARAM_PROBABILITY10RADIUS.getReal(radiuses[0]));
777                            attrs.add(PARAM_PROBABILITY20RADIUS.getReal(radiuses[1]));
778                            attrs.add(PARAM_PROBABILITY30RADIUS.getReal(radiuses[2]));
779                            attrs.add(PARAM_PROBABILITY40RADIUS.getReal(radiuses[3]));
780                            attrs.add(PARAM_PROBABILITY50RADIUS.getReal(radiuses[4]));
781                            attrs.add(PARAM_PROBABILITY60RADIUS.getReal(radiuses[5]));
782                            attrs.add(PARAM_PROBABILITY70RADIUS.getReal(radiuses[6]));
783                            attrs.add(PARAM_PROBABILITY80RADIUS.getReal(radiuses[7]));
784                            attrs.add(PARAM_PROBABILITY90RADIUS.getReal(radiuses[8]));
785                            attrs.add(PARAM_PROBABILITY100RADIUS.getReal(radiuses[9]));
786                            attrs.add(PARAM_DISTANCE_ERROR
787                                            .getReal(getLatLonValue(radiuses[10])));
788                    } else {
789                            attrs.add(PARAM_PROBABILITY10RADIUS.getReal(Float.NaN));
790                            attrs.add(PARAM_PROBABILITY20RADIUS.getReal(Float.NaN));
791                            attrs.add(PARAM_PROBABILITY30RADIUS.getReal(Float.NaN));
792                            attrs.add(PARAM_PROBABILITY40RADIUS.getReal(Float.NaN));
793                            attrs.add(PARAM_PROBABILITY50RADIUS.getReal(Float.NaN));
794                            attrs.add(PARAM_PROBABILITY60RADIUS.getReal(Float.NaN));
795                            attrs.add(PARAM_PROBABILITY70RADIUS.getReal(Float.NaN));
796                            attrs.add(PARAM_PROBABILITY80RADIUS.getReal(Float.NaN));
797                            attrs.add(PARAM_PROBABILITY90RADIUS.getReal(Float.NaN));
798                            attrs.add(PARAM_PROBABILITY100RADIUS.getReal(Float.NaN));
799                            attrs.add(PARAM_DISTANCE_ERROR.getReal(Float.NaN));
800    
801                    }
802            }
803    
804            /**
805             * _more_
806             * 
807             * @param sTime
808             *            _more_
809             * @param whereList
810             *            _more_
811             * 
812             * @throws VisADException
813             *             _more_
814             */
815            private void addDateSelection(DateTime sTime, List whereList)
816                            throws VisADException {
817                    GregorianCalendar cal = new GregorianCalendar(DateUtil.TIMEZONE_GMT);
818                    cal.setTime(ucar.visad.Util.makeDate(sTime));
819                    int yy = cal.get(Calendar.YEAR);
820                    // The MONTH is 0 based. The db month is 1 based
821                    int mm = cal.get(Calendar.MONTH) + 1;
822                    int dd = cal.get(Calendar.DAY_OF_MONTH);
823                    int hh = cal.get(Calendar.HOUR_OF_DAY);
824                    whereList.add(SqlUtil.eq(getColYear(), Integer.toString(yy)));
825                    whereList.add(SqlUtil.eq(COL_TYPHOON_MONTH, Integer.toString(mm)));
826                    whereList.add(SqlUtil.eq(COL_TYPHOON_DAY, Integer.toString(dd)));
827                    whereList.add(SqlUtil.eq(getColHour(), Integer.toString(hh)));
828            }
829    
830            /**
831             * _more_
832             * 
833             * @param year
834             *            _more_
835             * @param month
836             *            _more_
837             * @param day
838             *            _more_
839             * @param hour
840             *            _more_
841             * 
842             * @return _more_
843             * 
844             * @throws Exception
845             *             _more_
846             */
847            private DateTime getDateTime(int year, int month, int day, int hour)
848                            throws Exception {
849                    GregorianCalendar convertCal = new GregorianCalendar(
850                                    DateUtil.TIMEZONE_GMT);
851                    convertCal.clear();
852                    convertCal.set(Calendar.YEAR, year);
853                    // The MONTH is 0 based. The incoming month is 1 based
854                    convertCal.set(Calendar.MONTH, month - 1);
855                    convertCal.set(Calendar.DAY_OF_MONTH, day);
856                    convertCal.set(Calendar.HOUR_OF_DAY, hour);
857                    return new DateTime(convertCal.getTime());
858            }
859    
860            /**
861             * _more_
862             * 
863             * 
864             * 
865             * @param stormInfo
866             *            _more_
867             * @param way
868             *            _more_
869             * 
870             * @return _more_
871             * @throws Exception
872             *             _more_
873             */
874            protected List<DateTime> getForecastTrackStartDates(StormInfo stormInfo,
875                            Way way) throws Exception {
876    
877                    String columns = SqlUtil.comma(new String[] { getColYear(),
878                                    COL_TYPHOON_MONTH, COL_TYPHOON_DAY, getColHour() });
879    
880                    List whereList = new ArrayList();
881                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(stormInfo
882                                    .getStormId())));
883                    whereList.add(SqlUtil.eq(COL_TYPHOON_FHOUR, ZEROHOUR));
884                    whereList.add(SqlUtil.eq(COL_TYPHOON_WAY, SqlUtil.quote(way.getId())));
885    
886                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
887                                    SqlUtil.makeAnd(whereList));
888                    query = query
889                                    + " order by  "
890                                    + SqlUtil.comma(new String[] { getColYear(), COL_TYPHOON_MONTH,
891                                                    COL_TYPHOON_DAY, getColHour() });
892                    // System.err.println (query);
893                    Statement statement = evaluate(query);
894                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
895                    ResultSet results;
896                    List<DateTime> startDates = new ArrayList<DateTime>();
897                    while ((results = iter.getNext()) != null) {
898                            int col = 1;
899                            int year = results.getInt(col++);
900                            int month = results.getInt(col++);
901                            int day = results.getInt(col++);
902                            int hour = results.getInt(col++);
903                            startDates.add(getDateTime(year, month, day, hour));
904                    }
905                    return startDates;
906            }
907    
908            /**
909             * _more_
910             * 
911             * 
912             * @throws Exception
913             *             _more_
914             */
915            protected void getWayProbabilityRadius() throws Exception {
916    
917                    String columns = SqlUtil.comma(new String[] { COL_PROBILITY_WAYNAME,
918                                    COL_PROBILITY_FHOUR, COL_PROBILITY_P10, COL_PROBILITY_P20,
919                                    COL_PROBILITY_P30, COL_PROBILITY_P40, COL_PROBILITY_P50,
920                                    COL_PROBILITY_P60, COL_PROBILITY_P70, COL_PROBILITY_P80,
921                                    COL_PROBILITY_P90, COL_PROBILITY_P100, COL_DISTANCE_ERROR });
922    
923                    List whereList = new ArrayList();
924    
925                    String query = SqlUtil.makeSelect(columns, Misc
926                                    .newList(TABLE_PROBILITY), SqlUtil.makeAnd(whereList));
927                    Statement statement = evaluate(query);
928                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
929                    ResultSet results;
930                    wayfhourToRadius = new HashMap();
931                    while ((results = iter.getNext()) != null) {
932                            float[] wp = new float[11];
933                            int col = 1;
934                            String wayName = results.getString(col++);
935                            int fhour = results.getInt(col++);
936                            wp[0] = results.getFloat(col++);
937                            wp[1] = results.getFloat(col++);
938                            wp[2] = results.getFloat(col++);
939                            wp[3] = results.getFloat(col++);
940                            wp[4] = results.getFloat(col++);
941                            wp[5] = results.getFloat(col++);
942                            wp[6] = results.getFloat(col++);
943                            wp[7] = results.getFloat(col++);
944                            wp[8] = results.getFloat(col++);
945                            wp[9] = results.getFloat(col++);
946                            wp[10] = results.getFloat(col++);
947                            putProbabilityRadius(new Way(wayName), fhour, wp);
948                    }
949            }
950    
951            /**
952             * _more_
953             * 
954             * @param stormInfo
955             *            _more_
956             * @param observationWay
957             *            _more_
958             * 
959             * @return _more_
960             * 
961             * @throws Exception
962             *             _more_
963             */
964            protected StormTrack getObservationTrack(StormInfo stormInfo,
965                            Way observationWay) throws Exception {
966                    addWay(observationWay);
967                    // first get the obs from one specific way
968                    List<StormTrackPoint> obsTrackPoints = getObservationTrackPoints(
969                                    stormInfo, observationWay);
970    
971                    if (obsTrackPoints.size() == 0) {
972                            return null;
973                    }
974    
975                    return new StormTrack(stormInfo, addWay(Way.OBSERVATION),
976                                    obsTrackPoints, obsParams);
977            }
978    
979            /**
980             * _more_
981             * 
982             * @return _more_
983             */
984            public boolean getIsObservationWayChangeable() {
985                    return true;
986            }
987    
988            /**
989             * _more_
990             * 
991             * @return _more_
992             */
993            public Way getDefaultObservationWay() {
994                    return DEFAULT_OBSERVATION_WAY;
995            }
996    
997            /**
998             * _more_
999             * 
1000             * 
1001             * 
1002             * @param stormInfo
1003             *            _more_
1004             * @param wy
1005             *            _more_
1006             * 
1007             * @return _more_
1008             * @throws Exception
1009             *             _more_
1010             */
1011            protected List<StormTrackPoint> getObservationTrackPoints(
1012                            StormInfo stormInfo, Way wy) throws Exception {
1013                    String columns = SqlUtil.comma(new String[] { getColYear(),
1014                                    COL_TYPHOON_MONTH, COL_TYPHOON_DAY, getColHour(),
1015                                    COL_TYPHOON_LATITUDE, COL_TYPHOON_LONGITUDE,
1016                                    COL_TYPHOON_WINDSPEED, COL_TYPHOON_PRESSURE,
1017                                    COL_TYPHOON_RADIUSMG, COL_TYPHOON_RADIUSWG,
1018                                    COL_TYPHOON_MOVEDIR, COL_TYPHOON_MOVESPEED, COL_TYPHOON_WAY });
1019    
1020                    List whereList = new ArrayList();
1021    
1022                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(stormInfo
1023                                    .getStormId())));
1024                    whereList.add(SqlUtil.eq(COL_TYPHOON_FHOUR, ZEROHOUR));
1025                    whereList.add(SqlUtil.eq(COL_TYPHOON_WAY, SqlUtil.quote(wy.getId())));
1026    
1027                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
1028                                    SqlUtil.makeAnd(whereList));
1029                    query = query
1030                                    + " order by  "
1031                                    + SqlUtil.comma(new String[] { getColYear(), COL_TYPHOON_MONTH,
1032                                                    COL_TYPHOON_DAY, getColHour() });
1033                    // System.err.println (query);
1034                    Statement statement = evaluate(query);
1035                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
1036                    ResultSet results;
1037    
1038                    List<StormTrackPoint> obsPts = new ArrayList();
1039                    // Hashtable seenDate = new Hashtable();
1040                    Real altReal = new Real(RealType.Altitude, 0);
1041    
1042                    while ((results = iter.getNext()) != null) {
1043                            List<Real> attrs = new ArrayList();
1044                            int col = 1;
1045                            int year = results.getInt(col++);
1046                            int month = results.getInt(col++);
1047                            int day = results.getInt(col++);
1048                            int hour = results.getInt(col++);
1049                            double latitude = getLatLonValue(results.getDouble(col++));
1050                            if ((latitude > 90) || (latitude < -90)) {
1051                                    continue;
1052                            }
1053                            double longitude = getLatLonValue(results.getDouble(col++));
1054                            if ((longitude > 360) || (longitude < -180)) {
1055                                    continue;
1056                            }
1057                            attrs.add(PARAM_MAXWINDSPEED.getReal(getValue(results
1058                                            .getDouble(col++), PARAM_MAXWINDSPEED.getName())));
1059                            attrs.add(PARAM_MINPRESSURE.getReal(getValue(results
1060                                            .getDouble(col++), PARAM_MINPRESSURE.getName())));
1061                            attrs.add(PARAM_RADIUSMODERATEGALE.getReal(getValue(results
1062                                            .getDouble(col++), PARAM_RADIUSMODERATEGALE.getName())));
1063                            attrs.add(PARAM_RADIUSWHOLEGALE.getReal(getValue(results
1064                                            .getDouble(col++), PARAM_RADIUSWHOLEGALE.getName())));
1065                            attrs.add(PARAM_MOVEDIRECTION.getReal(getValue(results
1066                                            .getDouble(col++), PARAM_MOVEDIRECTION.getName())));
1067                            attrs.add(PARAM_MOVESPEED.getReal(getValue(
1068                                            results.getDouble(col++), PARAM_MOVESPEED.getName())));
1069    
1070                            EarthLocation elt = new EarthLocationLite(new Real(
1071                                            RealType.Latitude, latitude), new Real(RealType.Longitude,
1072                                            longitude), altReal);
1073    
1074                            DateTime date = getDateTime(year, month, day, hour);
1075                            String key = "" + latitude + " " + longitude;
1076                            // if(seenDate.get(date)!=null) {
1077                            // if(!seenDate.get(date).equals(key)) {
1078                            // System.err.println ("seen: " + date + " " + seenDate.get(date) +
1079                            // " != " + key);
1080                            // }
1081                            // continue;
1082                            // }
1083                            // seenDate.put(date,date);
1084                            // seenDate.put(date,key);
1085                            StormTrackPoint stp = new StormTrackPoint(elt, date, 0, attrs);
1086                            obsPts.add(stp);
1087                    }
1088    
1089                    return obsPts;
1090            }
1091    
1092            /**
1093             * _more_
1094             * 
1095             * @param stormInfo
1096             *            _more_
1097             * @param wy
1098             *            _more_
1099             * @param before
1100             *            _more_
1101             * @param after
1102             *            _more_
1103             * @param pts
1104             *            _more_
1105             * 
1106             * @return _more_
1107             * 
1108             * @throws Exception
1109             *             _more_
1110             */
1111            protected List<StormTrackPoint> getObservationTrack(StormInfo stormInfo,
1112                            Way wy, DateTime before, DateTime after, List pts) throws Exception {
1113    
1114                    String columns = SqlUtil.comma(new String[] { getColYear(),
1115                                    COL_TYPHOON_MONTH, COL_TYPHOON_DAY, getColHour(),
1116                                    COL_TYPHOON_LATITUDE, COL_TYPHOON_LONGITUDE,
1117                                    COL_TYPHOON_WINDSPEED, COL_TYPHOON_PRESSURE,
1118                                    COL_TYPHOON_RADIUSMG, COL_TYPHOON_RADIUSWG,
1119                                    COL_TYPHOON_MOVEDIR, COL_TYPHOON_MOVESPEED, COL_TYPHOON_WAY });
1120    
1121                    List whereList = new ArrayList();
1122    
1123                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(stormInfo
1124                                    .getStormId())));
1125                    whereList.add(SqlUtil.eq(COL_TYPHOON_FHOUR, ZEROHOUR));
1126                    whereList.add(SqlUtil.eq(COL_TYPHOON_WAY, SqlUtil.quote(wy.getId())));
1127    
1128                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
1129                                    SqlUtil.makeAnd(whereList));
1130                    query = query
1131                                    + " order by  "
1132                                    + SqlUtil.comma(new String[] { getColYear(), COL_TYPHOON_MONTH,
1133                                                    COL_TYPHOON_DAY, getColHour() });
1134                    // System.err.println (query);
1135                    Statement statement = evaluate(query);
1136                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
1137                    ResultSet results;
1138    
1139                    List<StormTrackPoint> obsPts = new ArrayList();
1140                    List<StormTrackPoint> obsPts1 = new ArrayList();
1141                    List<StormTrackPoint> obsPts2 = new ArrayList();
1142                    Real altReal = new Real(RealType.Altitude, 0);
1143    
1144                    while ((results = iter.getNext()) != null) {
1145                            List<Real> attrs = new ArrayList();
1146                            int col = 1;
1147                            int year = results.getInt(col++);
1148                            int month = results.getInt(col++);
1149                            int day = results.getInt(col++);
1150                            int hour = results.getInt(col++);
1151                            double latitude = getLatLonValue(results.getDouble(col++));
1152                            if ((latitude > 90) || (latitude < -90)) {
1153                                    continue;
1154                            }
1155                            double longitude = getLatLonValue(results.getDouble(col++));
1156                            if ((longitude > 360) || (longitude < -180)) {
1157                                    continue;
1158                            }
1159    
1160                            attrs.add(PARAM_MAXWINDSPEED.getReal(getValue(results
1161                                            .getDouble(col++), PARAM_MAXWINDSPEED.getName())));
1162                            attrs.add(PARAM_MINPRESSURE.getReal(getValue(results
1163                                            .getDouble(col++), PARAM_MINPRESSURE.getName())));
1164                            attrs.add(PARAM_RADIUSMODERATEGALE.getReal(getValue(results
1165                                            .getDouble(col++), PARAM_RADIUSMODERATEGALE.getName())));
1166                            attrs.add(PARAM_RADIUSWHOLEGALE.getReal(getValue(results
1167                                            .getDouble(col++), PARAM_RADIUSWHOLEGALE.getName())));
1168                            attrs.add(PARAM_MOVEDIRECTION.getReal(getValue(results
1169                                            .getDouble(col++), PARAM_MOVEDIRECTION.getName())));
1170                            attrs.add(PARAM_MOVESPEED.getReal(getValue(
1171                                            results.getDouble(col++), PARAM_MOVESPEED.getName())));
1172    
1173                            EarthLocation elt = new EarthLocationLite(new Real(
1174                                            RealType.Latitude, latitude), new Real(RealType.Longitude,
1175                                            longitude), altReal);
1176    
1177                            DateTime date = getDateTime(year, month, day, hour);
1178    
1179                            if (date.getValue() < before.getValue()) {
1180                                    StormTrackPoint stp = new StormTrackPoint(elt, date, 0, attrs);
1181                                    obsPts1.add(stp);
1182                            }
1183    
1184                            if (date.getValue() > after.getValue()) {
1185                                    StormTrackPoint stp = new StormTrackPoint(elt, date, 0, attrs);
1186                                    obsPts2.add(stp);
1187                            }
1188    
1189                    }
1190    
1191                    if (obsPts1.size() > 0) {
1192                            obsPts.addAll(obsPts1);
1193                    }
1194    
1195                    obsPts.addAll(pts);
1196    
1197                    if (obsPts2.size() > 0) {
1198                            obsPts.addAll(obsPts2);
1199                    }
1200    
1201                    return obsPts;
1202    
1203            }
1204    
1205            /**
1206             * _more_
1207             * 
1208             * @param times
1209             *            _more_
1210             * 
1211             * @return _more_
1212             */
1213            protected DateTime getStartTime(List times) {
1214                    int size = times.size();
1215                    DateTime dt = (DateTime) times.get(0);
1216                    int idx = 0;
1217                    double value = dt.getValue();
1218                    for (int i = 1; i < size; i++) {
1219                            dt = (DateTime) times.get(i);
1220                            double dtValue = dt.getValue();
1221                            if (dtValue < value) {
1222                                    value = dtValue;
1223                                    idx = i;
1224                            }
1225                    }
1226                    return (DateTime) times.get(idx);
1227            }
1228    
1229            /**
1230             * _more_
1231             * 
1232             * 
1233             * 
1234             * @return _more_
1235             * @throws Exception
1236             *             _more_
1237             */
1238            private List<StormInfo> getAllStormInfos() throws Exception {
1239                    String columns = SqlUtil.distinct(COL_TYPHOON_STORMID);
1240                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK));
1241                    // System.err.println (query);
1242                    // System.err.println(query);
1243                    SqlUtil.Iterator iter = SqlUtil.getIterator(evaluate(query));
1244                    ResultSet results;
1245                    List<StormInfo> stormInfos = new ArrayList<StormInfo>();
1246                    while ((results = iter.getNext()) != null) {
1247                            String id = results.getString(1);
1248                            DateTime startTime = getStormStartTime(id);
1249                            // System.err.println(id + " " + startTime);
1250                            StormInfo sinfo = new StormInfo(id, startTime);
1251                            stormInfos.add(sinfo);
1252                    }
1253                    return stormInfos;
1254            }
1255    
1256            /**
1257             * _more_
1258             * 
1259             * @param id
1260             *            _more_
1261             * 
1262             * @return _more_
1263             * 
1264             * @throws Exception
1265             *             _more_
1266             */
1267            protected DateTime getStormStartTime(String id) throws Exception {
1268                    String columns = SqlUtil.comma(new String[] { getColYear(),
1269                                    COL_TYPHOON_MONTH, COL_TYPHOON_DAY, getColHour() });
1270    
1271                    List whereList = new ArrayList();
1272                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(id)));
1273                    whereList.add(SqlUtil.eq(COL_TYPHOON_FHOUR, ZEROHOUR));
1274                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
1275                                    SqlUtil.makeAnd(whereList));
1276                    query = query + " order by  " + columns;
1277                    // System.err.println (query);
1278                    Statement statement = evaluate(query);
1279                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
1280                    ResultSet results;
1281                    while ((results = iter.getNext()) != null) {
1282                            int col = 1;
1283                            int year = results.getInt(col++);
1284                            int month = results.getInt(col++);
1285                            int day = results.getInt(col++);
1286                            int hour = results.getInt(col++);
1287                            statement.close();
1288                            // Just get the first one since we sorted the results with the order
1289                            // by
1290                            return getDateTime(year, month, day, hour);
1291                    }
1292                    return null;
1293            }
1294    
1295            /**
1296             * _more_
1297             * 
1298             * 
1299             * 
1300             * @param stormInfo
1301             *            _more_
1302             * 
1303             * @return _more_
1304             * @throws Exception
1305             *             _more_
1306             */
1307            protected List<Way> getForecastWays(StormInfo stormInfo) throws Exception {
1308    
1309                    String columns = SqlUtil.distinct(COL_TYPHOON_WAY);
1310    
1311                    List whereList = new ArrayList();
1312                    whereList.add(SqlUtil.eq(COL_TYPHOON_STORMID, SqlUtil.quote(stormInfo
1313                                    .getStormId())));
1314                    String query = SqlUtil.makeSelect(columns, Misc.newList(TABLE_TRACK),
1315                                    SqlUtil.makeAnd(whereList));
1316                    // System.err.println (query);
1317                    Statement statement = evaluate(query);
1318                    SqlUtil.Iterator iter = SqlUtil.getIterator(statement);
1319                    ResultSet results;
1320    
1321                    List<Way> forecastWays = new ArrayList<Way>();
1322    
1323                    // TODO: How do we handle no data???
1324                    while ((results = iter.getNext()) != null) {
1325                            Way way = new Way(results.getString(1));
1326                            addWay(way);
1327                            forecastWays.add(way);
1328                    }
1329    
1330                    // System.err.println ("ways:" + forecastWays);
1331                    return forecastWays;
1332    
1333            }
1334    
1335            /**
1336             * _more_
1337             * 
1338             * @param sql
1339             *            _more_
1340             * 
1341             * @return _more_
1342             * 
1343             * @throws SQLException
1344             *             _more_
1345             */
1346            private Statement evaluate(String sql) throws SQLException {
1347                    Statement stmt = getConnection().createStatement();
1348                    stmt.execute(sql);
1349                    return stmt;
1350            }
1351    
1352            /**
1353             * _more_
1354             * 
1355             * @return _more_
1356             */
1357            public Connection getConnection() {
1358                    if (connection != null) {
1359                            return connection;
1360                    }
1361                    // String url = getFilePath();
1362                    // Just hard code the jdbc url
1363                    String url = dbUrl;
1364                    // We don't need to do this for derby.
1365                    /*
1366                     * if ((getUserName() == null) || (getUserName().trim().length() == 0))
1367                     * { if (url.indexOf("?") >= 0) { int idx = url.indexOf("?");
1368                     * List<String> args = (List<String>) StringUtil.split(url.substring(idx
1369                     * + 1), "&", true, true); url = url.substring(0, idx); for (String tok
1370                     * : args) { List<String> subtoks = (List<String>) StringUtil.split(tok,
1371                     * "=", true, true); if (subtoks.size() != 2) { continue; } String name
1372                     * = subtoks.get(0); String value = subtoks.get(1); if
1373                     * (name.equals("user")) { setUserName(value); } else if
1374                     * (name.equals("password")) { setPassword(value); } } } }
1375                     */
1376    
1377                    int cnt = 0;
1378                    while (true) {
1379                            String userName = getUserName();
1380                            String password = getPassword();
1381                            if (userName == null) {
1382                                    userName = "";
1383                            }
1384                            if (password == null) {
1385                                    password = "";
1386                            }
1387                            // userName = "jeff";
1388                            // password = "mypassword";
1389                            try {
1390                                    // System.err.println(url);
1391                                    if (useDerby()) {
1392                                            connection = DriverManager.getConnection(url);
1393                                    } else {
1394                                            if ((url.indexOf("user") > 0)
1395                                                            && (url.indexOf("password") > 0)) {
1396                                                    connection = DriverManager.getConnection(url);
1397                                            } else {
1398                                                    connection = DriverManager.getConnection(url, userName,
1399                                                                    password);
1400                                            }
1401                                    }
1402    
1403                                    return connection;
1404                            } catch (Exception sqe) {
1405                                    // System.out.println(sqe);
1406                                    String msg = sqe.toString();
1407                                    if ((msg.indexOf("Access denied") >= 0)
1408                                                    || (msg.indexOf("role \"" + userName
1409                                                                    + "\" does not exist") >= 0)
1410                                                    || (msg.indexOf("user name specified") >= 0)) {
1411                                            String label;
1412                                            if (cnt == 0) {
1413                                                    label = "<html>The database requires a login.<br>Please enter a user name and password:</html>";
1414                                            } else {
1415                                                    label = "<html>Incorrect username/password. Please try again.</html>";
1416                                            }
1417                                            if (!showPasswordDialog("Database Login", label)) {
1418                                                    return null;
1419                                            }
1420                                            cnt++;
1421                                            continue;
1422                                    }
1423                                    throw new BadDataException("Unable to connect to database", sqe);
1424                            }
1425                    }
1426            }
1427    
1428            /**
1429             * _more_
1430             * 
1431             * @return _more_
1432             * 
1433             * @throws Exception
1434             *             _more_
1435             */
1436            private boolean initConnection() throws Exception {
1437                    if (getConnection() == null) {
1438                            return false;
1439                    }
1440    
1441                    try {
1442                            // Create the dummy database
1443                            Connection connection = getConnection();
1444                            Statement stmt = connection.createStatement();
1445                            // Drop the table - ignore any errors
1446                            // SqlUtil.loadSql("drop table " + TABLE_TRACK, stmt, false);
1447    
1448                            if (useDerby()) {
1449                                    // Load in the test data
1450                                    try {
1451                                            stmt.execute("select count(*) from typhoon");
1452                                            System.err.println("Derby DB OK");
1453                                    } catch (Exception exc) {
1454                                            System.err.println("exc;" + exc);
1455                                            System.err.println("Creating test database");
1456                                            String initSql = IOUtil.readContents(
1457                                                            "/ucar/unidata/data/storm/testdb.sql", getClass());
1458    
1459                                            connection.setAutoCommit(false);
1460                                            SqlUtil.loadSql(initSql, stmt, false);
1461                                            connection.commit();
1462                                            connection.setAutoCommit(true);
1463                                    }
1464                            }
1465                    } catch (Exception exc) {
1466                            exc.printStackTrace();
1467                            return false;
1468                    }
1469                    return true;
1470            }
1471    
1472            /**
1473             * _more_
1474             * 
1475             * @param args
1476             *            _more_
1477             * 
1478             * @throws Exception
1479             *             _more_
1480             */
1481            public static void main(String[] args) throws Exception {
1482                    String sid = "0623";
1483                    STIStormDataSource s = null;
1484                    try {
1485                            s = new STIStormDataSource();
1486                    } catch (Exception exc) {
1487                            System.err.println("err:" + exc);
1488                            exc.printStackTrace();
1489                    }
1490                    s.initAfter();
1491                    List sInfoList = s.getStormInfos();
1492                    StormInfo sInfo = (StormInfo) sInfoList.get(0);
1493                    sInfo = s.getStormInfo(sid);
1494                    String sd = sInfo.getStormId();
1495                    StormTrackCollection cls = s.getTrackCollection(sInfo, null, null);
1496                    StormTrack obsTrack = cls.getObsTrack();
1497                    List trackPointList = obsTrack.getTrackPoints();
1498                    List trackPointTime = obsTrack.getTrackTimes();
1499                    List ways = cls.getWayList();
1500                    Map mp = cls.getWayToStartDatesHashMap();
1501                    Map mp1 = cls.getWayToTracksHashMap();
1502    
1503                    System.err.println("test:");
1504    
1505            }
1506    
1507            /**
1508             * Set the DbUrl property.
1509             * 
1510             * @param value
1511             *            The new value for DbUrl
1512             */
1513            public void setDbUrl(String value) {
1514                    dbUrl = value;
1515            }
1516    
1517            /**
1518             * Get the DbUrl property.
1519             * 
1520             * @return The DbUrl
1521             */
1522            public String getDbUrl() {
1523                    return dbUrl;
1524            }
1525    
1526    }