Spatial-Related Traffic Sign Inspection for Inventory Purposes Using Mobile Laser Scanning Data

Spatial-Related Traf?c Sign Inspection for Inventory Purposes Using Mobile Laser Scanning Data Chenglu Wen,Member,IEEE,Jonathan Li,Senior Member,IEEE,Huan Luo,Yongtao Yu,

Zhipeng Cai,Hanyun Wang,Member,IEEE,and Cheng Wang,Member,IEEE

Abstract—This paper presents a spatial-related traf?c sign in-spection process for sign type,position,and placement using mobile laser scanning(MLS)data acquired by a RIEGL VMX-450 system and presents its potential for traf?c sign inventory appli-cations.First,the paper describes an algorithm for traf?c sign detection in complicated road scenes based on the retrore?ectivity properties of traf?c signs in MLS point clouds.Then,a point cloud-to-image registration process is proposed to project the traf?c sign point clouds onto a2-D image plane.Third,based on the extracted traf?c sign points,we propose a traf?c sign position and placement inspection process by creating geospatial relations between the traf?c signs and road environment.For further inventory applications,we acquire several spatial-related inventory measurements.Finally,a traf?c sign recognition process is conducted to assign sign type.With the acquired sign type,posi-tion,and placement data,a spatial-associated sign network is built. Experimental results indicate satisfactory performance of the pro-posed detection,recognition,position,and placement inspection algorithms.The experimental results also prove the potential of MLS data for automatic traf?c sign inventory applications.

Index Terms—Mobile laser scanning,traf?c sign,detection, recognition,road surfaces,inventory and inspection.

I.I NTRODUCTION

T RAFFIC signs are a fundamental and essential element in road transportation systems because,in addition to their importance for traf?c safety,they provide instructions to drivers and other road users.Traf?c sign design speci?cations differ from country to country.However,the categories,graphic standards,and placement of traf?c signs are all legally de?ned

Manuscript received October8,2014;revised February12,2015;accepted March26,2015.Date of publication April20,2015;date of current version December24,2015.This work was supported in part by the National Nat-ural Science Foundation of China under Grants61401382,61371144,and 41471379.The Associate Editor for this paper was J.Zhang.(Corresponding author:Cheng Wang.)

C.Wen,H.Luo,Y.Yu,Z.Cai,and C.Wang are with Fujian Key Laboratory of Sensing and Computing for Smart City,School of Information Science and Engineering,Xiamen University,Xiamen361005,China(e-mail:clwen@xmu. https://www.sodocs.net/doc/c88525755.html,;scholar.luo@https://www.sodocs.net/doc/c88525755.html,;allennessy.yu@https://www.sodocs.net/doc/c88525755.html,;azptc2h@gmail. com;cwang@https://www.sodocs.net/doc/c88525755.html,).

J.Li is with the MoE Key Laboratory of Underwater Acoustic Commu-nication and Marine Information Technology,School of Information Science and Engineering,Xiamen University,Xiamen361005China,and also with the Department of Geography and Environmental Management,Faculty of Environment,University of Waterloo,Waterloo,ON N2L3G1,Canada(e-mail: junli@https://www.sodocs.net/doc/c88525755.html,).

H.Wang is with the School of Electronic Science and Engineering,Na-tional University of Defense Technology,Changsha410073,China(e-mail: why860314@https://www.sodocs.net/doc/c88525755.html,).

Color versions of one or more of the?gures in this paper are available online at https://www.sodocs.net/doc/c88525755.html,.

Digital Object Identi?er10.1109/TITS.2015.2418214as standard[1],[2].Nowadays,the continual expansion of cities creates an increasing demand for the installation of new signs and maintenance of existing signs based on inventory and inspection.Currently,traf?c sign inventory procedures are mainly implemented manually or semi-automatically.These procedures require much time and labor to update the data, which makes it dif?cult to proceed on a regular basis.An ef?cient traf?c sign inventory process is needed to accurately record sign data,such as the number of signs,type,condition and geographic location of each sign,as well as the spatial relations between the signs[3].

Traf?c sign detection and recognition have been used for traf?c sign automatic inventory in recent years[4],[5].By detecting and recognizing the information from traf?c signs and panels,sign detection and recognition mainly deals with information extraction and understanding tasks for further applications,such as traf?c infrastructure surveys,intelligent driving aided systems,etc.[6]–[9].Current sign detection and recognition research is based mainly on sign images and videos. Traf?c signs exhibit ordinary shapes and standard colors;how-ever,the extensive outlook variability of traf?c signs is captured in uncontrolled situations,such as occlusion,drastic weather and illumination conditions,and image distortion and physical variation of the target surface.All these variations lead to tough working circumstances for image/video-based methods. Moreover,the output of the current traf?c sign detection and recognition research mainly focuses on sign type[10],[11],or the geometric distribution(position)of traf?c signs.However, spatial-related data is overlooked.

Sign placement,an important indicator for traf?c sign con-dition evaluation,is relevant to the usability and visibility of traf?c signs,and?nally affects the road safety of the road users. The visibility status,such as the placement and appearance of the traf?c signs,requires special concerns.Fig.1shows some examples of traf?c signs that display a bad appearance or are poorly positioned.Periodic,automatic road surveying for spatial-related traf?c sign inspection must be established to document,not only the traditional traf?c sign inventory data,but also the spatial relations of the road environment associated with traf?c signs(e.g.,road surfaces,road markings, and vegetation).The surveyed data can be used to(1)maintain and reconstruct current sign resources and road infrastruc-ture and(2)support the study of how traf?c sign practices (e.g.,appearance,position and placement)affect road safety. As a type of Mobile Mapping System(MMS),the Mo-bile Laser Scanning(MLS)system collects geospatial data

1524-9050?2015IEEE.Personal use is permitted,but republication/redistribution requires IEEE permission.

See https://www.sodocs.net/doc/c88525755.html,/publications_standards/publications/rights/index.html for more information.

Fig.1.Examples of traf?c signs with bad placement or appearance(collected from Internet).

using calibrated multi-sensors(e.g.,Global Navigation Satellite System/Initial Measurement Unit(GNSS/IMU)integration sys-tem,laser scanners,and high-resolution cameras)mounted on a mobile platform[12].The MLS systems record the time-of-?ight and the re?ected energy of the laser beams from a surface and form the3-D point clouds with high resolution of geometry and intensity information.With the acquisition of highly accurate and dense data,there is a great potential to apply the MLS systems to traf?c sign inventory,especially in regard to the geospatial information achieved for creating the road environment spatial relations associated with traf?c signs. In this paper,we propose a spatial-related traf?c sign inspec-tion process based on vehicle-borne MLS data and speci?cally introduce its application to automatic traf?c sign inventory.The ?owchart of the proposed process is shown in Fig.2.First,to extract the sign area from the road scene,a traf?c sign detection process is proposed for the MLS point clouds.Secondly,a registration process is conducted to project the colored point clouds of the extracted sign area onto a de?ned2-D image plane for further sign recognition.Then,with the advantages of the acquired accurate geo-referenced MLS data,the traf?c sign position and placement inspection process is conducted by modeling the spatial relationship among the traf?c sign and road elements.Finally,sign type is assigned by a support vector machine(SVM)classi?er trained by a mix feature of Histogram of Gradients(HOG)and color descriptor.With the above position and placement data,a spatial-associated sign network is built for a certain sign type.

The rest of the paper is organized as follows.A review of the previous work is given in Section II.The MLS system and the collected data sets are described presented in Section III. The traf?c sign detection process is presented in Section IV. The algorithm for traf?c sign position and placement inspection are detailed in Section V.The sign type recognition for spatial-associated sign network building is described in Section VI.Ex-perimental results and conclusions are presented in Sections VII and VIII,respectively.

II.R ELATED W ORK

A.Studies on Traf?c Sign Detection and Recognition

As an essential component of the intelligent transportation system,traf?c sign recognition(TSR)has developed rapidly in recent years.The TSR methods generally consist of sign detection and sign recognition.Most of the current TSR-related works remain focused on developing robust feature extractors and descriptors,or recognition models.Several public data sets [13],[14]have been used to assess the pros and cons of TSR. Because the data update in these data sets is time consuming and labor intensive,these data sets are appropriate for TSR research,but inappropriate for traf?c sign status inspection. Using color or shape information of the traf?c sign and panel,much effort has been made to extract visual appearance features from the images and videos[15].To achieve a reliable detection performance under different lighting situations,traf?c sign detection works have been conducted based on Y’CBCR [16],HSV[17],and CIECAM97[18]color models.Except for global features,local invariant features,such as points of interest/regions[19],MSER[20],and Hough-like feature [21],have also been used in traf?c sign detection.Moreover, Gonzalez-Jorge et al.[22]applied a3-D laser scanner for traf?c sign detection and re?ectance inspection regarding the highly re?ective radiation on traf?c signs.

Most of the existing traf?c sign detection methods,based on color and shape information,have relatively strict data require-ments.Detection performance highly depends on the environ-mental conditions,such as illumination,occlusion,viewpoint, and weather conditions.Also,shape-related local feature-based methods are usually time consuming and computationally in-tensive for such large volumes of data.In addition,for traf?c sign detection tasks only,almost all the existing detection methods must be conducted in the daytime.

There are many published results related to TSR based on images[23],[24]or videos[25]using local features.Ruta et al.

[26]proposed a sign recognition method based on similarity measurement learning from example pairs.Yuan et al.[27] developed color global and local oriented edge magnitude pattern features for traf?c sign description and applied an SVM for recognition.Except for the feature detectors and descriptors, some machine learning algorithms such as random forest[28] and neural network[29],also have been utilized for sign recognition[30],[31].

B.Studies on Automatic Traf?c Sign Inventory

The literature contains studies on the detection,recognition, and3-D localization of traf?c signs via multi-view images [32].The precision of localization and spatial relations of a road scene are dependent on the reconstruction algorithm based on multi-view images.Recently,image/video and GPS-based methods have been applied to traf?c sign inventory[33]. Image-based MMS has been applied to traf?c-sign related survey and mapping tasks in recent years.Habib et al.[34] applied an MMS to traf?c sign inventory and achieved traf?c sign data,including sign type,height above the pavement, offset from road edge,coordinate location,and size of the sign. However,the image-based MMS survey requires conducted

WEN et al.:SPATIAL-RELATED TRAFFIC SIGN INSPECTION FOR INVENTORY PURPOSES USING MLS DATA

29

Fig.2.Flowchart of the proposed method for traf?c sign type recognition,position and placement

inspection.

Fig.3.Roads and system for MLS data acquisition:(a)trajectory(in blue color)in Xiamen Island for data acquisition,(b)MLS system used.

under good lighting conditions.Also,the relationships between traf?c signs and road environment,such as the angle between the sign plane and road surface,and the angle between the sign plane and lane driving direction,must be analyzed further. Compared to photogrammetry and?eld surveys,an MLS system captures high point-density and accurate3-D point clouds in a short time period[35].MLS systems have been applied to transportation-related surveys[36],for example map-ping and extraction curbstone[37]and road markings[38]. Pu et al.[39]recognized basic road traf?c structures from mobile laser scanning data.Also,terrestrial and static laser scanning systems have been used in simple traf?c sign inven-tory applications[22].However,there is little research work related to systematic and automatic traf?c sign inventory based on MLS systems.

III.MLS S YSTEM AND D ATA A CQUISITION

The traf?c sign MLS data is acquired in the area within Xiamen Island(longitude118?04 04 E,latitude24?26 46 N), a part of the City of Xiamen(Fig.3(a)).A RIEGL VMX-450 system,mounted on a minivan,is used to acquire the MLS data(Fig.3(b)).This system is smoothly integrated with1)two RIEGL VQ-450laser scanners,2)four high-resolution digital cameras,3)a GNSS,4)an IMU,and5)a wheel-mounted dis-tance measurement indicator(DMI).These two laser scanners are installed on both sides of the main frame with an“X”con-?guration pattern and rotate to emit laser beams.The maximum valid range of the laser beams are approximately800m.Two of the four RIEGL CS-6high-resolution(2452by2056pixels) color digital cameras are installed on the left side of the frame and the other two on the right side of the frame.More infor-mation about the MLS system’s speci?cations can be found in[40].

