0
197
ViewsUna consulta SQL que enumerará todas las rutas, caídas de una coordenada o rutas más cercanas a la coordenada
Tengo una tabla de PostgreSQL llamada "rutas" en PostgreSQL con la estructura y los datos a continuación. Quiero una consulta donde proporcionaré la coordenada (Latitud y Longitud) y devolverá una ruta más cercana al punto de inicio al punto final de la ruta. por ejemplo, (40.690503, -73.840581) cae a lo largo de la Ruta del Atlántico, lo que significa que la consulta devolverá las primeras filas
NB: Una ruta es un camino o camino entre el punto de inicio y el punto final.
A continuación se muestra la estructura de la tabla y los datos de muestra.
#Table Structure CREATE TABLE public.route ( name text NOT NULL, startpoint point NOT NULL, endpoint point NOT NULL, id integer NOT NULL DEFAULT nextval('route_id_seq'::regclass), CONSTRAINT route_pkey PRIMARY KEY (id) ) WITH ( OIDS=FALSE ); #Table Data INSERT INTO public.route (name, startpoint, endpoint, id) VALUES ('Atlantic', (-73.848838,40.688299), (-73.824869,40.694831), 1); INSERT INTO public.route (name, startpoint, endpoint, id) VALUES ('Guy Brewer', (-73.7991,40.708257), (-73.78543,40.688334), 2);1 answers
Answer question0
En postgis, una geometría LINESTRING es algo como esto:
CREATE TABLE map.vzla_rto ( link_id bigint, geom geometry(LineString) ) SELECT ST_ASTEXT(geom) FROM map.vzla_rto; "LINESTRING(-72.285868 10.291798, -72.285604 10.291983, -72.285272 10.292124, -72.28512 10.292168, -72.284727 10.292228)" Aquí uso el operador <-> postgis para encontrar el objeto de carretera más cercano al punto. Luego use la función ST_Distance para saber cuál es la distancia a la carretera más cercana y seleccione la más cercana.
CREATE OR REPLACE FUNCTION map.get_near_link( x numeric, y numeric) RETURNS map.get_near_link AS $BODY$ DECLARE strPoint text; sRow map.get_near_link; -- custom type to return all link fields BEGIN strPoint = 'POINT('|| X || ' ' || Y || ')'; with index_query as ( SELECT Link_ID, TRUNC(ST_Distance(ST_GeomFromText(strPoint,4326), geom )*100000)::integer as distance, geom FROM map.vzla_seg S ORDER BY geom <-> ST_GeomFromText(strPoint, 4326) LIMIT 101 ) SELECT i.Link_ID, i.Distance, i.geom into sRow FROM index_query i ORDER BY distance limit 1; RAISE DEBUG 'GetLink distance % ', sRow.distance; if sRow.distance > 50 then sRow.link_id = -1; end if; RETURN sRow; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100;