SQL - Compare route coordinates

I have route's longitudes and latitudes and there is a table(route_info) which has columns as feature_id, route_id, coordinates, region

feature_id	route_id	coordinates	region
43829103	5467	[[long,lat],[long,lat]....[long,lat]]	NA

Now I want to write a sql query which will return only rows whose coordinates are either crossing, overlapping or touching the route's longitude and latitude.

I tried:

How to formulate a SQL query to identify sets of matches across a table

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

and few other stack answers...

Can anyone please help me to write the sql query?

Solution

tldr

It is possible to detecting route overlaps and intersections using direct 2D geometric line algorithms, however depending on the scale of your route and the accuracy of the data 2D geometry may miss many physical intersections between points or it may miss lines that are very close to intersecting, like either side of a wide roadway, but do not actually overlap.

To deal with the first issue, we use Spatial data types and Geography based algorithms.
The second issue we resolve by expanding the route with a buffer zone around the line, this is effectively applying an extended radius around all the edges of the shape.

Once the data has been translated into polygons, we can use standard intersect functions to determine if shapes intersect with or are entirely contained within the input.

This image explains what we want to detect, the light purple shape is the input the green and the darker purple shape show the two route_infos in the database, the final solution should select out the darker shape, as it is contained within the input:

A spatial solution without using a buffer zone around the route:

SELECT  feature_id, route_id, RouteLine
FROM @route_info
-- transpose into WKT
CROSS APPLY (SELECT WKT = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(coordinates, '[[', '('), ']]',')'),'],[','~ '),',', ' '),'~',',')) as Points
CROSS APPLY (SELECT RouteLine = geography::STGeomFromText('LINESTRING ' + Points.WKT,4326)) as Shapes
WHERE @inputPolygon.STIntersects(RouteLine) = 1

Prepare the Input:

Ideally for comparison, given that we are comparing to a column called coordinates you should first pre-process your input so that it is either the same type and structure as coordinates or the same type as what we will be transforming the coordinates into.

The following solution is implemented using MS SQL syntax, given that most RDBMS implement similar spatial functions you should be able to translate or transpose the concepts to your RDBMS of choice.

MS SQL doesn't have an array column type, so for this solution we will interpret coordinates as a string of comma delimited ordinate pairs similar to GeoJSON, lets massage the input into the same string:

DECLARE @input varchar(max) = '[[-122.15563,47.67868],[-122.15561,47.67832],[-122.15561,47.67823],[-122.1556,47.67814],[-122.1556,47.67796],[-122.15558,47.67762],[-122.15558,47.67742],[-122.15556,47.67724],[-122.15629,47.67748],[-122.15653,47.67755],[-122.15701,47.67771],[-122.15749,47.67785],[-122.15754,47.67786],[-122.1611,47.67903],[-122.1614,47.67914],[-122.16214,47.67937],[-122.16234,47.67942],[-122.16242,47.67943],[-122.16259,47.67947],[-122.1627,47.67948],[-122.16281,47.6795],[-122.1631,47.67953],[-122.16391,47.67956],[-122.16409,47.6795],[-122.16681,47.67946],[-122.16699,47.67945],[-122.16871,47.67943],[-122.16883,47.67942],[-122.17017,47.67941],[-122.17036,47.6794],[-122.17096,47.6794],[-122.17214,47.67937],[-122.17219,47.67938],[-122.17229,47.67941],[-122.17275,47.67942],[-122.17297,47.67941],[-122.17332,47.67941],[-122.17339,47.6794],[-122.17712,47.67935],[-122.17759,47.67933],[-122.18236,47.67928],[-122.18247,47.67927],[-122.18517,47.67922],[-122.18547,47.67927],[-122.18558,47.67928],[-122.18569,47.6793],[-122.18573,47.6793],[-122.18577,47.67932],[-122.18582,47.67933],[-122.18592,47.67937],[-122.18607,47.67946],[-122.18612,47.6795],[-122.18616,47.67954],[-122.18625,47.67967],[-122.18626,47.6797],[-122.18626,47.67971],[-122.18628,47.67976],[-122.18629,47.67983],[-122.18629,47.6799],[-122.18628,47.67995],[-122.18624,47.68006],[-122.18615,47.68018],[-122.1861,47.68023],[-122.18597,47.68032],[-122.18586,47.68036],[-122.18582,47.68037],[-122.18577,47.68039],[-122.18572,47.68039],[-122.18566,47.6804],[-122.18551,47.6804],[-122.18532,47.68037],[-122.18526,47.68034],[-122.18521,47.68032],[-122.18513,47.68027],[-122.18507,47.68022],[-122.185,47.68015   ],[-122.18496,47.68004],[-122.18496,47.67998],[-122.18495,47.67985],[-122.18495,47.67978],[-122.18492,47.67964],[-122.18495,47.67887],[-122.18492,47.67863],[-122.18489,47.67742],[-122.1849,47.67711],[-122.18491,47.67705],[-122.18492,47.6769],[-122.18495,47.67666],[-122.185,47.67641  ],[-122.18531,47.67536],[-122.18587,47.67415],[-122.18589,47.67401],[-122.18589,47.67392],[-122.18586,47.67382],[-122.18591,47.67375],[-122.18658,47.67242],[-122.18674,47.67206],[-122.18689,47.67167],[-122.18703,47.67113],[-122.18709,47.67085],[-122.18713,47.67061],[-122.18717,47.67018],[-122.18717,47.66626],[-122.18718,47.66607],[-122.18718,47.66322],[-122.18716,47.66231],[-122.18717,47.66223],[-122.18719,47.66014],[-122.18721,47.65954],[-122.18721,47.65135],[-122.18722,47.65096],[-122.18721,47.649    ],[-122.18723,47.64864],[-122.18721,47.64782],[-122.18721,47.64653],[-122.1872,47.64636],[-122.18719,47.64586],[-122.18714,47.64515],[-122.18707,47.64447],[-122.18701,47.64403],[-122.18698,47.6437],[-122.18682,47.64234],[-122.18679,47.64201],[-122.18674,47.64121],[-122.18673,47.64086],[-122.18673,47.64009],[-122.18674,47.63998],[-122.18678,47.6386],[-122.18678,47.63765],[-122.18681,47.63763],[-122.18685,47.63759],[-122.18688,47.63751],[-122.18695,47.63681],[-122.18699,47.63655],[-122.18711,47.63609],[-122.18724,47.63575],[-122.18729,47.63568],[-122.18737,47.63553],[-122.18769,47.63483],[-122.18783,47.63449],[-122.18785,47.63443],[-122.1879,47.63434],[-122.18803,47.63421],[-122.1881,47.63417],[-122.18814,47.63414],[-122.18834,47.63405],[-122.18861,47.63398],[-122.18877,47.63396],[-122.18914,47.63396],[-122.18925,47.63397],[-122.18933,47.63399],[-122.18942,47.634   ],[-122.18951,47.63402],[-122.1896,47.63405],[-122.1898,47.63414],[-122.18989,47.63419],[-122.19029,47.63445],[-122.19049,47.63455],[-122.19092,47.63486],[-122.19099,47.63492],[-122.19104,47.63498],[-122.19111,47.63505],[-122.19126,47.63527],[-122.19132,47.63531],[-122.19141,47.63536],[-122.19156,47.6356],[-122.19188,47.63605],[-122.1919,47.63607],[-122.19199,47.63619],[-122.1923,47.63656],[-122.19251,47.63679],[-122.19288,47.63715],[-122.19325,47.63749],[-122.19333,47.63754],[-122.19341,47.6376],[-122.19514,47.63919],[-122.19566,47.63963],[-122.19632,47.64015],[-122.19672,47.64042],[-122.19717,47.6407],[-122.19723,47.64073],[-122.19742,47.64084],[-122.19742,47.64085],[-122.19809,47.64121],[-122.19854,47.64143],[-122.19923,47.64174],[-122.19981,47.64196],[-122.20051,47.64219],[-122.20101,47.64233],[-122.20156,47.64246],[-122.20174,47.64249],[-122.20192,47.64253],[-122.20226,47.64259],[-122.20242,47.64261],[-122.20264,47.64265],[-122.204,47.64282 ],[-122.20444,47.64286],[-122.20617,47.64308],[-122.20638,47.6431],[-122.20709,47.64314],[-122.20732,47.64314],[-122.20751,47.64315],[-122.20813,47.64314],[-122.20875,47.6431],[-122.20902,47.64307],[-122.20967,47.64297],[-122.20999,47.64291],[-122.21058,47.64277],[-122.21121,47.64259],[-122.21287,47.64208],[-122.21316,47.642  ],[-122.2134,47.64192],[-122.21349,47.64192],[-122.21354,47.64191],[-122.2136,47.64191],[-122.21374,47.64188],[-122.21385,47.64185],[-122.21399,47.64182],[-122.21453,47.64167],[-122.2157,47.64138],[-122.21593,47.64131],[-122.21614,47.64126],[-122.21708,47.64099],[-122.21714,47.64098],[-122.21719,47.64098],[-122.21727,47.641   ],[-122.21733,47.64104],[-122.21738,47.64109],[-122.21738,47.64111],[-122.21739,47.64112],[-122.21739,47.64113],[-122.21741,47.64115],[-122.21742,47.64115],[-122.21742,47.64116],[-122.21743,47.64117],[-122.21744,47.64117],[-122.21745,47.64118],[-122.21746,47.64118],[-122.21747,47.64119],[-122.21749,47.64119],[-122.2175,47.6412    ],[-122.21764,47.6412],[-122.21765,47.64119],[-122.21766,47.64119],[-122.21767,47.64118],[-122.21769,47.64118],[-122.2177,47.64117],[-122.21771,47.64117],[-122.21772,47.64116],[-122.21772,47.64115],[-122.21773,47.64115],[-122.21775,47.64113],[-122.21775,47.64112],[-122.21776,47.64111],[-122.21776,47.64103],[-122.21775,47.64103],[-122.21775,47.64102],[-122.21771,47.64098],[-122.2177,47.64098],[-122.21769,47.64097],[-122.21768,47.64097],[-122.21767,47.64096],[-122.21766,47.64096],[-122.21741,47.64071],[-122.21736,47.64064],[-122.21735,47.64059],[-122.21733,47.64055],[-122.21733,47.64043],[-122.21735,47.64035],[-122.21737,47.64029],[-122.21743,47.64018],[-122.21756,47.63998],[-122.2176,47.63989],[-122.21765,47.6398],[-122.21769,47.6397],[-122.2177,47.63965],[-122.21769,47.63926],[-122.21768,47.6392],[-122.21769,47.63912],[-122.21772,47.63829],[-122.21773,47.63739],[-122.21774,47.63721],[-122.21774,47.63711],[-122.21776,47.63691],[-122.21776,47.63621],[-122.21732,47.63618],[-122.21722,47.63616],[-122.21719,47.63618],[-122.21714,47.63623],[-122.21711,47.63628],[-122.21693,47.63664],[-122.21685,47.63677],[-122.21678,47.63684],[-122.21677,47.63684],[-122.21675,47.63686],[-122.21669,47.6369],[-122.21662,47.63692],[-122.21655,47.63695],[-122.21642,47.63698],[-122.21507,47.63701],[-122.21483,47.63698],[-122.21478,47.63696],[-122.21474,47.63695],[-122.21456,47.63688],[-122.21451,47.63684],[-122.2144,47.63673],[-122.21422,47.6368],[-122.21411,47.63685],[-122.21375,47.63693],[-122.21361,47.63695],[-122.21321,47.63697],[-122.21236,47.63697],[-122.21236,47.63651],[-122.2097,47.63652],[-122.20944,47.63653],[-122.20917,47.63653],[-122.20898,47.63654],[-122.20837,47.63654],[-122.20817,47.63655],[-122.20754,47.63655],[-122.2073,47.63654],[-122.20703,47.63654],[-122.20704,47.63745],[-122.20703,47.63789],[-122.20678,47.63792],[-122.20663,47.63792],[-122.20653,47.63791],[-122.20644,47.63791],[-122.20619,47.63786],[-122.20614,47.63784],[-122.20571,47.63762],[-122.20557,47.63756],[-122.20555,47.63756],[-122.20553,47.63755],[-122.20548,47.63755],[-122.20546,47.63754],[-122.20542,47.63754],[-122.20539,47.63755],[-122.20532,47.63755],[-122.20531,47.63756],[-122.20529,47.63757],[-122.20524,47.63758],[-122.20488,47.63774],[-122.20485,47.63776],[-122.20483,47.63776],[-122.2048,47.63777],[-122.20466,47.6378],[-122.20458,47.63781],[-122.20456,47.63782],[-122.2041,47.63784],[-122.204,47.63785  ],[-122.20374,47.63789],[-122.20355,47.63796],[-122.20337,47.63804],[-122.20302,47.63803],[-122.20281,47.63793],[-122.20279,47.63793],[-122.20278,47.63792],[-122.20256,47.63788],[-122.20252,47.63788],[-122.20246,47.63787],[-122.2024,47.63787],[-122.20229,47.63786],[-122.20158,47.63786],[-122.20157,47.6383],[-122.20154,47.63859],[-122.20149,47.63886],[-122.20135,47.63943],[-122.20113,47.64013],[-122.20103,47.64053],[-122.20103,47.64064],[-122.20102,47.64068],[-122.20102,47.64107],[-122.20108,47.64136],[-122.20118,47.64165],[-122.20143,47.64228],[-122.20153,47.64257],[-122.20158,47.6428],[-122.20156,47.64292],[-122.20162,47.64338],[-122.20163,47.64369],[-122.20163,47.64413],[-122.20162,47.64427],[-122.20163,47.6444],[-122.20163,47.64469],[-122.20164,47.64473],[-122.20168,47.64479],[-122.20169,47.64501],[-122.20172,47.6453],[-122.20172,47.64564],[-122.20178,47.6459],[-122.20182,47.64603],[-122.20188,47.64619],[-122.20216,47.64667],[-122.20263,47.64725],[-122.20275,47.64738],[-122.20316,47.64788],[-122.20342,47.64818],[-122.20346,47.64824],[-122.2035,47.64829],[-122.20352,47.64834],[-122.20355,47.64838],[-122.20366,47.64861],[-122.20367,47.64866],[-122.20369,47.64871],[-122.20372,47.64884],[-122.20379,47.64933],[-122.20385,47.64986],[-122.20386,47.64989],[-122.20391,47.65034],[-122.20393,47.65045],[-122.20395,47.65052],[-122.20396,47.65057],[-122.204,47.65068   ],[-122.20409,47.65086],[-122.20413,47.65097],[-122.20417,47.65104],[-122.20424,47.65127],[-122.20429,47.65158],[-122.20446,47.65323],[-122.20448,47.65334],[-122.20451,47.65368],[-122.20453,47.65376],[-122.20455,47.65403],[-122.20463,47.65471],[-122.20467,47.6552],[-122.20466,47.65531],[-122.20466,47.65541],[-122.20469,47.65581],[-122.20477,47.65644],[-122.20485,47.65669],[-122.20496,47.6569],[-122.20503,47.65701],[-122.20515,47.65715],[-122.20548,47.65745],[-122.20565,47.65764],[-122.20576,47.65782],[-122.20582,47.65795],[-122.20604,47.65887],[-122.20605,47.65894],[-122.20608,47.65904],[-122.20629,47.65992],[-122.2065,47.66102],[-122.20703,47.663],[-122.20712,47.66345],[-122.20713,47.66356],[-122.20716,47.66374],[-122.20718,47.66408],[-122.20718,47.66444],[-122.20717,47.66474],[-122.20715,47.66492],[-122.20711,47.66518],[-122.20685,47.66635],[-122.2068,47.66673],[-122.20679,47.6669],[-122.2068,47.66852],[-122.20678,47.66877],[-122.20678,47.66884],[-122.20677,47.66891],[-122.20669,47.66927],[-122.2065,47.66981],[-122.2064,47.67005],[-122.20637,47.6701],[-122.20634,47.67017],[-122.20628,47.67028],[-122.20617,47.67045],[-122.20586,47.67083],[-122.20576,47.67097],[-122.20569,47.67109],[-122.20566,47.67117],[-122.20562,47.67125],[-122.20557,47.67141],[-122.20555,47.6715],[-122.20554,47.67159],[-122.20552,47.67167],[-122.2055,47.67185],[-122.2055,47.67206],[-122.20546,47.67251],[-122.20545,47.6729],[-122.20547,47.67303],[-122.2055,47.67313],[-122.20559,47.67337],[-122.20593,47.6741],[-122.20608,47.67447],[-122.20658,47.67557],[-122.2068,47.67595],[-122.20713,47.67656],[-122.20617,47.67677],[-122.20609,47.67683],[-122.20418,47.67725],[-122.20401,47.67728],[-122.20356,47.67739],[-122.20346,47.6774],[-122.20299,47.67752],[-122.20202,47.6778],[-122.20175,47.67789],[-122.201,47.67811],[-122.19963,47.67855],[-122.19952,47.67858],[-122.19891,47.67878],[-122.19869,47.67881],[-122.19773,47.67908],[-122.19744,47.67915],[-122.197,47.67922],[-122.19645,47.67929],[-122.19564,47.67935],[-122.19457,47.67939],[-122.19435,47.67939],[-122.19431,47.6794],[-122.19405,47.67943],[-122.19205,47.67945],[-122.19166,47.67944],[-122.19152,47.67943],[-122.19133,47.67943],[-122.19005,47.67935],[-122.18995,47.67935],[-122.1897,47.67933],[-122.18962,47.67929],[-122.18781,47.67917],[-122.18676,47.67912],[-122.17829,47.67924],[-122.17711,47.67923],[-122.17342,47.67932],[-122.17229,47.67933],[-122.17219,47.67938],[-122.17214,47.67937],[-122.17096,47.6794],[-122.17036,47.6794],[-122.17017,47.67941],[-122.16883,47.67942],[-122.16871,47.67943],[-122.16699,47.67945],[-122.16681,47.67946],[-122.16409,47.6795],[-122.16391,47.67945],[-122.16348,47.67946],[-122.16335,47.67945],[-122.16324,47.67945],[-122.16273,47.6794],[-122.16244,47.67935],[-122.16223,47.6793],[-122.1621,47.67926],[-122.16194,47.67922],[-122.15515,47.677],[-122.15505,47.67708],[-122.15499,47.67711],[-122.15493,47.67713],[-122.15485,47.67721],[-122.15484,47.67723],[-122.1548,47.67728],[-122.15478,47.67733],[-122.15478,47.67737],[-122.15476,47.67744],[-122.15476,47.67748],[-122.15477,47.67752],[-122.15477,47.67755],[-122.15478,47.67757],[-122.15514,47.67878],[-122.15514,47.67879],[-122.15516,47.67882],[-122.15524,47.67888],[-122.15527,47.67888],[-122.15533,47.67889],[-122.15564,47.6789],[-122.15563,47.67877],[-122.15563,47.67868]]';