Three surveys were performed with MLS to obtain the data required for this research.The Ring Road that is used for survey is a seaside green-corridor for tourism with a speed limit of 60–70km/h and contains different kinds of traf?c signs.

The Fig.4.Illustration of the proposed traf?c sign detection in MLS point clouds. Ring Belt Road(including a tunnel)that is used for survey is a busier inner-city road,with a speed limit of50–60km/h.The Zhongshan Road that is used for survey is a much busier road, with a speed limit of30–40km/h,and contains many crossings and more dense traf?c signs.The three roads have different speed limits,traf?c?ows,and traf?c sign placements.These roads provide various MLS data of traf?c signs according to different road situations.The Ring Road data set contains about 800million points and has a road section of approximately 20km.The Ring Belt Road data set contains about400million points and has a road section of approximately10km.The Zhongshan Road data set contains about300million points and has a road section of approximately7km.Samples of more than260MLS traf?c sign scenes and500traf?c sign images were collected for further testing.

IV.T RAFFIC S IGN D ETECTION IN MLS D ATA

In our automatic traf?c sign inspection process,a sign detec-tion algorithm is?rstly used to detect the signs on both MLS point clouds and image data.In the detection stage,the traf?c sign target is extracted from the MLS point cloud and projected onto the image region that embodies this traf?c sign.Here,the traf?c sign surface is de?ned as a highly retro-re?ective vertical plane in the MLS data that can be used for traf?c sign detection and extraction.

A.Traf?c Sign Detection Based on MLS Point Clouds

The proposed algorithm for detecting traf?c signs is based on both the linear structure of the traf?c sign pole and the retro-re?ectance of the sign surface in the MLS point clouds. The detection procedure is divided into three main steps:ter-rain points?ltering,linear structured objects detecting,and re?ectance intensity based sign surface?ltering.Fig.4shows an illustration of the proposed detection process.

30IEEE TRANSACTIONS ON INTELLIGENT TRANSPORTATION SYSTEMS,VOL.17,NO.1,JANUARY

2016

Fig.5.Grid representation of MLS point clouds:(a)a point clouds scene, (b)XY horizontal plane,(c)cell point selection.

A terrain?lter is?rstly used to separate off-terrain points

(e.g.,vegetation,small objects,moving objects such as vehi-

cles,etc.)from terrain points.The?ltering process is based on a grid representation of the data at each level.For a MLS

point clouds scene(Fig.5(a)),the?rst step is to specify a

coarse grid in the XY-plane,which is de?ned as the horizontal plane(Fig.5(b)).For each non-empty cell of the grid,we

select a point according to the given percentile which speci?es the percentage of points within a cell that shall be below the

representative cell point(Fig.5(c)).Then,each representative

cell point,together with its neighboring cells’representative points,is used to estimate a local plane.After that,for each

cell,a bounding box with a certain distance range above/below

its estimated plane is de?ned.Those points,which are outside the tolerance range,are marked as“off-terrain”points and

not considered further.The remaining points are assigned to one of the four sub-cells of the next?ner level,and again

the representative cell points are determined.The process is

repeated until the?nest level is reached.Then,a Euclidean clustering algorithm is applied to segment the off-terrain points

into clusters.Following[41],the maximal eigenvalue(λ1),the

second and the minimal eigenvalues(λ2andλ3)of the local covariance matrix of a linear structure satisfy the following

condition:λ1 λ2≈λ3,andλ1/λ2>10.Based on this cri-terion,the points in each cluster are classi?ed as linear and

nonlinear structures.The clusters that contain the number of

linear structural points less than a given threshold(setting as 50)are removed.

The retro-re?ectance properties of the traf?c sign front sur-

faces generally have a strong re?ectance intensity in MLS point clouds.As the?nal step for the proposed sign detection algorithm,we extract the traf?c sign surfaces according to the re?ectance intensity and remove the points with intensi-ties below60000.This step works especially well under bad weather conditions,such as fog,low/strong illumination,and detection tasks at night.Meanwhile,the existing image/video-based traf?c sign detection algorithms have dif?culty working under such conditions.

B.On-Image Sign Detection by Point Clouds Projection Based on the detected sign point clouds,on-image sign area detection is implemented by projecting the3-D points of each traf?c sign onto a2-D image to provide appear-ance information for further sign type recognition.The on-image sign area detection process includes the following

two Fig.6.Illustration of projecting the traf?c sign point clouds onto a2-D image:(a)MLS point clouds and sign area detected,(b)image acquired along with the MLS data,(c)traf?c sign area projected onto a2-D image. steps:1)map the3-D points in the world coordinate system onto the camera coordinate system,2)project the points in the camera coordinate system onto the image plane de?ned by the camera system in VMX-450.

Denote CMCS as the camera coordinate system,BODY as the vehicle coordinate system,ECEF as the global co-ordinate system(WGS84system used in our study),and NED as the north east down coordinate system.Mapping a 3-D point in MLS data in CMCS has three transformation steps as ECEF-to-NED,NED-to-BODY and BODY-to-CMCS. The VMX-450system provides three transformation matrices (C ECEF2NED,C NED2BODY,and C BODY2CMCS)for these transformation steps,respectively.A3-D point P ECEF ac-quired by VMX-450system in ECEF is transformed into the point,P CMCS,in camera coordinate system CMCS by apply-ing the following:

P CMCS=C BODY2CMCS?C NED2BODY

?C ECEF2NED?P ECEF.(1) Then,the point P CMCS is projected onto a2-D image plane by obtaining the correspondent image pixel coordinates according to the3-D points in the CMCS system.Regarding the original image’s radial distortion acquired by the MLS system, a camera calibration model is applied to obtain distorted image coordinates[42].As for tolerating registration error between the MLS point clouds and images,we enlarged the sign area in images with thresholdρ(setting as10).

An example of the on-image sign detection is shown in Fig.6.In this example,an image of a scene is acquired with relatively low illumination,far acquiring distance,and large viewpoint.Under these conditions,the image-based detection methods have dif?culty performing;whereas the proposed method is able to correctly detect the traf?c sign in the image. V.T RAFFIC S IGN P OSITION AND P LACEMENT I NSPECTION The accurate geo-spatial relations built for the traf?c signs and the associated road elements is the unique characteristic of the proposed traf?c sign inspection process based on MLS data.Here,we present the automatic inspection of position and placement of traf?c signs as examples.With the MLS data,the position of every point of the traf?c sign and associated road surface is accurate within millimeters.Suf?cient measurements can be further acquired according to speci?c requirements.

WEN et al.:SPATIAL-RELATED TRAFFIC SIGN INSPECTION FOR INVENTORY PURPOSES USING MLS DATA

31

Fig.7.Illustration of road surface extraction:(a)original point cloud, (b)road surface extracted(shown in red color).

A.Road Surface and Boundary Extraction

Before evaluating the aforementioned position and place-ment parameters,the road boundaries in MLS point clouds are?rstly located.In our previous work[40],we developed a curb-based method for extracting road surface points from MLS point clouds.First,by using the vehicle’s trajectory data,which are recorded by the onboard navigation systems,a raw point cloud is vertically partitioned into a set of data segments with a length of approximately3m perpendicularly to the direction of travel.Then,each data segment is sampled to generate a vertical pro?le perpendicularly to the trajectory.Next,the generated pro?les are further gridded to ascertain principal points from each of the grids by using a layering approach.The ascertained principal points within each pro?le form a pseudo scan line for the detection of curb points.Afterwards,curb points are determined by ascertaining the?rst two principal points located on opposite sides of the trajectory with speci?c elevation gradient constraints.Finally,the extracted curb points from all pro?les are?tted to form curb-lines,which represent the boundaries of the road and are used to separate road surface and roadside points.

In this paper,we use this curb-based method[40]to extract road boundaries and separate the road surface points from roadside points in the point clouds.In Section IV-A,ground points are segmented from the entire point cloud.Since the segmented ground points contain both road surface points and roadside points,the segmented ground points are used to extract road boundaries and segment road surface points from roadside ground points.An example of the road surface extraction is shown in Fig.7.

B.Traf?c Sign Positioning Parameter Measurement

The position and type of traf?c sign information can be embedded into many intelligent transportation related applica-tions.In this paper,we de?ne a traf?c sign’s position as the coordinates of the centroid of the bottom ring of the traf?c sign pole.By such a de?nition,the position,instead of a real point on the traf?c sign,is actually a?oating point at the bottom of a traf?c sign.

To compute the position of a traf?c sign using MLS point clouds,?rst,the point,p,with the lowest elevation(denoted as h p)on the traf?c sign pole is determined(Fig.8(a)).Then, a horizontal pro?le with a thickness size,s t(2cm used),is generated by including the points with elevations within the range h p to h p+s t on the traf?c sign pole.Next,a horizontal circle C is?tted from the points within the horizontal pro?le (Fig.8(b)).Finally,the x and y coordinates of the traf?c

sign Fig.8.Illustration of traf?c sign position calculation:(a)lowest-elevation point selection and horizontal pro?le generation,(b)horizontal pro?le?tting and circle center

determination.

Fig.9.Illustration of traf?c sign placement calculation.

position are assigned,respectively,to the x and y coordinates of the center of the?tted circle C,and the z coordinate is assigned to the z coordinate of point p.

C.Traf?c Sign Placement Parameters Measurement

In this paper,the following parameters are measured for evaluating traf?c sign placement:1)height of the traf?c sign above the ground(h t),2)distance of the traf?c sign from the road boundary(d t),3)orientation of the traf?c sign with respect to the road direction(αd),4)inclination of the traf?c sign with respect to the traf?c sign board orientation(αt), 5)inclination of the traf?c sign with respect to the traf?c sign board pro?le(αp),and6)planarity of the traf?c sign.

As shown in Fig.9,h t is de?ned as the height of the lowest point on the traf?c sign board(point B)over the ground. The horizontal distance,d t,between the point with the short-est horizontal distance to the road on the traf?c sign board (point A)and the road boundary point closest to the traf?c sign in the horizontal plane(point C)is de?ned as:

d t=

(x A?x C)2+(y A?y C)2(2) where x A and x c are the x coordinates of points A and C, respectively,and v A and v c are the y coordinates of points A and C,respectively.The horizontal angle,αd,included between

32IEEE TRANSACTIONS ON INTELLIGENT TRANSPORTATION SYSTEMS,VOL.17,NO.1,JANUARY2016 the tangent vector of point C(v c)and the normal vector of the

traf?c sign board(n t)is de?ned as:

a d=arccos(v x c n x t+v y c n y t)(3)

where v x c and n x t are the x components of v c and n t,respec-

tively,and v y c and n y t are the y components of v c and n t,

respectively.The angle included,αt,between the traf?c sign

pole’s distribution direction(n p)and the vertical direction with

respect to the traf?c sign board’s orientation is de?ned as:

