maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_mexico_carreteras(real, real)
Parameters
Name
Type
Mode
longitude
real
IN
latitude
real
IN
distance
double precision
OUT
descr
character varying
OUT
Definition
/* Determinar cuál es la carretera en dónde se encuentra la unidad */ WITH q1 as (SELECT 'Carretera de Terracería '||' '|| numero||' '|| tipo as descr, st_distance((st_setsrid(st_makepoint($1,$2),4326)),st_closestpoint(geom,(st_setsrid(st_makepoint( $1, $2),4326)))) AS distance FROM geo.mexico_carretera_de_terraceria order by geom <-> (st_setsrid(st_makepoint($1,$2),4326)) limit 1), q2 as (SELECT 'Carretera Estatal de Cuota '||' '|| numero||' '||tipo as descr, st_distance((st_setsrid(st_makepoint($1,$2),4326)),st_closestpoint(geom,(st_setsrid(st_makepoint( $1, $2),4326)))) AS distance FROM geo.mexico_carretera_estatal_de_cuota order by geom <-> (st_setsrid(st_makepoint($1,$2),4326)) limit 1), q3 as (SELECT 'Carretera Estatal Libre '||' '|| numero||' '||tipo as descr, st_distance((st_setsrid(st_makepoint($1,$2),4326)),st_closestpoint(geom,st_setsrid(st_makepoint( $1, $2),4326))) AS distance FROM geo.mexico_carretera_estatal_libre order by geom <-> (st_setsrid(st_makepoint($1,$2),4326)) limit 1), q4 as (SELECT 'Carretera Federal de Cuota '||' '|| numero||' '|| tipo as descr, st_distance((st_setsrid(st_makepoint($1,$2),4326)),st_closestpoint(geom,(st_setsrid(st_makepoint( $1, $2),4326)))) AS distance FROM geo.mexico_carretera_federal_de_cuota order by geom <-> (st_setsrid(st_makepoint($1,$2),4326)) limit 1), q5 as (SELECT 'Carretera Federal Libre '||' '|| numero||' '|| tipo as descr, st_distance((st_setsrid(st_makepoint($1,$2),4326)),st_closestpoint(geom,(st_setsrid(st_makepoint( $1, $2),4326)))) AS distance FROM geo.mexico_carretera_federal_libre order by geom <-> (st_setsrid(st_makepoint($1,$2),4326)) limit 1), q6 as (SELECT * FROM q1 UNION SELECT * FROM q2 UNION SELECT * FROM q3 UNION SELECT * FROM q4 UNION SELECT * FROM q5) SELECT distance,descr FROM q6 ORDER BY distance LIMIT 1;