KtzEKkN8

1-- Function: get_line_across_addresses(text)
2
3-- DROP FUNCTION get_line_across_addresses(text);
4
5CREATE OR REPLACE FUNCTION get_line_across_addresses(points_str text)
6  RETURNS SETOF record AS
7$BODY$
8DECLARE 
9   lonlat text;   
10   lon text;
11   lat text;
12   rec record; 
13BEGIN
14    CREATE TEMP TABLE IF NOT EXISTS result_table1(
15       country_name text,
16       province_name text,
17       city_name text,    
18       all_name text,
19       longtitude double precision,
20       latitude double precision
21    ) ON COMMIT DROP ;    
22    foreach lonlat in array string_to_array(points_str, ',')
23    loop
24         lon := cast((select string_to_array(lonlat, ' '))[1] as double precision);
25         lat := cast((select string_to_array(lonlat, ' '))[2] as double precision);	          
26         EXECUTE 'INSERT INTO result_table1 SELECT name_0, name_1, nl_name_2,name_0||name_1||nl_name_2 ,ST_X(ST_AsText(ST_centroid(geom))) as longtitude , ST_Y(ST_AsText(ST_centroid(geom))) 
27		as latitude FROM  world_city where ST_Contains(world_city.geom, ST_GeomFromText(''POINT('|| lon ||' ' || lat ||')'',4326));';                  
28    end loop;    
29    FOR rec IN SELECT DISTINCT ON (all_name) country_name, province_name,city_name, longtitude, latitude FROM result_table1 LOOP 
30      RETURN NEXT rec; 
31    END LOOP;     
32    /*RETURN array_to_json(array_agg(t)) FROM (select DISTINCT ON (all_name) all_name, country_name,province_name,city_name FROM result_table1) as t; */
33END;
34$BODY$
35  LANGUAGE plpgsql VOLATILE
36  COST 100
37  ROWS 1000;
38ALTER FUNCTION get_line_across_addresses(text)
39  OWNER TO postgres;