a t=arcsin(n z t)(4)

where n z t is the z component of n t.The angle included,αp,

between the traf?c sign pole’s distribution direction(n p)and

the vertical direction with respect to the traf?c sign board’s

pro?le is de?ned as:

a p=arcsin

n z p

(5)

where n z p is the z component of n p;the traf?c sign’s planarity is measured by the standard deviation of the laser points on the traf?c sign board.

The?tted curb-lines and segmented roadside ground points are used to compute the above parameters.To measure h t, the points on the traf?c sign board are?rst projected onto a horizontal plane to calculate a horizontal bounding box for the traf?c sign board.Then,the ground points within the bounding box are selected to?t a ground plane.Finally,h t is assigned as the height of the lowest point,B,on the traf?c sign board over the ground plane.

To measure d t,?rst,the corner point A on the traf?c sign board,whose vertical line segment dropped from this corner point has the shortest distance to the curb-line,is determined. For a round-shaped traf?c sign,the bounding rectangle of the traf?c sign board is used for the determination of point A.Then, the corresponding curb-line,point C,with the shortest distance to the traf?c sign is selected.Finally,d t is assigned as the horizontal distance between points A and C.To measureαd,?rst,the points on the traf?c sign board are used to construct a covariance matrix.After eigenvalue decomposition on the covariance matrix,the normal vector,n t,of the traf?c sign board is obtained.Then,the tangent vector,v c of point C is calculated using the?tted curb-line.Finally,αd is assigned as the horizontal angle included between vectors n t and v c.

To measureαt,andαp,?rst,the points on the traf?c sign pole are used to construct a covariance matrix.After eigenvalue decomposition on the covariance matrix,the distribution direc-tion,n p,of the traf?c sign pole is obtained.Then,αt is assigned as the angle included between n p and the vertical direction with respect to n t.αp is assigned as the angle included between n p and the vertical direction with respect to the perpendicular direction of n t.To measure the planarity of the traf?c sign,?rst, the points on the traf?c sign board are?tted to form a plane. Then,the planarity is assigned as the standard deviation of these points with respect to the?tted plane.

With above traf?c sign placement parameters measured,the abnormality and usability of the traf?c signs can be evaluated. For example,if the value ofαd is too large,the orientation of the traf?c sign is bad for conducting traf?c activities.If the

value Fig.10.Illustration of the spatial-associated traf?c sign network.

ofαt is too large,the traf?c sign is not installed in an upright position.These parameters in?uence the usability of the traf?c signs and can help to accurately evaluate sign conditions.

VI.S IGN T YPE R ECOGNITION FOR S PATIAL-A SSOCIATED

S IGN N ETWORK B UILDING

In automatic traf?c sign inventory applications,sign type is usually given by a TSR process.The recognition stage labels bounding?elds according to the enclosed traf?c sign and resembles the sign type classi?cation problem.In our method, the traf?c sign type is obtained by an image-based TSR process using the detection results from the on-image sign detection by point clouds projection.Two types of features are used to represent the visual characteristics of a traf?c sign.Following [43],a concatenation of hue histogram with the scale-invariant feature transform(SIFT)descriptor,the HueSIFT,is extracted as color descriptor.The HOG feature[44]is used as a local descriptor of a traf?c sign.The SVM classi?er is adapted as the recognition model.Since the TSR work is not the main focus of our study,we demonstrate only the sign type recognition process in our method.Also,there is much work about TSR that can be further applied to our traf?c sign inspection process. Besides giving the sign type,the uniqueness of our inspection process is that we give accurate spatial coordinates(position) and placement for each recognized traf?c sign and also build accurate geo-spatial relations between the recognized traf?c signs(same or different sign types).With the achieved position and placement information for a traf?c sign,a spatial-associated network for traf?c signs of the same type can be built(Fig.10). The node of the network represents the recognized traf?c sign; nodes with a certain type of attribute are associated in the network by position attributes.

VII.E XPERIMENTS AND D ISCUSSIONS

A.Traf?c Sign Detection on MLS Point Clouds and

Image Data

We tested the detection accuracy on the data sets selected from the survey conducted on June2,2014(Ring Road and Ring Belt Road)and February3,2015(Zhongshan Road).To test the detection algorithm on MLS data,264point cloud scenes were used.To test the detection algorithm on images, 327image samples were used.Some examples of traf?c signs detected by the proposed algorithm are shown in Fig.11.The images are acquired while the MMS is driven on the road,the images possess varied viewpoints(some of which are large).

WEN et al.:SPATIAL-RELATED TRAFFIC SIGN INSPECTION FOR INVENTORY PURPOSES USING MLS DATA

33

Fig.11.Examples of traf?c signs detected by the proposed detection algorithm.

TABLE I

T HE Q UANTITATIVE A SSESSMENT OF THE P ROPOSED

T RAFFIC S IGN D ETECTION A

LGORITHM

1)Quantitative Assessment of the Proposed Detection Al-gorithm:For quantitative assessment purposes,we compared the extracted traf?c signs with the manually labeled reference data,as well as a detection method based on MLS point clouds presented in [45].Similar to the evaluation criteria used in [26],TP (“correctly detected”)represents the percentage of the correctly detected testing samples in the reference data.“Partially detected”represents the percentage of the testing samples that are partially detected.FN (“missed”)represents the percentage of the undetected testing samples,and FP (“false alarm”)represents the percentage of the falsely detected non-traf?c signs.Precision,recall,and F 1measure values are used to evaluate the accuracy of the detection algorithm as follows:

Precision =

T P T P +F P (6)Recall =

T P

T P +F N

(7)F 1measure =

2?T P

2?T P +F N +F P .(8)

All these three values were computed for the proposed de-tection algorithm on MLS data,the detection algorithm on 2-D images,and method in [45](see Table I).

As seen in Table I,a precision value of 91.63%is obtained for the detection algorithm on MLS data.Out of 264traf?c signs,241are correctly detected using point cloud data.Sixteen samples are missed by the detection algorithm.The two reasons for the missed traf?c signs are 1)the acquisition of incomplete point clouds and 2)the acquisition of non-re?ective side signs.For example,point clouds of some traf?c signs are acquired from the opposite direction of the lane,and the back sides of the traf?c signs (without re?ective material)are scanned.

Our

Fig.12.Examples of traf?c sign detected in challenging scenarios.(a)strong il-lumination and large viewpoint,(b)partial occlusions,(c)cluttered background.

algorithm fails to detect these traf?c signs.There are sixteen partially detected traf?c signs,whose point clouds are heavily occluded by vegetation or other objects;thus,these traf?c signs are impossible to detect by the image-based method.Twenty two samples,mostly advertising boards with similar linear structures and re?ectance intensities,are detected as false positives.For the same data,method in [45]achieves a precision and recall of 91.92%and 90.53%,respectively.Twenty ?ve traf?c signs are missed caused by severe occlusions of trees.In addition,21false alarms are generated because of high similarities of other objects to the traf?c signs in the https://www.sodocs.net/doc/c88525755.html,pared to method in [45],our method achieves closed precision,and higher recall and F 1measure values.

For one MLS scene,several images were obtained with different viewpoints and distances while our RIEGL VMX-450system (with 4color digital cameras)was driven on the road to collect both the point clouds and image data.Out of 501image samples,464are correctly detected when testing the proposed on-image detection algorithm.With the projection process,the precision rate reaches 92.61%.Because the point clouds acquired for a traf?c sign are too sparse and the size of a traf?c sign in an image is too small,the on-image detection algorithm misses 37samples.Thirty six samples,detected as false positives,are mostly the false positives brought about by the previous detection results.Also,the calibration error between the laser scanners and cameras in MMS makes multi-targets coexist while projecting point clouds onto an image,and ?nally causes false positives.

2)Traf?c Sign Detection in Challenging Scenarios:Fig.12shows some examples of the detection results related to the challenging scenarios of strong illumination and large viewpoint,partial occlusions,and cluttered background.The green blocks are the manually labeled detection results;the red blocks are our detection results.

Traf?c sign surfaces are very distinctive,with high retro-re?ectance intensity,in MLS data.Our detection process can be performed under adverse weather conditions,such as fog,rain,and strong illumination (Fig.12(a)),as well as at night time.Unlike images,the MLS data contain full 3-D information,and there are no viewpoint and scale problems.Especially,because laser beams can travel through vegetation of certain densities,MLS data can detect traf?c signs that are partially occluded by vegetation (Fig.12(b)).For urban areas,traf?c sign data are usually acquired with cluttered backgrounds,including vehicles,pedestrians,etc.Since a geo-spatial relation is used to separate traf?c signs from other objects,our detection algorithm works well in cluttered backgrounds (Fig.12(c)).

34IEEE TRANSACTIONS ON INTELLIGENT TRANSPORTATION SYSTEMS,VOL.17,NO.1,JANUARY

2016

Fig.13.Correct detection rates in challenging scenarios.

TABLE II

R ECOGNITION R ESULTS OF T RAFFIC S IGN T

YPES

To quantitatively validate the performance of the proposed detection method in challenging scenarios,we present two histograms in Fig.13based on the detection results in Table I.The viewpoint degree histogram gives the detection correct rate according to different viewpoints of images.Here,we can calculate the viewpoint value for each image by the pose information of the MMS and the normal vector of the traf?c sign board achieved in Section V-C.The occlusion percentage histogram gives the detection correct rate according to different occluded percentage of the signs.Here,we did not present the quantitative validation result for cluttered background since it is dif?cult to specify the degree of the background being cluttered.The results indicate that our detection method is capable of correctly detecting certain samples with large viewpoint and high percentage of occlusion (even over 60?viewpoint and over 50%occlusion).B.Sign Type Recognition

To evaluate the sign type recognition performance,2844traf?c sign images of eight classes,picked from data set [13],were used as training samples.327traf?c sign images of eight classes,correspondently selected from the 501sign images (total images in Table I),were used as testing sam-ples.The 31dimensions of HOG features were extracted.For HueSIFT color descriptor extracted,the lengths are 128bins for SIFT,1-D histogram of 37bins,and 2-D descriptor of 121bins in total.Two recognition models,SVM and Random Forest (RandFor)[46],were compared.The following three feature and model combinations were compared for sign type recognition:1)HOG+HueSIFT+SVM,2)HoG+RandFor,and 3)HoG+HueSIFT+RandFor.The experimental result is given by the precision value,i.e.,from among the entire number of testing samples,the percentage of the samples that are correctly recognized.

As shown in Table II,a precision value of 96.32%is obtained for the proposed feature and model combination (HOG+HueSIFT+SVM)when testing our image data.A pre-

cision value of 94.17%is obtained for the HoG+RandFor combination,and a precision value of 93.87%is obtained for the HoG+HueSIFT+RandFor combination.C.Traf?c Sign Position Inspection Evaluation

To quantitatively evaluate the accuracy of the proposed algo-rithm in inspecting traf?c sign positions,a Leica total station TS 15i-1and a Leica RTK GS15were used to collect on-site measurement data as the ground truth.It is dif?cult to measure the coordinates of the centroid of the traf?c sign pole’s bottom ring.Therefore,in practice,for computing the real position of each traf?c sign,we measured the coordinates of three points,which were located 1)on the same horizontal plane,2)around the bottom surface of the traf?c sign pole,and 3)on the bottom surface of the traf?c sign pole.Based on the three measured points,the Leica total station easily inferred the arc center of these three points.The coordinates of the inferred arc center were regarded as the ground truth for depicting the position of the traf?c sign.In this paper,we selected a total number of 50traf?c signs of different shapes and statuses for evaluating the performance of the proposed algorithm.For illustration,10of the 50typical traf?c signs were selected.The measured ground truth data are listed in the left column of Table III.Correspondingly,the position information was also computed by our proposed algorithm using the MLS point cloud data.The experimental results for traf?c sign positions (all in the WGS 84coordinate system)are detailed in the middle column of Table III.