Then we need to convert this into WKT syntax that MS SQL can interpret, as this is a simple poly line, this can be achieved with simple string replacement:

DECLARE @inputPoints varchar(max) = (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@input, '[[', '('), ']]',')'),'],[','~ '),',', ' '),'~',','))

We need to convert this WKT to a Geography data type using the STGeomFromText function so that we can access geospatial functions. Note here that for the sample set it is neccessary to use the MakeValid function to clean the points and make them valid.
I haven't bothered to track down the invalid points in the input set, but this function ensures we have a valid Geography typed value.

SELECT geography::STGeomFromText('LINESTRING ' + @inputPoints,4326).MakeValid()

Finally we should add the buffer zone to this line, in this case 200m was added to deal with common GPS and mapping software inaccuracies:

In MS SQL we can use the STBuffer function to expand the line into a polygon with a given radius along the outside edges of the line.

DECLARE @inputPolygon GEOGRAPHY = (SELECT geography::STGeomFromText('LINESTRING ' + @inputPoints,4326).MakeValid().STBuffer(200))

The following is a graphical representation of the input route, showing the line layered over the top of the buffer polygon:

Translate the Comparison Data

First prepare the route_info, for MS SQL we can use a table variable:

INSERT INTO @route_info (feature_id, route_id, coordinates, region)
VALUES 
(829103, 5467, '[[-122.15321853719847,47.704257161120964],[-122.15242683312687,47.704287625949426],[-122.15141115331853,47.7042659242787],[-122.151002599546,47.704239491474524],[-122.15056519354913,47.70424294443106],[-122.13656243672308,47.702561868535334],[-122.1367167103278,47.70265034379374],[-122.13680546685777,47.7026652682439],[-122.1368750582949,47.70265015370357],[-122.13702313144782,47.702580694018444],[-122.13713370249233,47.70257592730222],[-122.13720512357425,47.7026017527707],[-122.13725529640807,47.70264271957269],[-122.13729225849197,47.702707475376954],[-122.13731393561761,47.70279150335102],[-122.13730102987266,47.70289246802932],[-122.13722159699756,47.70294178092878],[-122.1364904689167,47.70294221368222],[-122.13642514781414,47.70295751571461],[-122.13637993727195,47.70299336765046],[-122.13633905254707,47.70317105588839],[-122.13625438532652,47.703359666327856],[-122.13620260073597,47.703693694687885],[-122.13619610763965,47.70392886034481],[-122.13620895687168,47.703971865864446],[-122.1362493151447,47.70400863193401],[-122.1363896904285,47.70404370360903],[-122.13970203897256,47.70408418133381],[-122.1407600289385,47.704111151624296],[-122.1426384264584,47.704111059333144],[-122.14381921122735,47.70412820672761],[-122.15301277888531,47.70431632368074],[-122.15351493064163,47.70427606229016],[-122.154307459918,47.7041156287491],[-122.1543555360334,47.70408410669192],[-122.1543757399256,47.704040152597926],[-122.15436369136117,47.70398504303041],[-122.15429004659003,47.70385569752873],[-122.15429915650301,47.70381888617188],[-122.15433814533942,47.70379881492536],[-122.15425006442979,47.70381261354941]]', 'NA'),
(6745232,326723,'[[-122.20924225131948,47.63654068853021],[-122.20959886607366,47.63653055079782],[-122.20974461687068,47.636562405529034],[-122.20978418964306,47.63655047942522],[-122.20979964243297,47.636523489048756],[-122.20978170686948,47.63649643636931],[-122.20973941694002,47.63648583784396],[-122.20957742365101,47.63650714825016],[-122.20813297490652,47.636536168929894],[-122.20748075569423,47.6365355316734],[-122.207174975492,47.636513142978096],[-122.20711380102775,47.63652772092295],[-122.20707006498203,47.636586399453115],[-122.20705138718169,47.636725649992684],[-122.20706284893252,47.63757463185574],[-122.20704951054438,47.63779018707944],[-122.20699410946716,47.637858208247394],[-122.20684382013624,47.637896365219255],[-122.20660452907042,47.63790319071946],[-122.20633108303164,47.63786570217268],[-122.20592957408878,47.637710543964204],[-122.20569931910303,47.63757262203018],[-122.20557716782696,47.63753764971581],[-122.20546128730268,47.63752515144605],[-122.20526062476505,47.63755765532653],[-122.20493418858695,47.63771227215794],[-122.2047391216572,47.637773866184816],[-122.20453932574736,47.637801463880876],[-122.20397364787053,47.637833195194226],[-122.20330033675143,47.63799764142429],[-122.20309714585967,47.6379842551224],[-122.20276767011075,47.63789392487174],[-122.20262164922863,47.63786884662702],[-122.20223427927411,47.63785874816519],[-122.20186163360707,47.637822841084486],[-122.20175206631733,47.63783995519987],[-122.20169689590504,47.637894509808234],[-122.20165566130295,47.63801576063718],[-122.20159911810678,47.63856511432317],[-122.20154414065408,47.63888786934801],[-122.20142774750526,47.639342155284794],[-122.20114261230658,47.64026520294354],[-122.20109330127268,47.640496982000336],[-122.20105933196454,47.64078151003597],[-122.20106617102937,47.64113115540817],[-122.20111831338298,47.64144853105318],[-122.20119376361536,47.64169322669468],[-122.2014888144561,47.64245752424735],[-122.20156107847986,47.64272875390125],[-122.20161975816703,47.64304741442819],[-122.20165869561491,47.64350424418403],[-122.20166679752015,47.64454553482995],[-122.20169632970385,47.645506944816425],[-122.2017156216216,47.64569422759733],[-122.20175502624184,47.64587643577226],[-122.20188110639896,47.64622871559883],[-122.20206420858327,47.6465447492725],[-122.2025276360524,47.64714725115876],[-122.20338476740731,47.6481633856046],[-122.20354489926464,47.648410193307626],[-122.2036550582623,47.64867026491107],[-122.20372093859676,47.649008423147336],[-122.20391248872879,47.650446636625254],[-122.20399631309941,47.650707231054525],[-122.2041735502516,47.651119365503284],[-122.2042413147393,47.65139907093974],[-122.20460337483819,47.65477168692145],[-122.20473954049076,47.65627131409287],[-122.20477686907459,47.65651936484553],[-122.20487024338726,47.65678795778948],[-122.20500317590171,47.6570160946326],[-122.20513676190754,47.657175625566666],[-122.20560392340805,47.65763051719512],[-122.20572791087662,47.65780612310151],[-122.20580873861486,47.65798908534502],[-122.20622107641682,47.659688009036735],[-122.20649664831528,47.66106057325448],[-122.20671870691307,47.66181878278846],[-122.20701910303578,47.66294342367798],[-122.20711937001822,47.66341887745007],[-122.20717512716497,47.66394067032207],[-122.20718341342948,47.66454865555046],[-122.2071127220163,47.66517599597976],[-122.20685379615783,47.66632632397425],[-122.20680113248211,47.66671746066995],[-122.20678556293049,47.667025592162354],[-122.20680172266444,47.668480643053144],[-122.2067521184863,47.668975064094404],[-122.20669013766285,47.66925356990899],[-122.2065719452186,47.669632717649165],[-122.20636376061138,47.67011248235685],[-122.20616177596536,47.67044335133318],[-122.20578844807633,47.67091259541408],[-122.20566997410495,47.671123126348625],[-122.20560325572075,47.67127749132394],[-122.20554602774469,47.67147855140464],[-122.20551857005424,47.671642497368886],[-122.20543763103393,47.67276419226535],[-122.20546334265991,47.67300413760042],[-122.20554937848094,47.67327378050489],[-122.20632663552921,47.67502797436773],[-122.20653661780949,47.67547834265503],[-122.20679451843698,47.675960428305196],[-122.20688078632692,47.67619368451809],[-122.20702562951813,47.67646400568915],[-122.20703281589105,47.67651630969306],[-122.20700229921094,47.676561088935436],[-122.20692222019092,47.676598249424806],[-122.20674068013304,47.676644561345384],[-122.2030600580312,47.67746018011528],[-122.20194043612139,47.67778574872449],[-122.19827504473534,47.678914970387176],[-122.19775492243565,47.67906135279002],[-122.1971054234946,47.67919073809623],[-122.19654331768065,47.679276258678776],[-122.19591556585145,47.679335226327886],[-122.19522147419596,47.67937088456765],[-122.19253748762337,47.67942059747874],[-122.19174301855095,47.67941341165099],[-122.19102604222066,47.67938292679486],[-122.18728450859103,47.67915210068492],[-122.18662946463355,47.67912936271692],[-122.18490129238687,47.679165443101375],[-122.1825531141693,47.67918003037603],[-122.17839916516029,47.679241158045464],[-122.17731386880675,47.67923442805584],[-122.17598990309797,47.679252211493036],[-122.17445951479128,47.67930063182815],[-122.16383393770029,47.67946437702741],[-122.16336807644038,47.67945594139624],[-122.1626566395718,47.6793822781886],[-122.16230692134226,47.67931158485741],[-122.16195579644311,47.67921815097597],[-122.15657491277722,47.67746781755592],[-122.15593274979878,47.67728837977593],[-122.1551477684811,47.677037140005574],[-122.15507346269982,47.6770351228692],[-122.15499839521344,47.6770684961196],[-122.15492716679834,47.67713479261665],[-122.15485694611937,47.6772342741259],[-122.15479939902391,47.67739079035412],[-122.15481300606632,47.677574376287815],[-122.15502149857777,47.67819571510843],[-122.15506408171457,47.67843488199807],[-122.15514135221858,47.678730349449985],[-122.15517445472798,47.678794511682725],[-122.15522933897338,47.678840856345246],[-122.15530725864264,47.67887176633097],[-122.15540373732499,47.678882636623555],[-122.1557625825481,47.67886517843533],[-122.15637633736208,47.67889408086449],[-122.156664673575,47.67886240968958],[-122.15678448511589,47.6788140333334],[-122.15685051574424,47.678738110117074],[-122.15687565556136,47.67861192593755],[-122.15687545960371,47.678424447466654]]','NA')

