Page 1
Page 2
Travel Time and Delay Study (TTDS) Overview
The purpose of this project is to allow users to collect
travel time data while traveling between two locations. The eVB application
(and supporting web site) then analyze and present the data to help determine
the best time and route to travel between two points to minimize travel time.
This
application could be used by commuters to determine the best time and
route to travel to and from work or couriers to determine when to make
deliveries. As data is gathered for different cities users on business
trips will be able to determine the travel time between two locations
based on data collected by local commuters. With enough data emergency
response workers could use it to determine the fastest route to an accident.
An eVB application on a PocketPC makes gathering
data easy for the user. The application collects data in a SQL Server
2000 CE database on the PocketPC. When connectivity becomes available,
the eVB application can push new records from the local SQL database to
the server.
The TTDS PocketPC application. A commuter has started a
new trip. When they reach their destination they will click "Stop".
I knew I was up late working on this but…
Analysis and presentation of the data (answering the
"when is the best time to leave" question) is done through a supporting
web site or the eVB application. The web pages are optimized for Pocket IE and
can be accessed from any browser. The web page has access to all the data from
all users. Similar analysis (using the same SQL SELECT statement) is also implemented
in the eVB application so users without connectivity can see the results of
the data they have collected.
Data from the local SQL Server database as presented in
the eVB application. From this it looks like 9AM is a good time to make the
trip.This bar chart is useful to the user if he/she does not have connectivity
to the back end web server.Selecting one of the check boxes will cause the query
to consider a subset of the data. The web page is a simple ASP page running
on the web server. This ASP can be accessed by any browser, but is optimized
for PIE. The fundamental difference is that this bar chart considers the data
from all the users that have made this trip. Only the summarized results (in
the form of the web page) need to be sent to the PocketPC client.
The PocketPC is a great platform for collecting and sending
data to a server. Wireless connectivity (via a cell phone) is relatively easy
to achieve and lets us easily connect the eVB application to the back end database
as needed. SQL Server 2000 and SQL Server 2000/CE are crucial components that
make this all possible. The data gathering aspect of this application provided
a couple of challenges that were elegantly solved by eVB with ADOCE 3.1, SQL
Server 2000 and SQL Server 2000 CE. For this application I chose to use RDA
(instead of replication) and to use the Push, Pull and SubmitSQL methods. Help
on the three RDA methods can be found in the excellent "Books Online"
file that ships with SQL Server 2000/CE.
Design Guidelines
- Use the PocketPC to collect and push data.
- Your users may have low bandwidth connections
to SQL Server so minimize the amount of data pulled and the amount of data
stored on the PocketPC.
- Supplement the eVB application with a supporting
web site where it makes sense. Optimize the site for pIE. If the user can
connect to SQL Server over HTTP then they can connect to IIS/ASP pages as
well.
- Support the user when host connectivity is not
available. Remember adding keyboard support after the mouse came along - same
thing.
- I know everything is a variant but "type"
it anyway. Not only will you get IntelliSense but when more data types are
supported you won't have to re-code.
Requirement #1 - Uniquely Save User Trips
The back end SQL Server 2000 database holds all the trip
data from all users. Completed trips are stored in each PocketPC local SQL Server
CE database. When the user gets connectivity to the Internet they use the eVB
application to upload (push) their trip data to the back end SQL Server. The
trips need to be pushed to the server with a unique ID. The RDA "Push"
method requires a key field in the table. SQL Server 2000/CE supports identity
fields but I did not want the user to have to connect each time they were about
to create a new trip. The unique record identifier had to be created on the
PocketPC without connectivity to the back end database. It had to be unique
without any knowledge of other PocketPCs simultaneously creating trip data.
SQL Server 7, 2000 and CE both support a relatively new field type called UniqueIdentifier.
This 16 byte globally unique identifier (GUID) is guaranteed unique regardless
of where it is generated. In eVB it can be treated as a string. This is fundamental
to this application, and I suspect all PocketPC applications creating data.
No Dorothy, we aren't in "CDB-land" anymore.
I set the key field in the Trips table to UniqueIdentifier
and set the field default to the built in function called newid(). I also set
the ROWGUIDCOL property for this field to True. This works on both SQL Server
2000 and SQL Server 2000 CE. NewID() is not documented in the SQL Server 2000
CE Help file but it is in the SQL Server 2000 Help.
When a new record is created (INSERT) the new GUID is automatically
created. The result is that we can create a record on the PocketPC (ANY PocketPC)
and be guaranteed that the key field will be unique when the record is pushed
back to the back end SQL Server 2000 database. When the Trips table was created
on the CE device it was done so with the RDA Pull method. A parameter of the
Pull method is "RDA_TRACKOPTION". This parameter can be set to TRACKINGON
or TRACKINGOFF. By setting it to TRACKINGON we only send new or updated records
to the back end database when we use the push method.
Next Page