To assess the accuracy of the experimental results with respect to the ground truth,the biases between the two groups of coordinates were analyzed,as shown in the right col-umn of Table III.Our proposed method achieved biases of ±0.231m,±0.287m,and ±0.442m in the x,y ,and z direc-tions,respectively,on these 10traf?c signs.On a whole,our proposed method attained biases of ±0.245m,±0.292m,and ±0.449m in the x,y ,and z directions,respectively,on the 50selected traf?c signs.The proposed method obtained the best accuracy in the x direction;whereas the worst accuracy occurred in the z direction.The reason behind this phenomenon is that a part of the bottom of the traf?c sign pole was cut off when removing the ground points,thereby resulting in great biases in the elevations.With the acquisition of spatial-related data,our proposed inspection method,using MLS point clouds,provides a promising means for inspecting traf?c sign positions and achieves decimeter-level positioning accuracy.It can be noticed that no control point is used in the test,and the accuracy of the positioning parameter measured will be further improved when control points are considered during the data acquisition.D.Traf?c Sign Placement Inspection Evaluation

To quantitatively assess the accuracy of the proposed al-gorithm for measuring traf?c sign placement parameters,we used a Leica total station TS 15i-1,which provides millimeter level measurements to collect on-site measurement data as the ground truth.A total of 50traf?c signs were measured by the total station unit and used as reference samples.Because it is

WEN et al.:SPATIAL-RELATED TRAFFIC SIGN INSPECTION FOR INVENTORY PURPOSES USING MLS DATA 35

TABLE III

G ROUND T RUTH AND P OSITION M EASUREMENT R

ESULTS

TABLE IV

G ROUND T RUTH AND P LACEMENT P ARAMETER C OMPUTING R ESULTS AND A

CCURACIES

very dif?cult to measure orientation angles,(αd ),in practice,we collected only 1)the ground truth for the heights of traf?c signs,(h t ),2)distances from the road boundary (d t ),and 3)tilt angles (αt and αp ).For illustration,10of the 50typical traf?c signs were selected.The on-site measurement results are listed in the left section of Table III.Correspondingly,using the collected point cloud data of these traf?c signs,the above parameters were also calculated by our proposed algorithm,details of the results are shown in the right section of Table IV.In Table IV,negative values for d t indicate that the boards of these traf?c signs hang above the road surface and inside the curbs.Through statistics and analysis,the proposed algorithm achieved an accuracy of ±0.063m,±0.076m,±52 ,and ±37 for calculating h t ,d t ,αt ,and αp ,respectively,on these 10traf?c signs.On a whole,the proposed algorithm attained an accuracy of ±0.07,±0.08,±54 ,and ±42 for calculating h t ,d t ,αt ,and αp ,respectively,on the 50selected traf?c signs.Therefore,toward an accurate evaluation of the abnormalities and usability of traf?c signs,our proposed algorithm is very promising and highly accurate for measuring traf?c sign pa-rameters.Similarly,no control point is used in the test,and the

accuracy of the placement parameters measured will be further improved when control points are considered during the data acquisition.

VIII.C ONCLUSION

This paper presented a spatial-related traf?c sign inspection process for traf?c sign inventory and management based on MLS data collected by a commercial MLS system,RIEGL VMX-450.The work demonstrated the potential for automated inspection of traf?c sign boards and determination of spatial relations between traf?c signs and the road environment using the 3-D MLS point clouds.Based on the high retro-re?ective properties of traf?c sign boards in MLS data,the proposed method works quite well in automated extraction of traf?c sign boards from the laser scanned urban road scenes.From the surface points of the detected traf?c sign boards,the developed registration algorithm projects those traf?c sign board points onto a 2-D image plane for further image-based traf?c sign recognition.Based on the detected traf?c sign area in a 2-D image,a sign type recognition was conducted.

36IEEE TRANSACTIONS ON INTELLIGENT TRANSPORTATION SYSTEMS,VOL.17,NO.1,JANUARY2016

With the geo-spatial data acquired for the traf?c sign and the connected road,inventory measurements were carried out. Taking the position and placement inspection as examples,our algorithm achieved several accurate inventory measurements, such as position,pose measurements,traf?c sign planarity, etc.The experimental results demonstrated that the algorithms achieved detection precision of91.63%and92.61%on MLS point clouds and images,respectively;achieved precision of 96.32%for traf?c sign type recognition.Moreover,with the achievement of sign type,position,and placement data,a spatial-associated sign network was built for a certain sign type and can be used for further ITS applications.

R EFERENCES

[1]Manual on Uniform Traf?c Control Devices for Streets and Highways,

U.S.Dept.Transp.Federal Highway Admin.,Washington,DC,USA, 2009Edition.

[2]Fixed,Vertical Road Traf?c Signs—Part1:Fixed Signs,EN12899-

1:2007,May2008.

[3]“Maintenance of signs and sign supports:A guide for local highway

and street maintenance personnel,”U.S.Dept.Transp.Federal Highway Admin.,Washington,DC,USA,Jan.2010.

[4]S.Maldonado-Bascon,https://www.sodocs.net/doc/c88525755.html,fuente-Arroyo,P.Siegmann,H.Gomez-

Moreno,and F.J.Acevedo-Rodriguez,“Traf?c sign recognition sys-tem for inventory purposes,”in Proc.IEEE Intell.Veh.Symp.,2008, pp.590–595.

[5]L.Hazelhoff,I.M.Creusen,and P.H.N.de With,“Robust detection,clas-

si?cation and positioning of traf?c signs from street-level panoramic im-ages for inventory purposes,”in Proc.IEEE WACV,2012,pp.313–320. [6]H.Gómez-Moreno,S.Maldonado-Bascón,P.Gil-Jiménez,and

https://www.sodocs.net/doc/c88525755.html,fuente-Arroyo,“Goal evaluation of segmentation algorithms for traf?c sign recognition,”IEEE Trans.Intell.Transp.Syst.,vol.11, no.4,pp.917–930,Dec.2010.

[7]á.Gonzalez et al.,“Automatic traf?c signs and panels inspection system

using computer vision,”IEEE Trans.Intell.Transp.Syst.,vol.12,no.2, pp.485–499,Jun.2011.

[8]H.Fleyeh and E.Davami,“Eigen-based traf?c sign recognition,”IET

Intell.Transp.Syst.,vol.5,no.3,pp.190–196,Sep.2011.

[9]K.Lu,Z.Ding,and S.Ge,“Sparse-representation-based graph embedding

for traf?c sign recognition,”IEEE Trans.Intell.Transp.Syst.,vol.13, no.4,pp.1515–1524,Dec.2012.

[10]F.Zaklouta and B.Stanciulescu,“Real-time traf?c-sign recognition us-

ing tree classi?ers,”IEEE Trans.Intell.Transp.Syst.,vol.13,no.4, pp.1507–1514,Dec.2012.

[11]á.González,L.M.Bergasa,and J.J.Yebes,“Text detection and recogni-

tion on traf?c panels from street-level imagery using visual appearance,”

IEEE Trans.Intell.Transp.Syst.,vol.15,no.1,pp.228–238,Feb.2014. [12]V.Tao and J.Li,Advances in Mobile Mapping Technology.London,

U.K.:Taylor&Francis,2007.

[13]J.Stallkamp,M.Schlipsing,J.Salmen,and C.Igel,“The German traf?c

sign recognition benchmark:A multi-class classi?cation competition,”in Proc.Int.Joint Conf.Neural Netw.,2011,pp.1453–1460.

[14]A.M?gelmose,M.M.Trivedi,and T.B.Moeslund,“Vision based traf?c

sign detection and analysis for intelligent driver assistance systems:Per-spectives and survey,”IEEE Trans.Intell.Transp.Syst.,vol.13,no.4, pp.1484–1497,Dec.2012.

[15]S.?Segvi′c,K.Brki′c,Z.Kalafati′c,and A.Pinz,“Exploiting temporal and

spatial constraints in traf?c sign detection from a moving vehicle,”Mach.

Vis.Appl.,vol.25,no.3,pp.649–665,Apr.2011.

[16]M.S.Prieto and A.R.Allen,“Using self-organizing maps in the detec-

tion and recognition of road signs,”Image https://www.sodocs.net/doc/c88525755.html,put.,vol.27,no.6, pp.673–683,May2009.

[17]S.Maldonado Bascón,J.Acevedo Rodríguez,https://www.sodocs.net/doc/c88525755.html,fuente Arroyo,

A.Fernández Caballero,and F.López-Ferreras,“An optimization

on pictogram identi?cation for the road-sign recognition task using SVMs,”Comput.Vis.Image Understand.,vol.114,no.3,pp.373–383, Mar.2010.

[18]X.W.Gao,L.Podladchikova, D.Shaposhnikov,K.Hong,and

N.Shevtsova,“Recognition of traf?c signs based on their color and shape features extracted using human vision models,”https://www.sodocs.net/doc/c88525755.html,mun.Image Represent.,vol.17,no.4,pp.675–685,Aug.2006.[19]S.Salti,A.Petrelli,F.Tombari,N.Fioraio,and L.D.Stefano,“Traf?c

sign detection via interest region extraction,”Pattern Recognit.,vol.48, no.4,pp.1039–1049,Apr.2015.

[20]J.Greenhalgh and M.Mirmehdi,“Real-time detection and recognition

of road traf?c signs,”IEEE Trans.Intell.Transp.Syst.,vol.13,no.4, pp.1498–1506,Dec.2012.

[21]S.Houben,“A single target voting scheme for traf?c sign detection,”in

Proc.IEEE Intell.Veh.Symp.,Jun.2011,pp.124–129.

[22]H.González-Jorge,B.Riveiro,J.Armesto,and P.Arias,“Geometric eval-

uation of road signs using radiometric information from laser scanning data,”in Proc.2th Int.Conf.ISARC,2011,pp.1007–1012.

[23]A.Ihara,H.Fujiyoshi,M.Takaki,H.Kumon,and Y.Tamatsu,“Im-

provement in the accuracy of matching by different feature subspaces in traf?c sign recognition,”IEEJ Trans.Electron.Inf.Syst.,vol.129,no.5, pp.893–900,2009.

[24]M.Takaki and H.Fujiyoshi,“Traf?c sign recognition using SIFT fea-

tures,”IEEJ Trans.Electron.Inf.Syst.,vol.129,no.5,pp.824–831, 2009.

[25]A.Ruta,Y.Li,and X.Liu,“Real-time traf?c sign recognition from video

by class-speci?c discriminative features,”Pattern Recognit.,vol.43, no.1,pp.416–430,Jan.2010.

[26]A.Ruta,Y.Li,and X.Liu,“Robust class similarity measure for traf-

?c sign recognition,”IEEE Trans.Intell.Transp.Syst.,vol.11,no.4, pp.846–855,Dec.2010.

[27]X.Yuan,X.Hao,H.Chen,and X.Wei,“Robust traf?c sign recog-

nition based on color global and local oriented edge magnitude pat-terns,”IEEE Trans.Intell.Transp.Syst.,vol.15,no.4,pp.1466–1477, Aug.2014.

[28]F.Zaklouta,B.Stanciulescu,and O.Hamdoun,“Traf?c sign classi?cation