As with the input, the data within the route_info table needs to be translated into WKT and then translated into Geography representation, using the same functions used for the input.

SELECT  feature_id, route_id, RouteBuffer
FROM @route_info
-- transpose into WKT
CROSS APPLY (SELECT WKT = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(coordinates, '[[', '('), ']]',')'),'],[','~ '),',', ' '),'~',',')) as Points
-- Create the Geography Line and the Buffer Polygon from WKT
CROSS APPLY (SELECT RouteBuffer = geography::STGeomFromText('LINESTRING ' + Points.WKT,4326).STBuffer(50)) as Shapes

This produces these two polygons:

We can union this with the input parameters to show the routes all overlayed, but what we really want to do is only select the records in route_info that intersect, so for that we can use the STIntersects() function:

SELECT  feature_id, route_id, RouteBuffer
FROM @route_info
-- transpose into WKT
CROSS APPLY (SELECT WKT = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(coordinates, '[[', '('), ']]',')'),'],[','~ '),',', ' '),'~',',')) as Points
CROSS APPLY (SELECT RouteBuffer = geography::STGeomFromText('LINESTRING ' + Points.WKT,4326).STBuffer(50)) as Shapes
WHERE @inputPolygon.STIntersects(RouteBuffer) = 1

For this query I deliberately used a smaller buffer radius of 50m for the route_info to show the routes all overlayed in the image in the tldr section. but you would configure the buffering, or omit it altogether depending on your needs

This results in a single row:

Can this work without the buffer?

This particular dataset would work without the buffer as the points directly intersect, however common scenarios where routes may not intersect with high precision coordinates are where routes share the same major arterial road or highway, but using opposing sides of it, resulting in edges of the routes being parallel, but not actually meeting.

STBuffer is often used in queries where you want to find points of interest that are along side or near given routes, but where the points for the POI are not directly on the route, as is common when the route follows a motorway. It is a more efficient query to apply a buffer zone to the route once, rather than deliberately expanding all the points of the POI records to see if they overlap.

Using STBuffer has an additional benefit when comparing against larger result sets, adding an appropriate buffer can smooth out or approximate the boundry of the shape, for some types of indexing and spatial querying this can significantly improve the query as it reduces the vertices, the benefits are best achieved by storing the buffer shape in the database and applying a spatial index on that column rather than calculating it each time for every row comparison.