map matching数据库操作系列

说明：这是在进行map matching时，对数据库操作

需求：1）目前设计了图1~2 所示的TABLE结构，需要将point_cfm中的各字段填满，并且trace_cfm的trace_id, point_id[]需要填上

　　　

　　　　　　　　　　　　　　　　　　　　　　图1

        

                            图2

步骤：

　　1、数据预处理：gps数据原始格式如下：

　　   

        为了达到把gps点分成一条条轨迹的目的，即trace_cfm需要知道哪些点是属于某条轨迹的，现在处理一下这些数据。

　　生成一列数据作为trace id,trace id 的生成包含了3部分内容：a、taxi id b、一年的第多少天 c、一天的哪一个时段。python代码如下：

import time
import string

def segtime( strtime ):
    """
        param: a time string
        ret  : a integer composed by two parts, the first is the day of in
               that year(1~366) and the last is the 0~47
        author: shaohui zhu
    """
    struct_time = time.strptime( strtime,"%Y-%m-%d %H:%M:%S")
    hour = struct_time.tm_hour
    minute = struct_time.tm_min
    dayofyear = struct_time.tm_yday
    if minute >= 30:
        ret = hour*2 + 1
    else:
        ret = hour*2
    ret = dayofyear*100 + ret
    return ret 

#segtime("2015-07-08 23:30:59")

f = open('366.txt','r')
fd = open('366ok.txt','w')
lines = f.readlines()
for line in lines:
    comma_err = 0
    comma1 = line.find(',')
    if -1 != comma1 :
        comma2 = line.find(',', comma1+1)
        if -1 != comma2 :
            comma3 = line.find(',', comma2+1)
            if -1 != comma3 :
                comma4 = line.find(',', comma3+1)
                if -1 != comma4:
                    comma_err = 1
            else:
                comma_err = 1
        else:
            comma_err = 1
    else:
        comma_err = 1

    if comma_err == 0:
        seg = segtime( line[4:23] )
        itrace = 366 * 100000 + seg
        enterindex = line.find('\n')
        if enterindex != -1:
            line = line[0:enterindex-1]
        newline = line + ',' + "%d"%itrace +'\n'
        print newline
        fd.write( newline )

　　即完成了数据预处理过程，结果如下：

　　

 

　　2、要在导入gps数据到point_cfm时，在trace_cfm中填写trace_id字段，创建一个触发器来完成，代码如下：

create or replace function get_point() returns trigger as 
$BODY$
	DECLARE
		rec record;
		oldrec record;
	BEGIN
		FOR rec IN (SELECT trace_id FROM point_cfm) LOOP
			SELECT INTO oldrec * FROM trace_cfm WHERE trace_id = rec.trace_id;
			IF NOT FOUND THEN
				INSERT INTO trace_cfm VALUES(rec.trace_id);
			END IF;
		END LOOP;
		return NEW;
	END;
$BODY$ LANGUAGE plpgsql;



create trigger tri_get_point 
AFTER insert or update or delete
on point_cfm 
for each row
execute procedure get_point()

　　按照另一篇文章中的方法导入gps数据到point_cfm中

　　

　　3、填写trace_cfm中的point_id[]，pl代码如下：

CREATE OR REPLACE FUNCTION write_point() RETURNS void
AS $BODY$
DECLARE
	id bigint;
BEGIN
	FOR id IN (SELECT trace_id FROM trace_cfm) LOOP

		DECLARE
			points bigint[];
			p bigint;
			i integer;
		BEGIN
			i := 0;
			FOR p IN (SELECT point_pk FROM point_cfm WHERE trace_id = id) LOOP
				points[i] := p;
				i := i+1;
			END LOOP;
			--SELECT sort_asc(points);
			UPDATE trace_cfm SET point_id = points WHERE trace_id = id;
		END;

	END LOOP;
END;
$BODY$ LANGUAGE plpgsql;

SELECT write_point();

　　

　　上述步骤即完成。

　　4、注意：

　　　　1）导入的point必须是按照时间有序的，一般来说应该是的，但要注意

　　　　2）本来我计划在把points填入point_id[]时，首先对points进行排序(利用intarray的sort_asc函数)，但是该函数只对integer数据有效，所以就没有进行。

　　　　但是，trace一定是严格时间序的，在每次操作时，注意检查一下。