using k-d trees and random forests,”in Proc.Int.Conf.Neural Netw., 2011,pp.2151–2155.

[29]D.Cire?s an,U.Meier,J.Masci,and J.Schmidhuber,“Multi-column deep

neural network for traf?c sign classi?cation,”Neural Netw.,vol.32, pp.333–338,Aug.2012.

[30]J.Stallkamp,M.Schlipsing,J.Salmen,and C.Igel,“Man https://www.sodocs.net/doc/c88525755.html,puter:

Benchmarking machine learning algorithms for traf?c sign recognition,”

Neural Netw.,vol.32,pp.323–332,Aug.2012.

[31]J.Stallkamp,M.Schlipsing,J.Salmen,and C.Igel,“Introduction to

the special issue on machine learning for traf?c sign recognition,”IEEE Trans.Intell.Transp.Syst.,vol.13,no.4,pp.1481–1483,Dec.2012. [32]R.Timofte,K.Zimmermann,and L.V.Gool,“Multi-view traf?c sign

detection,recognition,and3D localization,”Mach.Vis.Appl.,vol.25, no.3,pp.633–647,Apr.2014.

[33]Z.Hu,“Intelligent road sign inventory(IRSI)with image recognition and

attribute computation from video log,”Comput.-Aided Civil Infrastruct., vol.28,no.2,pp.130–145,Feb.2013.

[34]A.Habib,R.Uebbing,and K.Novak,“Automatic extraction of road

signs from terrestrial color imagery,”Photogramm.Eng.Remote Sensing, vol.65,no.5,pp.597–601,1999.

[35]D.Manandhar and R.Shibasaki,“Vehicle-borne Laser Mapping Sys-

tem(VLMS)for3-D GIS,”in Proc.IEEE IGARSS,2001,vol.5, pp.2073–2075.

[36]Road Talk“A new tool for engineering surveys Mobile Terrestrial LiDAR

Scanning(MTLS),”Ontario’s Transp.Technol.Transfer Dig.,Ministry Transp.,Toronto,ON,USA,Spring2013.

[37]L.Zhou and G.V osselman,“Mapping curbstones in airborne and mo-

bile laser scanning data,”Int.J.Appl.Earth Observ.Geoinf.,vol.18, pp.293–304,Aug.2012.

[38]B.Yang,L.Fang,Q.Li,and J.Li,“Automated extraction of road markings

from mobile lidar point clouds,”Photogramm.Eng.Remote Sens.,vol.78, no.4,pp.331–338,2012.

[39]S.Pu,M.Rutzinger,G.V osselman,and S.O.Elberink,“Recognizing

basic structure from mobile laser scanning data for road inventory stud-ies,”ISPRS J.Photogram.Remote Sens.,vol.66,no.6,pp.s28–s39, Dec.2011.

[40]H.Guan,J.Li,Y.Tao,M.Chapman,and C.Wang,“Automated road

information extraction from mobile laser scanning data,”IEEE Trans.

Intell.Transp.Syst.,vol.16,no.1,pp.194–205,Feb.2015.

[41]B.Yang and Z.Dong,“A shape-based segmentation method for mo-

bile laser scanning point clouds,”ISPRS J.Photogramm.Remote Sens., vol.81,pp.19–30,Jul.2013.

[42]Z.Zhang,“A?exible new technique for camera calibration,”Microsoft

Res.,Redmond,WA,USA,Tech.Rep.MSR-TR-98-71,Dec.1998. [43]J.Weijer and C.Schmid,“Coloring local feature extraction,”in Proc.

ECCV,2006,pp.334–348.

[44]N.Dalal and B.Triggs,“Histograms of oriented gradients for human

detection,”in Proc.IEEE CVPR,2005,pp.886–893.

WEN et al.:SPATIAL-RELATED TRAFFIC SIGN INSPECTION FOR INVENTORY PURPOSES USING MLS DATA 37

[45]Y .Yu,J.Li,H.Guan,and C.Wang,“Automated extraction of urban

road facilities using mobile laser scanning data,”IEEE Trans.Intell.Transp.Syst.,DOI:10.1109/TITS.2015.2399492.[Online].Available:https://www.sodocs.net/doc/c88525755.html,/xpl/articleDetails.jsp?tp =&arnumber =7047870&queryText%3D10.1109%2FTITS.2015.2399492

[46]L.Breiman,“Random forests,”Mach.Learn.,vol.45,no.1,pp.5–32,

2002.

Chenglu Wen (M’14)received the Ph.D.degree in mechanical engineering from China Agricultural University,Beijing,China,in 2009.She is currently an Assistant Professor with Fujian Key Laboratory of Sensing and Computing for Smart City,School of In-formation Science and Engineering,Xiamen Univer-sity,Xiamen,China.She has coauthored more than 30research papers published in refereed journals and proceedings.Her current research interests include machine vision,machine learning,and point cloud data processing.She is the Secretary of the ISPRS

WG I/3on Multi-Platform Multi-Sensor System Calibration

(2012–2016).

Jonathan Li (M’00–SM’11)received the Ph.D.de-gree in geomatics engineering from the University of Cape Town,Cape Town,South Africa.He is currently a Professor with the MoE Key Laboratory of Underwater Acoustic Communication and Marine Information Technology,School of Information Sci-ence and Engineering,Xiamen University,Xiamen,China.He is also a Professor with and the Head of the GeoSTARS Laboratory,Faculty of Environment,University of Waterloo,Waterloo,ON,Canada.He has coauthored more than 300publications,over 100

of which were published in refereed journals including IEEE-TGRS,IEEE-TITS,IEEE-GRSL,ISPRS-JPRS,IJRS,PE&RS,and RSE.His current research interests include information extraction from mobile LiDAR point clouds and from earth observation images.He is the Chair of the ISPRS WG I/Va on Mobile Scanning and Imaging Systems (2012–2016),the Vice Chair of the ICA Commission on Mapping from Remote Sensor Imagery (2011–2015),and the Vice Chair of the FIG Commission on Hydrography

(2015–2018).

Huan Luo received the B.Sc.degree in com-puter science from Nanchang University,Nanchang,China,in 2009.He is currently working toward the Ph.D.degree with Fujian Key Laboratory of Sensing and Computing for Smart City,School of Informa-tion Science and Engineering,Xiamen University,Xiamen,China.His current research interests in-clude computer vision,machine learning,and mobile LiDAR point cloud data

processing.

Yongtao Yu received the B.Sc.degree in com-puter science from Xiamen University,Xiamen,China,in 2010.He is currently working toward the Ph.D.degree with Fujian Key Laboratory of Sensing and Computing for Smart City,School of Infor-mation Science and Engineering,Xiamen Univer-sity.His current research interests include computer vision,machine learning,and LiDAR point cloud

processing.

Zhipeng Cai received the B.Sc.degree in com-puter science from Xiamen University,Xiamen,China,in 2013.He is currently working toward the M.Sc.degree with Fujian Key Laboratory of Sensing and Computing for Smart City,School of Infor-mation Science and Engineering,Xiamen Univer-sity.His current research interests include computer vision,machine learning,and LiDAR point cloud

processing.

Hanyun Wang (M’13)received the M.Sc.degree in information and communication engineering in 2010from the National University of Defense Technology,Changsha,China,where he is currently working toward the Ph.D.degree with the School of Elec-tronic Science and Engineering.His research inter-ests include computer vision,machine learning,and LiDAR point cloud

processing.

Cheng Wang (M’11)received the Ph.D.degree in information and communication engineering from the National University of Defense Technology,Changsha,China,in 2002.

He is currently a Professor with and the Asso-ciate Dean of the School of Information Science and Engineering and the Executive Director of Fujian Key Laboratory of Sensing and Computing for Smart City,both at Xiamen University,Xiamen,China.He has coauthored more than 80papers in referred journals,including IEEE-TGRS,IEEE-TITS,IEEE-GRSL,IEEE-JSTARS,IJRS,and ISPRS-JPRS.His current research interests include remote sensing image processing,mobile LiDAR data analysis,and multisensor fusion.He is a Cochair of the ISPRS WG I/3on Multi-Platform Multi-Sensor System Calibration (2012–2016)and a Council Member of China Society of Image and Graphics.

SQL数据库基础知识集合

1、数据库简介： 数据库是专门开发数据管理的软件，或者说专门管理数据的软件就是数据库。 数据库存在的意义就是：减轻开发人员的负担。数据库是一个综合的软件，那么我们不需要队要进行2进制保存数据进行处理了，但是却是要与数据库产生交互，那么命令式SQL，有技巧的，数据库就是万物皆关系（面向对象，万物皆是对象）有所区别。 2、数据库的发展： 一开始的是层次化的数据与网状数据库，后来也发现使用确实很麻烦。 于是到了1970年EF.Cold博士（IBM公司的研究员）开创了关系性的数据库的先驱，发表了关系性数据库的论文，但是由于当时电脑硬件的局限性，大家觉得跑如此大的程序不值得。后来，Oracle（甲骨文）公司的创始人，拉里带领Oracle投入到关系型数据库的研发，并且得到了一个大客户—美国国防部。随即开始世界刮起了关系数据库的旋风，随后各个公司都纷纷推出自己的数据库系统。比如：IBM的DB2 ，还有风靡一时的DBS3。 但是随即出现不兼容的问题，由于最早的时候都没有进行没规范。所以到最后各个数据库巨头统一了操纵数据库的SQL（结构化Struct数据查询语言）变成了标准语言，而关系型数据库也俨然变成大家的宠儿，Oracle也从一个小公司，变成现在的数据库巨头，而我们的微软也推出了SQLServer。当然还有PHPer的最爱mySQL。但是mySQL被SUN，SUN 被Oracle收购，现在有免费版与收费专业版了。所以我们学习SQL语言的时候，先学共同点，再学特异性。各种数据库软件在使用上有一点区别。 3、数据库系统详解： 为适应数据处理的需要而发展起来的一种较为理想的数据处理的核心机构。计算机的高速处理能力和大容量存储器提供了实现数据管理自动化的条件。 数据库系统一般由4个部分组成： 数据库，即存储在磁带、磁盘、光盘或其他外存介质上、按一定结构组织在一起的相关数据的集合。（个体） 数据库管理系统（DBMS）。一组能完成描述、管理、维护子数据库的程序系统。它按照一种公用的和可控制的方法完成插入新数据、修改和检索原有数据的操作。 数据库管理员（DBA）。 用户和应用程序。（微软的称作SSMS） 4、数据库系统的基本要求是： 1、能够保证数据的独立性。数据和程序相互独立有利于加快软件开发速度，节省开发费用。 2、冗余数据少，数据共享程度高。 3、系统的用户接口简单，用户容易掌握，使用方便。 4、能够确保系统运行可靠，出现故障时能迅速排除，能够保护数据不受非受权者访问或破坏，能够防止错误数据的产生，一旦产生也能及时发现。 5、有重新组织数据的能力，能改变数据的存储结构或数据存储位置，以适应用户操作特性的变化，改善由于频繁插入、删除操作造成的数据组织零乱和时空性能变坏的状况。 6、具有可修改性和可扩充性、可维护性。 7、能够充分描述数据间的内在联系。 5、数据库（Database）: 由众多的数据、数据表、约束、存储过程、函数、视图、索引构成的一个数据存储与交互单元，是按照数据结构来组织、存储和管理数据的仓库。 6、数据表（table）: 数据表，实际上是一个二维表。一般是围绕一个事务、动作记录，或者是一个信息主题作为一个数据表。数据表由行与列构成。 7、列（column、field）: 列，其实就是字段。也是决定了信息的基本单元。列，包含有数据类型的设定。 8、行（row、record）： 行，实际上就是一条基本信息。一行包含了多列数据的存储的信息。所以一行也有一条记录之称。 9、行业（trade） 一个行业一种需求，没一个需求每一种数据库的设计模式与思想。每个行业的数据设计的重点都是不同的。侧重查询（要求低范式）还是操作（要求搞范式）就是自己选择的问题了。 10、索引（index） 索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引其实就是一个B+树，但是这个索引是N^n层数次方的。目的就是在数据库中划分出一定的区域优化查询。可以提升大量数据的查询速度。索引一般可以分为：基于字段优化查询速度的普通索引、唯一性索引、主键索引、全文索引、单列与多列索引。现在由于数据库系统的不断升级，我们只要设定索引就可以了，不需要特殊的维护。而且数据在查询的时候也会根据查询适当地选择是利用索引查询，还是仅仅是表查询。由于数据库系统的发展，系统内部已经自动帮我们完成对索引的维护。但是在设计的时候要考虑到索引的损耗问题。数据库DB就像是一个字典，索引就是根据指定字段制成的快速指向。由于只是指向数据对象标识，真正的数据是存储在DB中，所以查询速度极快。但是额外的内存与硬盘花销也是一个需要考虑的问题。比如：增加、删除、修改时数据库都要对索引进行维护，但是这样也是为了最后查询的效率的提升，特别适合W行级别的数据查询。而索引可以分为：隐式索引（针对单个字段）、唯一索引（唯一约束）、函数索引（函数（字段））、聚簇索引（主键）、组合索引（最多16个field）与全文索引（text）。一般索引会占用原数据库大小的20%。 11、视图（view） 固化的子查询，将一个子查询起了一个固化的名字，保存在数据库中，方便以后的使用。其实调用大量的Join来进行一个查询一般也是用视图。视图与索引都是为了优化查询的速度与语句。视图是优化语句，索引是优化单查速度。一般是DBA来设定数据库的视图，封装内部数据库的数据关系，范式修改数据容易了，视图让我们查询复杂关系的数据变得容易。 12、触发器（trigger） 触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由个事件来触发，比如当对一个表进行操作（insert，delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以从DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。 13、SQLServer数据类型详解

