0
193
ViewsA SQL query that will list all the routes, a coordinate falls or closest routes to the coordinate
I have a PostgreSQL table called "routes" in PostgreSQL with below structure and data. I want a query where i will provide the coordinate(Latitude and Longitude) and it will return a route closest to the startpoint to endpoint of the route. for example, (40.690503, -73.840581) falls along Atlantic Route which means the query will return the first rows
NB: A route is a path or way between startpoint and endpoint.
Below is the table structure and Sample Data
#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);
·
Santiago Trujillo
1 answers
Answer question0
In postgis a LINESTRING geometry is something like this:
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)"
Here I use the <-> postgis operator to find the closest road object to the point. Then use ST_Distance function to know what is the distance to that closest road and select the closest one.
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;
·
Santiago Trujillo
Report
Answer question