II IN T E R N A T IO N A L C O N FE R E N C E ___________
T R A N SPO R T SY ST E M S T E L E M A T IC S T S T '02
P O LITEC H N IK I ŚLĄ SK IEJ 2002 T R A N SP O R T z.45, n r kol. 1570
database, programming tools, information about accidents Piotr W C IS Ł O 1
A T E L E M E T R IC SY ST E M SU PPO R T IN G D A T A B A SE O F IN FO R M A T IO N A B O U T A C C ID E NT S
T h e sy stem ’s purpose is to collect inform ation about road accidents in K atow ice, and as a target, also over the entire Silesian State territory.
T h e inform ation collected will enable creation o f a w orkshop to produce a full situation on the road in respect o f safety. T his will allow determ ining w hat factors participated in the ap pearance of hazard in th e road traffic. It will also enable follow up and planning, designing and perform ance o f road investm ent project taking them from safety point o f view . A lso m onitoring o f road traffic safety im provem ent w ill be possible to evaluate the efficiency o f m easures taken.
SY ST E M T E L E M E T R Y C Z N Y W SPO M A G A JĄ C Y BAZĘ DANYCH IN FO R M A C JI O W Y PAD K A C H
System będzie m iał na celu zbieranie inform acji o w ypadkach drogow ych w K atow icach, a docelow o na terenie całego w ojew ództw a Śląskiego.
Z ebrane inform acje po zw o lą stw orzyć w arsztat, dzięki którem u uzyska się pełny o b raz sytuacji na drogach pod w zględem bezpieczeństw a. Pozw oli to określić, ja k ie czynniki m iały w pływ na pow staw anie zagrożenia w ruchu drogow ym . U m ożliw i ró w n ież kontrolow anie i planow anie, projektow anie i w ykonanie inw estycji drogow ych pod w zględem b ezpieczeństw a ruchu o raz prow adzenie m onitoringu procesu p opraw y brd i ocen efektyw ności stosow anych środków .
1. IN TR O D U C TIO N
T he sy stem ’s purpose is to collect inform ation about road accidents in K atow ice, and as a target, also over the entire Silesian S tate territory.
T he services responsible (Police) w ould co llect inform ation necessary for construction o f a database during accident site visit. A fter preparation o f docum entation in an electronic form , the data about accident w ill be sent to a central database. In order to m ake this system work, the P olice, besides inform ation collected during site visit, i.e. circum stances o f the accident and w eather conditions, should also give a full description o f road that was the theater o f accident. F or this purpose it is possible to use data contained in the electronic system o f road and bridge database with detailed description o f each section (Dz. U. N o 17 item. 225 o f 2000 and Dz. U. 32 item. 393 o f 2000).
T he inform ation collected will enable creation o f a w orkshop to produce a full situation on the road in respect o f safety. T his will allow determ ining w hat factors participated in the appearance o f hazard in the road traffic. It w ill also enable follow up and planning, designing
'R egional C en tre o f Road T raffic, F rancuska 78, 40-507 K atow ice. Poland
34 Piotr W CISŁO and perform ance o f road investm ent project taking them from safety point o f view. A lso m onitoring o f road traffic safety im provem ent w ill be possible to evaluate the efficiency o f m easures taken.
2. PR O G R A M M IN G TO OLS
B orland Delphi
T he database w ill be b u ilt using D elphi - a tool that allow s creating applications (both database and Internet-related) for W indow s. D elphi is one o f the best com pilers with result code optim ization. T he program m er has at his disposal an integrated environm ent consisting o f all tools enabling designing, debugging and testing o f applications. D elphi is a perfect solution everyw here, w here optim um solutions are needed, m ainly related w ith databases. It is provided w ith B D E (B orland D atabase Engine) enabling handling o f databases o f Paradox and dB ase as well as O D BC (Open D atabase C onnectivity) i.e. open com m unication between bases, supporting o ther database form ats available in the W indow s environm ent. F or the purpose o f efficient base m anagem ent, D elphi has SQ L i.e. Structured Query' Language.
D atabase D esktop application enables designing o f databases in m any p o pular form ats.
A SP and PHP
A ctiver S erver Pages is a script program m ing environm ent integrated w ith M icrosoft IIS, to be used for creation o f dynam ic W W W pages and advanced applications using databases, w here the u se r’s interface is Internet search tool.
A SP pages are com posed o f H T M L language elem ents, text and com m ands o f script language. T hey are perform ed entirely on W W W server (so called server-side scripting) processing the script com m ands and sending a ready-m ade page to the search tool, containing b u t “pure” H T M L form at. F or this reason, the entire burden o f processing rem ains w ith the server, w here in addition A SP scripts may com m unicate w ith the databases using A ctiveX m echanism s, as well as perform o ther tasks that require large capacity o r central processing, A SP is provided w ith a full X M L object service, w hich m akes it a perfect tool for rem ote d atabase application service using dynam ic loading.
PH P is a sim ple and efficient tool constituting an interesting alternative for ASP.
S im ilarly as A SP, PH P is program m ing language fo r W W W program m ers and creation o f dynam ic pages, and its code is nested directly in the docum ent. T he official m eaning o f PH P is „PH P: H ypertext P reprocessor” . It is a program m ing language nested in H TM L. Sim ilarly as A SP, the PH P preprocessor carries out the task contained in th e script and return the result in a text form . T his code is perform ed by the W W W server and n o t the cu sto m er’s one.
S election o f script language depends o f W W W server used.
3. SY STEM
As a science, the statistics enables estim ation o f a percentage resu lt o f phenom enon being investigated. S tatistics o f road accidents reflects only the num eric index o f their o ccurrence but says nothing about the causes o f accidents.
F or this reason, a question arises: is it possible to use appropriate prevention m easures to im prove road safety, using only statistics as indication?
No. U sing only statistics as guidance, it is possible to take im proper preventive actions that may bring po o r effects.
H aving a p roper tool that gives a possibility o f an adequate interpretation o f causes for accidents and events that have happened on the roads, it is possible to counteract hazards in a p roper way. T he solution to the problem m ight be brought by presented database o f car accidents.
F or this purpose it w ould be necessary to use the inform ation resources that are already in place as w ell as services that update these data in a continuous m anner. H aving a tool and using a com m only available Internet access, P olice w ould be able to update the database with data concerning o f accidents on the roads that are hitherto prepared on special form s. W hereas data from the road and bridge recording system w ith their detailed description and m arkings could be updated w ith streets that have been theater o f accident.
A fter com pletion o f these data and appropriate tim e as needed with regard to the am ount o f data contained therein, by way o f using SQ L query in the database it is possible to retrieve statistical inform ation about the accidents, b u t also circum stances o f these events.
R esult o f such a query w ould show a reason for accidents happening on a particular road section and w ould indicate places w ith the m ost frequently repeated cause for accidents. Use o f queries gives m any possibilities to obtain a desired result. A ppropriate interpretation o f this result w ould enable use proper preventive m easures and counteract further existence o f hazardous conditions on the selected road sections.
T his m ethod o f m aintaining accident record and retrieval o f result could greatly im prove safety on the roads o f K atow ice City.
T he system basically consists o f three parts:
- D atabase describing the road accidents, nested on a server,
- U ser interface based on W W W pages using internet connections to update database w ith inform ation provided by the police collecting data upon arrival to the accident site, - A pplication fo r generation o f SQL queries w ith all necessary param eters and display
and printing o f required inform ation.
4. D A TA B ASE
T he database will be based upon one o f the database server existing on the m arket such as M icrosoft SQ L Serw er, w ith an additional m echanism o f A SP o r PH P script service, to be contacted by each system user (police departm ents) for introduction o f new data.
T he database w ill contain all inform ation about the accidents that have happened on the roads o f K atow ice City.
Its resources will describe:
- S ender o f data i.e. ID o f police departm ent,
- D ata contained in the system o f records about roads and bridges, inform ation about streets and signals installed w ithin them, lighting etc.,
C ircum stances that have lead to the road accidents i.e. w eather conditions and street condition. T h ese data describe also braking distance o f vehicle and the accid en t’s causes (such as w here the pedestrian cam e from ) etc.,
36 Piotr W CISŁO
- A ccident participants,
- Situation on the road before the accident,
- A ccident effects describe condition o f the vehicle and its dam age (protocol o f inspection), injuries o f pedestrians etc.
5. U SER IN TER FA C E - D A TA TR A N SFER SY STEM
T he operato r’s station would be connected to Internet. Interface o f the inform ation system user w ould be Internet search engine to contact our W W W w eb site and enabling rem ote operation o f a database installed on the server to be contacted by the web site.
U pon loading o f hom epage, the operator has to be logged in to perform any further actions.
As first inform ation, the user shall enter a unique police departm ent identifier (assum ing that the users logged in are authorized to am end the tables he tries to use). T he u se r’s interface will be m ade in such a way that the description o f accident’s circum stances will be selected from a pattern library, to unify the description; such m ethod o f description is presently used by PD in the docum ents describing visits on the accident site and inspection o f vehicle.
D uring introduction o f inform ation, the inform ation about the street being a theater for the event will be also entered from the data m ade available by the server.
Inform ation about road infrastructure w ould be supplem ented in a regular way from the street description system used by the m anager o f roads and bridges. T his system was created because o f an ordinance obliging the m anager o f the roads to keep such detailed records.
Inform ation about the streets w ould be split up betw een police departm ents they are subject to. T his m eans that the police departm ent in question, after entry o f ID, w ould receive for selection only the streets that are assigned to it as a part o f its region. B esides description o f circum stances and place o f accident, the database w ould be supplem ented w ith data describing the accident’s participants, vehicle inspection, description o f w eather conditions, braking distance etc. B y calling the com m and to send the data to the server, the user causes retrieval o f a script w ith param eters describing the data entered. I f they w ere correct, a new record w ould be added to the table, otherw ise the operator w ould be asked to correct or com plete the entered data. All revisions will be reflected in the database and in the clien t’s search engine.
6. A PPLIC A TIO N FOR G EN ER A TIO N OF SQL QU ERIES A N D PR IN TIN G O F D ESIRED IN FO R M A TIO N
T his application was w ritten in such a way that the persons not know ing SQ L language w ould be able to obtain desired inform ation. U sing descriptive nam es o f conditions to be met by the desired inform ation and prom pting the user to enter m anually such data as date and tim e, other conditions will be selected from the selection lists prepared by the program m er and supplem ented w hen necessary. N ext task o f the application w ill be to translate all the requirem ents from user-friendly language to that o f SQL and then displaying the inform ation received from SQ L server on the screen in a form appropriate for further use.
7. SU M M AR Y
T he accident recording system will perform a role o f a database containing inform ation about accidents that have happened on the roads o f K atow ice. W hen im plem ented in this city, it m ay constitute a good testing field for testing and verification o f road safety im provem ent process.
Inform ation contained in the database enable creation o f SQ L queries, giving a possibility to retrieve various types o f inform ation, for exam ple about circum stances leading to the accident. H aving such accurate data about the accidents will m ake easier talking appropriate m easures to counteract and prevent the accidents. U sing this system it w ould be possible to interpret properly the causes o f accidents and to react correspondingly. This should im prove the safety on the roads.
Review er: Prof. R om uald Szopa