SQL银行数据库管理语句(详细版)

create table UserInfo( CustomerId char(20), CustomerName char(50)not null, PID varchar(20)unique, Telephone varchar(20), Address char(50), PRIMARY KEY(CustomerId)) create table CardInfo( cardID char(50)not null, curType varchar(10)not null default'RMB', savingType varchar(10), openDate datetime default getdate(), openMoney int not null, balance int not null, pass char(50)not null default'888888', IsReportLoss char(50)not null default'否', customerID char(20)not null foreign key(customerID)references userinfo(customerID), PRIMARY KEY(CardId)) create table TransInfo( transDate datetime not null default'getdate()', cardID char(50)foreign key(cardID)references cardinfo(cardID), transType char(10)not null, transMoney bigint not null, remark char(20), ID int identity(1,1)not null) Insert into userinfo values('10001','李清','420101************','2071-84216821','湖北武汉'); Insert into userinfo values('10002','玉清','420101************','2071-84216820','湖北武汉'); INSERT INTO CardInfo V ALUES('102128001','RMB','活期',getdate(),'10000','10000','84212121','否','10001'); INSERT INTO CardInfo V ALUES('102128002','RMB','活期',getdate(),'10001','10001','84202020','否','10002');

sql数据库期末考试题及答案

一、单选题（共 10 道试题，共 50 分。）V 1. SQL Server中，保存着每个数据库对象的信息的系统表是（）。 A. sysdatabases B. Syscolumns C. Sysobjects D. Syslogs 满分：5 分 2. 在存在下列关键字的SQL语句中，不可能出现Where子句的是（）。 A. Update B. Delete C. Insert D. Alter 满分：5 分 3. 在查询语句的Where子句中，如果出现了“age Between 30 and 40”,这个表达式等同于（）。 A. age>=30 and age<=40 B. age>=30 or age<=40 C. age>30 and age<40 D. age>30 or age<40 满分：5 分 4. 如果要在一管理职工工资的表中限制工资的输入围，应使用（）约束。 A. PDRIMARY KEY B. FOREIGN KEY C. unique D. check 满分：5 分 5. 记录数据库事务操作信息的文件是（）。 A. 数据文件 B. 索引文件 C. 辅助数据文件 D. 日志文件 满分：5 分 6. 要查询XSH数据库CP表中产品名含有“冰箱”的产品情况，可用（）命令。 A. SELECT * FROM CP WHERE 产品名称LIKE ‘冰箱’ B. SELECT * FROM XSH WHERE 产品名称LIKE ‘冰箱’ C. SELECT * FROM CP WHERE 产品名称LIKE ‘%冰箱%’ D. SELECT * FROM CP WHERE 产品名称=‘冰箱’ 满分：5 分 7. 储蓄所有多个储户，储户能够在多个储蓄所存取款，储蓄所与储户之间是（）。 A. 一对一的联系 B. 一对多的联系 C. 多对一的联系 D. 多对多的联系 满分：5 分

(整理)SQLServer数据库基本知识点.

SQL Server 数据库基本知识点一、数据类型

二、常用语句 (用到的数据库Northwind) 查询语句 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的 表或视图、以及搜索条件等。例如，下面的语句查询Customers 表中公司名称为“Alfreds Futterkiste”的ContactName字段和Address字段。 SELECT ContactName, Address FROM Customers WHERE CompanyName='Alfreds Futterkiste' (一) 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例如，下面语句显示Customers表中所有列的数据： SELECT * FROM Customers 2、选择部分列并指定它们的显示次序查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 例如： SELECT ContactName, Address FROM Customers 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题 as 列名 列名列标题如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题： SELECT ContactName as 联系人名称, Address as地址 FROM Customers 4、删除重复行

SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认 为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 SELECT DISTINCT(Country) FROM Customers 5、限制返回的行数 使用TOP n [PERCENT]选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT 时，说明n是 表示一百分数，指定返回的行数等于总行数的百分之几。 例如： SELECT TOP 2 * FROM Customers SELECT TOP 20 PERCENT * FROM Customers (二)FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列 所属的表或视图。例如在Orders和Customers表中同时存在CustomerID列，在查询两个表中的CustomerID时应 使用下面语句格式加以限定： select * from Orders,Customers where Orders.CustomerID =Customers.CustomerID 在FROM子句中可用以下两种格式为表或视图指定别名： 表名 as 别名 表名别名 select * from Orders as a,Customers as b where a.CustomerID =b.CustomerID SELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。 例如： select * from Customers where CustomerID in (select CustomerID from Orders where EmployeeID=4) 此例中，将SELECT返回的结果集合给予一别名CustomerID，然后再从中检索数据。 (三) 使用WHERE子句设置查询条件 WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：select CustomerID from Orders where EmployeeID=4

SQL server数据库毕业设计论文

河南工业职业技术学院 《SQL server数据库基础》 大型作业 （2009—2010学年第一学期） 系部：计算机工程系 专业：计算机网络技术 课程名称：S Q L server 数据库 班级： 姓名：托尔 学号： 指导老师： 2010—1—14

企业人事管理系统分析与概述 摘要： 信息技术为企业的发展提供了机遇，也带来了挑战。如何改良企业内部经营机制，公正、客观、全面、快捷地评估员工的业绩，实现以人为本的经营战略，提高人事管理工作的效率，使人事管理员有更多的精力去做人力资源分析、研究和开发工作，是企业立足发展，开拓未来，领先同行业的关键。本文叙述了一个人事管理系统形成的全过程，包括部分演示。 本课程设计是一个企业人事管理系统的后台数据库的设计，首先要查阅资料对企业人事管理领域的基本知识有一定的认识，了解用户各个方面的需求，包括现有的以及将来的可能增加的需求。 本数据库按照数据库设计的六个阶段进行设计：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库实施、数据库的运行和维护。 关键词： [企业人事、部门、工资、奖惩]

前言 随着计算机技术、网络技术和信息技术的发展，越来越多地改善着现代人的观念。网络办公自动化系统是计算机技术和网络迅速发展的一个办公应用解决方案，它的主要目的是实现信息交流和信息共享，提供协同工作的手段,提高办公的效率,让人们从繁琐的有纸办公中解脱出来。现在我国的许多的机关单位的人事管理水平还停留在纸介质的基础上，这样的机制已经不能适应时代的发展，因为它浪费了许多人力和物力，在信息时代这种传统的管理方法必然被计算机为基础的信息管理所取代。 基于这种情况，我们研究开发了这套人事管理系统，希望这套系统能为企业人事管理带来更多的方便。 本数据库的设计建设，虽然倾注我们小组所有成员的努力，但是由于时间仓促水平有限，纰漏和不足在所难免希望老师和同学们批评指正。

SQL2008数据库使用手册

SQL数据库用户使用手册 如何将本地SQL数据库导入到万网主机上，一般分为4个步骤 示例： 第一步：通过SQL脚本生成表结构，可以保证本地数据库中视图、存储过程、以及表的默认值、标识、主键等属性的完整性，避免由于DTS传输引起丢失属性的问题，同时使用查询分析器执行SQL脚本，在服务器端创建表结构； 1)、请您先运行本地的企业管理器 2）、请您点击您本地的数据库点击右健－》选择生成SQL脚本

3)、选择常规－》点击全部显示，选择您想导出的脚本对象 4)、点击选项－》表脚本选项，把您所用到的脚本选中然后点击确定 5）为自己导入的.sql脚本文件命名，并保存在本地

6）、找到刚才我们保存在本地的.sql脚本文件，使用记事本打开 7）、选择编辑－》替换，把程序中所有[dbo]的字符都更改成您万网发信告知您的数据库登

陆名，更换完成后保存关闭记事本 8）、通过万网通知书中的数据库登陆地址、数据库登陆名、和数据库密码，使用企业管理器连接到万网的主机服务器上，然后选择查询分析器

9）、点击打开选择刚才编辑过的.sql脚本，然后点击运行 第二步：在本地创建一个和万网主机相同权限的SQL数据库 1）、完成上面操作后，请您选择数据库点击右键选择新建数据库，由于您在万网申请的是虚拟主机，万网分配的权限都是user的而不是dbo的权限，因此需要您在本地也创建一个与服务器一样的配置，以便正常完成导入操作

2)、在常规－》名称处输入万网开通通知中告知您的数据库库名,然后点击确定。例如：cw01001_db 3)、选择安全性－》新建登陆

在线考试系统-操作手册

微厦在线考试（试题练习）平台 操作手册

