maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_get_location_local_v3(double precision, double precision)
Parameters
Name
Type
Mode
longitude
double precision
IN
latitude
double precision
IN
Definition
DECLARE punto geometry('POINT',4326); colonia_name varchar; colonia_distance float; colonia_max_distance float := 0.05; -- si no intersecta, distancia máxima que el punto puede tener hacia una colonia para considerarse dentro de la colonia county_max_distance float := 0.05; -- si no intersecta, distancia máxima que el punto puede tener hacia una colonia para considerarse dentro de la colonia colonia_key varchar; localidad_name varchar; localidad_distance float; localidad_max_distance float := 0.05; localidad_key varchar; municipio_nombre varchar; municipio_key varchar; municipio_distancia float; municipio_max_distance float := 0.05; estado_nombre varchar; estado_distancia float; estado_max_distance float := 0.05; estado_key varchar; ciudad_key varchar; geocercas_nombres varchar; country_name varchar; var1 varchar; var2 varchar; var3 varchar; county_name varchar; county_distance float; BEGIN SET search_path = public, geo,tiger; punto := st_geomfromtext('POINT('||longitude||' '||latitude||')',4326); /*************** Determinar el país en que se encuentra el punto ****************************/ -- PERFORM 1 FROM estados /*Mexico */ WHERE st_intersects(geom,punto); PERFORM gid from countries where st_intersects(punto,geom) and gid = 157; /*gid for Mexico*/ IF FOUND THEN country_name := 'Mexico'; END IF; IF country_name IS NULL THEN PERFORM gid from countries where st_intersects(punto,geom) and gid = 233; /*gid for Usa*/ IF FOUND THEN country_name := 'United States'; END IF; END IF; IF country_name IS NULL THEN SELECT cntry_name INTO country_name FROM countries WHERE st_intersects(geom,punto); END IF; /**************************************COLOMBIA*********************************************************/ IF country_name = 'Colombia' THEN SELECT nombre_dpt INTO estado_nombre FROM colombia_departamentos WHERE st_intersects(geom,punto); estado_key := '"estado"=>' || coalesce(quote_ident(estado_nombre),'NULL'); SELECT nombre_mpi INTO municipio_nombre FROM colombia_municipios WHERE st_intersects(geom,punto); SELECT st_distance(geom,punto),name INTO colonia_distance,colonia_name FROM colombia_lmimport ORDER BY geom <-> punto LIMIT 1; IF colonia_distance > colonia_max_distance THEN colonia_name := NULL; END IF; SELECT st_distance(geom,punto),name INTO localidad_distance,localidad_name FROM colombia_peaje ORDER BY geom <-> punto LIMIT 1; IF localidad_distance > localidad_max_distance THEN SELECT st_distance(geom,punto),name INTO localidad_distance,localidad_name FROM colombia_lugares ORDER BY geom <-> punto LIMIT 1; IF localidad_distance > localidad_max_distance THEN localidad_name := NULL; END IF; END IF; ciudad_key := '"ciudad"=>'|| quote_ident(concat_ws(',',localidad_name,colonia_name,municipio_nombre)) ; RETURN hstore(concat_ws(',',estado_key,ciudad_key)); /*************************************** Estados Unidos *******************************************************/ ELSIF country_name = 'United States' THEN SELECT state_name INTO estado_nombre FROM usa_states WHERE st_intersects(geom,punto); estado_key := '"estado"=>' || coalesce(quote_ident(estado_nombre),'NULL'); IF estado_nombre NOT IN ('California','Arizona','Texas','New Mexico') THEN /*NOT IN THE BORDER WITH MX*/ SELECT cityname INTO colonia_name FROM cities ORDER BY geom <-> punto LIMIT 1; ciudad_key := '"ciudad"=>'|| quote_ident(colonia_name) ; RETURN hstore(concat_ws(',',estado_key,ciudad_key)); END IF; /* Reverse geocode the USA states in the MX border */ select concat_ws(' & ',(regexp_split_to_array(pprint_addy(r.addy[1]),','))[1],(regexp_split_to_array(pprint_addy(r.addy[2]),','))[1]),concat_ws(',',(regexp_split_to_array(pprint_addy(r.addy[1]),','))[2],(regexp_split_to_array(pprint_addy(r.addy[1]),','))[3]) INTO colonia_name,municipio_nombre from tiger.reverse_geocode(punto,true) r; ciudad_key := '"ciudad"=>'|| quote_ident(concat_ws(',',colonia_name,municipio_nombre)) ; RETURN hstore(concat_ws(',',estado_key,ciudad_key)); ELSIF country_name = 'Mexico' THEN select st_distance(geom,punto),sett_name,mun_name,st_name INTO colonia_distance,colonia_name,municipio_nombre,estado_nombre FROM colonias ORDER BY geom <-> punto LIMIT 1; IF (colonia_distance < colonia_max_distance) THEN ciudad_key := '"ciudad"=>'||quote_ident(colonia_name||','||municipio_nombre); localidad_key := '"localidad"=>"Mi geocerca invalida"'; estado_key := '"estado"=>'||quote_ident(estado_nombre); RETURN /*If no entry in colonia try localidades urbanas, rurales */ hstore(coalesce(estado_key,'"estado"=>""')) || hstore(coalesce(ciudad_key,'"ciudad"=>""')) || hstore(localidad_key); ELSE colonia_key := '"colonia"=>""'; END IF; select st_distance(geom,punto),nom_loc INTO localidad_distance,localidad_name FROM localidades_urbanas ORDER BY geom <-> punto LIMIT 1; IF (localidad_distance < localidad_max_distance) THEN localidad_key := '"localidad"=>'||quote_ident(localidad_name); ELSE select st_distance(geom,punto),nom_loc INTO localidad_distance,localidad_name FROM localidades_rurales ORDER BY geom <-> punto LIMIT 1; IF (localidad_distance < localidad_max_distance) THEN localidad_key := '"localidad"=>'||quote_ident(localidad_name); ELSE localidad_key := '"localidad"=>""'; END IF; END IF; SELECT st_distance(geom,punto),nom_mun INTO municipio_distancia,municipio_nombre FROM municipios ORDER by geom <-> punto limit 1; IF (municipio_distancia < municipio_max_distance) THEN municipio_key := '"municipio"=>'||quote_ident(municipio_nombre); ELSE municipio_key := '"municipio"=>""'; END IF; SELECT '"estado"=>'||quote_ident(nom_ent) INTO estado_key FROM estados WHERE st_intersects(geom,punto); ciudad_key := '"ciudad"=>'||quote_ident(localidad_name||','||municipio_nombre); RETURN hstore(coalesce(estado_key,'"estado"=>""')) || hstore('"localidad"=>"Mi geocerca"') || hstore(coalesce(ciudad_key,'"ciudad"=>""')); ELSE RAISE WARNING 'We have no data for %',country_name; RETURN hstore('estado','ND') || hstore('ciudad','ND') || hstore('localidad','ND'); END IF; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS var1 = MESSAGE_TEXT, var2 = PG_EXCEPTION_DETAIL, var3 = PG_EXCEPTION_HINT; RAISE WARNING '% % %',var1,var2,var3; RETURN hstore('estado','ND') || hstore('ciudad','ND') || hstore('localidad','ND'); END;