points_contains_query_plpythonu.psql

/**
 * 传入轨迹抽样点，获取整条轨迹穿越地区
 * 300: 执行时间184.992ms
 * 1000: 执行时间587.400ms
 * 5000: 执行时间2834.339ms
 * 10000:执行时间5526.228ms
 */
CREATE OR REPLACE FUNCTION getAddressFromPoints(points_str text)
  RETURNS text AS $$
import json
addres = []
addresses = {'addresses':[]}
for point in points_str.split(','):
    tmp = point.split(' ')
    result = plpy.execute("SELECT name_0, name_1, nl_name_2 FROM  world_city where ST_Contains(world_city.geom, ST_GeomFromText('POINT(%s %s)',4326));"%(tmp[0],tmp[1]))
    if not len(result)==0:
        addr_concat = result[0]['name_0'] + result[0]['name_1'] + result[0]['nl_name_2']
        if not addr_concat in addres:
            address = {		 
		'name_0':result[0]['name_0'],
		'name_1':result[0]['name_1'],
		'nl_name_2':result[0]['nl_name_2'],
		'datetime':tmp[2]+ ' ' + tmp[3]
	    }
	    addres.append(addr_concat)
            addresses['addresses'].append(address)
return json.dumps(addresses)	
$$
LANGUAGE 'plpythonu' 



/**
 * 传入点数据，获取点数据对应的高程
 * 100: 执行时间325.601ms
 * 500: 执行时间2448.811ms
 * 1000: 执行时间5762.753ms
 * 10000:执行时间37975.752ms
 */

DROP FUNCTION IF EXISTS get_value_by_points(text);
CREATE FUNCTION get_value_by_points(points_str text)
       RETURNS json AS $$
DECLARE 
   lonlat text;   
   lon text;
   lat text;   
BEGIN
    CREATE TEMP TABLE IF NOT EXISTS result_table(
       value double precision
    ) ON COMMIT DROP ;
    foreach lonlat in array string_to_array(points_str, ',')
    loop
         lon := cast((select string_to_array(lonlat, ' '))[1] as double precision);
         lat := cast((select string_to_array(lonlat, ' '))[2] as double precision);
         EXECUTE 'INSERT INTO result_table SELECT ST_Value(rast,  ST_GeomFromText(''POINT(' ||lon || ' ' ||lat||' )'',4326)) val  FROM  elevation WHERE ST_Intersects(rast,ST_GeomFromText(''POINT(' ||lon ||' '|| lat ||' )'',4326))';
    end loop;
    RETURN array_to_json(array_agg(t)) FROM (select * FROM result_table) as t;
END;
$$ LANGUAGE plpgsql;