1建设内容 微厦在线考试（试题练习）平台主要分为两大块学员管理和管理员管理，学员在系统中的主要职责是在线学习、在线练习、在线考试、充值消费；管理员主要负责系统日常任务的分配和管理，如：教务管理、题库管理、资金管理、员工管理等。 1.1学员管理 学员在系统中主要是学习和消费，学员进入系统后主要对以下六个模块的内容进行操作：章节练习、模拟场、考试指南、错题重做、我的笔记、我的收藏、统计分析、联系客服、个人中心，如下图： 学员进入系统后如果未购买课程，可以对系统中的课程进行试用，试用的题数可以管理员后台自定义，试用分为两种情况：一、游客试用（即未登录试用）；游客试用时只能操作章节练习、考试指南、联系客服这三个模块的内容，游客操作其他模块会自动跳转到登录界面。二、登录试用；学员登录试用时可以操作除“模拟考场”之外的所有模块，学员购买科目试题后方能操作全部模块。 点击右上角的“”可以切换专业，也可以查看“我的科目”，如下图： 点击其他专业则会切换到其他专业下的科目学习，点击“我的科目”可以查看“当前科目”和“已购买的科目”。如下图： 1.1.1章节练习 学员第一次登录后操作任意模块都会进入专业选择，学员选择相关专业和科目后才能进行学习，级别划分是：专业>>>科目>>>章节，学员学习时针对“科目”进行充值消费，科目有多个章节，这里的“章节练习”包含了该科目下的所有章节。如下图： “章节练习”即试题练习，主要是对章节里的试题进行练习和学习。学员在练习时可以查看试题的答案和解析。对于一些难题、错题、易考题学员可以收藏，

收藏后收藏按钮会变成红色，笔记功能有助于学员在学习过程中记录自己的解题思路，帮助理解加深记忆。左右滑动可以切换上下题。 点击“提交”按钮后系统会自动对该题的答案做出批阅，如下图： 如果该试题有错误，学员可以点击右上角的“报错”向系统提交错误报告，错误报告在管理员后台查阅。如下图： 1.1.2模拟考场 模拟考场中存储了科目下的所有试卷，学员可以随时进行模拟测试，如下图： 如上图所示右上角是计时器，显示该场考试的剩余时间，点击“”可以收藏试题，收藏后“”按钮会变成“”点击“”可以报错。最下方是答题卡和提交按钮，答题卡按钮提示了当前已做答的题数和全部试题数，点击可进入答题卡界面。如下图： 如上图所示，蓝色背景的试题序号表示已作答的试题，点击“试题序号”可以自动定位到该试题，点击“交卷”可以交卷，交卷后系统会自动给出得分，学员也可以在“统计分析”中查看详细的成绩报告。 1.1.3考试指南 考试指南类似于教学大纲，明确重点、难点、考点，帮助学员轻松掌握，顺利通过考试。由管理员后台录入。 1.1.4错题重做 错题重做收录了学员每次在练习中做错的试题，相当于一个错题集。如下图所示： 点击试题题干可以查看该试题的答案和笔记，点击“进入答题”可以练习这些错题，重点学习。如下图：

SQL数据库完全操作手册

SQL数据库完全操作手册 SQL是Structured Quevy Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL。 ##1 二、SQL数据库数据体系结构 SQL数据库的数据体系结构基本上是三级结构，但使用术语与传统关系模型术语不同。在SQL中，关系模式(模式)称为“基本表”(base table)；存储模式(内模式)称为“存储文件”(stored file)；子模式(外模式)称为“视图”(view)；元组称为“行”(row)；属性称为“列”(column)。名称对称如^00100009a^： ##1 三、SQL语言的组成 在正式学习SQL语言之前，首先让我们对SQL语言有一个基本认识，介绍一下SQL 语言的组成： 1.一个SQL数据库是表(Table)的集合，它由一个或多个SQL模式定义。 2.一个SQL表由行集构成，一行是列的序列(集合)，每列与行对应一个数据项。 3.一个表或者是一个基本表或者是一个视图。基本表是实际存储在数据库的表，而视图是由若干基本表或其他视图构成的表的定义。 4.一个基本表可以跨一个或多个存储文件，一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储上一个物理文件对应。 5.用户可以用SQL语句对视图和基本表进行查询等操作。在用户角度来看，视图和基本表是一样的，没有区别，都是关系(表格)。 6.SQL用户可以是应用程序，也可以是终端用户。SQL语句可嵌入在宿主语言的程序中使用，宿主语言有FORTRAN，COBOL，PASCAL，PL/I，C和Ada语言等。SQL用户也能作为独立的用户接口，供交互环境下的终端用户使用。 ##1 四、对数据库进行操作 SQL包括了所有对数据库的操作，主要是由4个部分组成： 1.数据定义：这一部分又称为“SQL DDL”，定义数据库的逻辑结构，包括定义数据库、基本表、视图和索引4部分。 2.数据操纵：这一部分又称为“SQL DML”，其中包括数据查询和数据更新两大类操作，其中数据更新又包括插入、删除和更新三种操作。 3.数据控制：对用户访问数据的控制有基本表和视图的授权、完整性规则的描述，事务控制语句等。 4.嵌入式SQL语言的使用规定：规定SQL语句在宿主语言的程序中使用的规则。下面我们将分别介绍： ##2 (一)数据定义 SQL数据定义功能包括定义数据库、基本表、索引和视图。 首先，让我们了解一下SQL所提供的基本数据类型：(如^00100009b^) 1.数据库的建立与删除 (1)建立数据库：数据库是一个包括了多个基本表的数据集，其语句格式为： CREATE DATABASE <数据库名> ［其它参数］ 其中，<数据库名>在系统中必须是唯一的，不能重复，不然将导致数据存取失误。［其它参数］因具体数据库实现系统不同而异。 例：要建立项目管理数据库(xmmanage)，其语句应为： CREATE DATABASE xmmanage

SQL_Server数据库设计的案例分析

数据库设计的案例分析 一、教学管理 1. 基本需求 某学校设计学生教学管理系统。学生实体包括学号、姓名、性别、生日、民族、籍贯、简历、登记照，每名学生选择一个主修专业，专业包括专业编号和名称，一个专业属于一个学院，一个学院可以有若干个专业。学院信息要存储学院号、学院名、院长。教学管理还要管理课程表和学生成绩。课程表包括课程号、课程名、学分，每门课程由一个学院开设。学生选修的每门课程获得一个成绩。 设计该教学管理的ER模型，然后转化为关系模型。 若上面的管理系统还要管理教师教学安排，教师包括编号、姓名、年龄、职称，一个教师只能属于一个学院，一名教师可以上若干门课程，一门课程可以有多名老师来上，每个教师所上的每门课都有一个课堂号和课时数。试修改上题的ER模型，将教师教学信息管理增加进去。

2. 参考设计： 图一教学管理ER图 由ER模型转换的关系模型是： 学生（学号，姓名，性别，生日，民族，籍贯，专业号，简历，登记照）专业（专业号，专业，专业类别，学院号） 学院（学院号，学院，院长） 课程（课程号，课程名，学分，学院号） 成绩（学号，课程号，成绩） （题目分析：本题中有学生、专业、学院、课程四个实体。一个学生只有一个主修专业，学生与专业有多对一的联系；一个专业只由一个学院开设，一门课程只由一个学院开设，学院与专业、学院与课程都是一对多的联系；学生与课程有多对多的联系。 在转换为关系模型时，一对多的联系都在相应的多方实体的关系中增加一个外键。） 增加教师，ER图如下。

图二有教师实体的教学管理ER图 3. 物理设计 基于Access的数据库结构设计如下。 指定数据库文件的名称，并为设计好的关系模型设计表结构。 数据库文件保存在“E:\教学管理\”文件夹中，数据库文件名：教学管理.MDB。 表包括：学院、专业、学生、课程、成绩单。对应表结构如表1-2至表1-6所示。 表1-1 学院 表1-2 专业 表1-3 学生

如何连接SQL Server数据库

如何连接SQL Server数据库（Java） 用Java连接SQL Server2000数据库有多种方法，下面介绍其中最常用的两种（通过JDBC 驱动连接数据库）。 1. 通过Microsoft的JDBC驱动连接。此JDBC驱动共有三个文件，分别是mssqlserver.jar、msutil.jar和msbase.jar，可以到微软的网站去下载（https://www.sodocs.net/doc/c88525755.html,/downloa ... &displaylang=en），如果你下载的是setup.exe，还需要安装它，安装后会生成上面的三个jar 文件。此JDBC驱动实现了JDBC 2.0。 驱动程序名称：com.microsoft.jdbc.sqlserver.SQLServerDriver（即下面的classforname） 数据库连接URL：jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname（即下面的url） 2. 通过JTDS JDBC Driver连接SQL Server数据库，此驱动的文件名为jtds-1.2.jar，下载路径为（https://www.sodocs.net/doc/c88525755.html,/project/showfiles.php?group_id=33291），此驱动支持Microsoft SQL Server (6.5, 7.0, 2000 和2005) 和Sybase，并且实现了JDBC 3.0，是免费的。 驱动程序名称：net.sourceforge.jtds.jdbc.Driver（即下面的classforname） 数据库连接URL：jdbc:jtds:sqlserver://localhost:1433/dbname（即下面的url） JDBC连接SQL Server数据库的Bean代码网上大把的有，下面摘录其中的一部分：（请将localhost和1433改成你实际应用中的SQL Server服务器地址和端口号，dbname改成你实际的数据库名） 1.import java.sql.*; 2.public class DatabaseConn { 3. 4.private Connection conn; 5.private Statement stmt; 6.private String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname"; 7.private String classforname = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; 8.private String uid = "sa"; 9.private String pwd = "password"; 10.

普通话水平智能测试系统操作指南

国家普通话水平智能测试系统操作手册（简易版） 安徽科大讯飞信息科技股份有限公司

目录 一系统简介 (3) 1.系统构成 (3) 2.系统构架 (3) 二测试流程 (4) 1.业务总体流程介绍 (4) 2.基层测试站测试操作流程 (5) 2.1测试报名 (5) 2.2考前准备 (10) 2.3现场测试 (16) 2.4信息上传 (21) 三系统维护 (21) 四常见问题 (22)

计算机辅助普通话水平测试系统 操作手册 一系统简介 1.系统构成 科大讯飞提供的普通话测试系统不仅能够对考生的普通话进行智能评测，还能够对考试现场和测试流程以信息化的方式管理，实现了国家普通话水平测试的测试、组织和管理的信息化，该系统主要包括两个部分： ●国家普通话水平智能测试系统 国家普通话水平智能测试系统（PSCP）是安徽科大讯飞信息科技股份有限公司在国家语委“十五”重点科研项目支持下研发完成。系统基于国家普通话水平测试大纲，可准确地对命题说话之外的所有测试题型实现自动评测，同时自动检测发音者存在的语音错误和缺陷；而且系统提供的测试管理功能，也能够帮助基层测试站组织测试，提高测试的效率。该系统部署在基层测试站，主要使用者为考生和基层测试站的管理人员。 ●国家普通话水平测试信息管理系统 国家普通话水平测试信息管理系统（PSCW）实现的是普通话水平测试全过程的计算机管理，为计算机辅助测试全面解决方案提供支撑平台。在该系统中，可以进行考生报名、测试员打分、成绩管理、数据管理等一系列操作。该系统部署在远程ＷＥＢ服务器上，相关人员通过登录网页完成相应的操作，主要使用者为省级测试管理人员、基层测试站的管理人员和测试员。 2.系统构架 普通话测试系统解决方案的构架图如下：

SQL数据库语句大全大全(完全整理版)

