A SQL query that will list all the routes, a coordinate falls or closest routes to the coordinate

Question

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);

Show us db schema, sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. , Show us your route structure. I guess you have some geometry object route? — Juan Carlos Oropeza, Apr 21 '17 at 14:45
What is a route? Just the straight line between two points ? — Juan Carlos Oropeza, Apr 21 '17 at 15:08
@JuanCarlosOropeza a route is a way or path between two points. it can be a straight line and it might not — Ayeni Olusegun, Apr 21 '17 at 15:14
Well then you need a `LINESTRING` geometry instead two point. Otherwise how can you compare if a point is close to a route? — Juan Carlos Oropeza, Apr 21 '17 at 15:21

score 0 · Accepted Answer · answered Apr 21 '17 at 15:28

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;

A SQL query that will list all the routes, a coordinate falls or closest routes to the coordinate

1 Answers1

Linked