SQL语句大全--语句功能 --数据操作 SELECT --从数据库表中检索数据行和列 INSERT --向数据库表添加新数据行 DELETE --从数据库表中删除数据行 UPDATE --更新数据库表中的数据 -数据定义 CREATE TABLE --创建一个数据库表 DROP TABLE --从数据库中删除表 ALTER TABLE --修改数据库表结构 CREATE VIEW --创建一个视图 DROP VIEW --从数据库中删除视图 CREATE INDEX --为数据库表创建一个索引 DROP INDEX --从数据库中删除索引 CREATE PROCEDURE --创建一个存储过程 DROP PROCEDURE --从数据库中删除存储过程CREATE TRIGGER --创建一个触发器 DROP TRIGGER --从数据库中删除触发器 CREATE SCHEMA --向数据库添加一个新模式DROP SCHEMA --从数据库中删除一个模式CREATE DOMAIN --创建一个数据值域 ALTER DOMAIN --改变域定义 DROP DOMAIN --从数据库中删除一个域 --数据控制 GRANT --授予用户访问权限 DENY --拒绝用户访问 REVOKE --解除用户访问权限 --事务控制 COMMIT --结束当前事务 ROLLBACK --中止当前事务 SET TRANSACTION --定义当前事务数据访问特征 --程序化SQL DECLARE --为查询设定游标 EXPLAN --为查询描述数据访问计划 OPEN --检索查询结果打开一个游标

SQL2005数据库安装详细步骤(带图解说明)

1，安装前准备： 安装IIS，打开控制面板，点“添加或删除程序”，点“添加/删除Windows组件(A)”,把“Internet 信息服务（IIS）”前面的勾选框的勾选上，点“下一步”，一路确认完成。这个过程一般没有问题。 当然，要装SQL Server 2005，当然要准备安装程序（光盘），也就要选对版本。 我之前下载了个“Microsoft SQL Server 2005 Enterprise Edition”，兴冲冲的去安装，安装不成功，最后看他的安装说明里面恰好这个版本“Windows XP Professional Edition SP24”是不能安装的，背啊。。。。才知道磨刀不误砍材功的道理，后来去下载了“Microsoft SQL Server 2005 Standard Edition”，920MB的压缩包。 2,SQL Server 2005的安装光盘共有2张，先打开第一张，点“服务器组件、工具、联机丛书和示例(C)”

此步骤系统配置检查很重要，14个项目里面如果有1项有错误或者警告，整个SQL Server 2005都将不正常。 我第一次在一台笔记本的Windows xp2系统上安装就遇到了一个COM+组建的警告，以为没有什么大不了，不过最后是发现程序不能正常运行，在google，百度上搜索半天，操作了半天，结果还是问题依旧。不过我回家第二次在台式机上的Windows xp2安装就没有问题，一切正常。 看来和Windows xp2的系统版本没有关系，而是我的笔记本电脑的Windows xp2系统不正常了，可能重新装下系统就可以正常安装SQL Server 2005了。 重装系统看来是遇到问题的唯一法宝——除非你是那种专家级别的高手。在这个步骤遇到问题或者错误警告还是举手投降了，选择重新安装系统。可能那样解决问题的时间还要短些。烦恼也少些。

SQL Server数据库规范

数据库设计规范 1.简介 数据库设计是指对一个给定的应用环境，构造最优的数据库模式，建立数据库及其他应用系统，使之能有效地存储数据，满足各种用户的需求。数据库设计过程中命名规范很是重要，命名规范合理的设计能够省去开发人员很多时间去区别数据库实体。 最近也因为工作需要所以整理出了这个word文档，望大家指正。 2数据库设计 数据库规划→需求分析→数据库设计→应用程序设计→实现→测试→运行于维护 2.1数据库规划 定义数据库应用系统的主要目标，定义系统特定任务，包括工作量的估计、使用资源、和需求经费，定义系统的范围以及边界。 2.2需求分析 2.1.1需求分析步骤与成果 涉及人员：用户和分析人员 任务：对现实世界要处理的对象进行详细的调查，收集基础数据及处理方法，在用户调查的基础上通过分析，逐步明确用户对系统的需求，包括信息的要求及处理的要求。 方法与步骤：1.通过与用户的调查，对用户的信息需求进行收集。 2.在收集数据的同时，设计人员要对其进行加工和整理，以数据字典和数据流图的形式描述出来，并以设计人员的角度向用户讲述信息，根据用户的反馈加以修改并确定（该过程是反复的过程） 成果：数据流图，数据字典，各种说明性表格，统计输出表以及系统功能结构图。 2.1.2数据流图基本元素与数据流图 外部实体：存在于软件系统之外的人员或组织（正方形或立方体表示）。 加工：数据处理，表示输入数据在此进行变换，产生输出数据（圆角巨型或圆形表示）。数据流：表示流动着的数据（箭头线表示）。 数据存储：用来表示要存储的数据（开门矩形或两条平行横线表示）。 订单处理系统顶层流程图：

0层数据流图：

XXX软件用户手册

XXX软件用户手册 (软件版本：FMS2.1)

目录 用户手册 (3) 概述 (3) 功能特点 (3) 系统要求 (3) 软件安装 (3) 硬件安装 (4) 请用户在阅读本手册及操作本软件前先详读《RTS-8型四探针测试仪》

用户手册 因有的概念跟理论要了解后,阅读此手册和使用本软件时更容易理解、操作。 概述 RTS-8型四探针软件测试系统是一个运行在计算机上拥有友好测试界面的用户程序，程序操作直观易用。测试程序在计算机与RTS-8型四探针测试仪连接的状态下，通过计算机的并口实现通讯。 测试程序控制四探针测试仪进行测量并采集测试数据，把采集到的数据在计算机中加以分析，然后把测试数据以表格，图形直观地记录、显示出来。用户可对采集到的数据在计算机中保存或者打印以备日后参考和查看，还可以把采集到的数据输出到Excel中，让用户对数据进行各种数据分析。 功能特点 可以选择进行手动测量或者是自动测量； 对可疑测量点的数据进行重测； 详细记录被测试材料各种参数，结合测试数据进行数据统计分析； 以直方图形式统计分析数据； 打开、保存、打印测试数据； 把测试数据导出到Excel中进行更详细的数据统计分析； 系统要求 请确定你的电脑是IBM PC兼容型并具备以下最低的系统要求： Intel兼容586DX-500MHz中央处理器或者更高； 一个IEEE-1284标准并行通讯端口； 显示卡分辨率支持1024*768模式； 软件安装 本软件可安装在Win98、Win2000、XP操作系统上。请按以下步骤进行安装。 1.将四探针软件测试系统的安装光盘放入光驱中，执行光盘目录下的【Setup.exe】进行安

SQL数据库学习心得

SQL数据库学习心得 一数据库课程总结 （一）数据库概述 1 数据 （1）数据库中存储的基本对象，是描述事物的符号记录（如存储某一个人 （2) 数据的种类包括文字、图形、图象、声音 （3）数据的特点是数据与其语义是不可分的。 2 数据库 （1）（database,简称DB）是指长期存储在电脑内有组织的、可共享的数据集合； （2）数据库管理系统（Database Management System，简称DBMS），是位于用户与操作系统之间的一层数据管理软件。 3 关系数据库基础 （1）元组（Tuple）表中的一行即为一个元组。 （2）属性（Attribute）表中的一列即为一个属性，给每一个属性起一个名称即属性名。 （3）主码（Key）表中的某个属性组，它可以唯一确定一个元组。 （4）域（Domain）属性的取值范围。 （5）分量元组中的一个属性值。 （二）SQL Server概述 1 SQL Server的版本 （1）企业版，作为生产使用 （2）个人版，供移动用户使用 （3）标准版，作为小工作组或部门使用 （4）开发板，供程序员开发测试使用 2 SQL数据库的创建 （1）创建数据库的过程就是确定数据库名称、大小、存放位置等信息。 （2）数据库文件包括主数据库文件（.mdf）、次数据文件（.ndf）、事务日志文件（.ldf）（3）3种方式创建数据库：使用数据库创建向导、使用企业管理器、使用SQL语句在查询分析器中创建。 （三）数据库中的表 1 创建表两种方式：使用企业管理器、使用SQL语句

2修改表，修改操作包括：增加字段、删除字段、修改字段(名称、修改其已有的属性)两种方式，使用企业管理器、使用SQL语句 （四）数据查询 1基本的SELECT语句，例查询Readers表中所有记录的信息SELECT*FROM Readers 2查询语句的统计功能 3数据的连接 外连接的结果集中，不仅包括满足连接条件记录，还包括不满足连接条件的记录。连接方式分为：左外连接、右外连接、全外连接。 （五）数据完整性 1定义：数据完整性是指存储在数据库中的数据的一致性和准确性。 2实体完整性 又称行完整性，要求表中不能存在完全相同的记录，而且每条记录都要具有一个非空且不重复的主键值。 3使用约束:约束是保证数据完整性的有效方法, (1)PRIMARY KEY(主键约束)特点：每个表只能定义一个主键、主键值不可为空、主键值不可重复。 (2)UNIQUE(唯一约束)确保表中的某非主键列不输入重复值，PRIMARY KEY 与 UNIQUE的区别、约束数量、是否允许NULL值 (3)CHECK(检查约束)，用于限制输入到列中的值的范围 （六）数据库的安全 1对于SQL Server来说，提供身份验证和权限验证两种保护方式。 （1）身份验证模式： Windows验证模式，只限于本机使用；混合验证模式，本机或远程均可访问 2 角色管理是一种权限管理的方法，角色中的每一用户都拥有此角色中的所有权限。 3数据库角色提供了在数据库级别上的管理权限组。 （七）备份与恢复 1备份数据库，使用SQL语句备份 步骤：（1）创建备份设备 （2）备份数据库（完全数据库备份、差异备份、日志备份、文件与文件组备份） 2恢复数据库(使用企业管理器恢复、使用SQL语句恢复）

SQL Server数据库维护计划的实施步骤

作为网管员，无论其管理的网络的规模是大还是小，在日常的管理中除了维护网络平稳运行、及时排除网络故障、保护网络安全等工作以外，备份网络中关键数据也是其中的一个 非常非常重要的工作环节。 网络中的各种故障无非就分两种:软件故障和硬件故障。对于“硬件故障”可以通过维修或更换硬件设备得到及时解决;对于“软件故障”则可以通过重新安装或升级软件、重做网络或应用软件系统等方法及时解决，而且用此方法来解决网络故障大多需要一些基础的、关键的数据支持才能得以恢复正常。但是，网络中诸如此类的关键数据(特别是“应用软件系统”中的关键数据)的损坏或丢失，绝大部分是无法恢复和弥补的。即使可以恢复部分数据，弥补它们所花费的代价(诸如时间、人力、财力、物力等)都可能远远超出了公司的承受 能力。 所以说，注重数据的备份工作是网管员日常管理工作中的必须时刻关注的一项任务，也 是必须周期性重复操作的一项工作。 目录 现行备份策略 具体实现步骤 结束语 现行备份策略 我公司在组建局域网时，考虑到商业企业的特点，仔细考量了购、销、存三大环节中发生的各种数据及其存储问题后，选定了以Windows 2000 Server为操作系统，SQL Server 2000为数据库平台来搭建局域网的应用系统的软件平台，以网线为载体将购、销、存等核心部门的计算机通过局域网平台紧密地连接起来。这样，各个核心部门每天的任何一笔业务都及时地、动态地存储到公司局域网的核心Dell服务器上的SQL Server 2000数据库中，并以此 为基础平台向各方提供所需的各种数据服务。 因此，自公司局域网开始正式运作之日起，作为网管员的我就非常注重对局域网中的关键数据——特别是这些业务数据的备份工作。同时，我也希望备份数据的软件能够实现以下 自动功能。 功能1:能够在每天的某个固定的时刻(如夜晚0:00:00，当然这个时间是可以自主设定的)对包含所有业务数据库在内的所有关键数据库进行一次“完全备份”。 功能2:能够在每天的0:00:00至23:59:59这段时间内，每间隔1个小时对功能1中所涉及到的各个数据库的事务日志进行“差异备份”。