[GIS] How to find a point on linestring and in a range of 10 meter

linestringMySQLpointspatial-database

i have a linestring which is show in diagram and i have a point which is some time on the linestring or out of linestring but in range which is red line and some time its is out of range so how can i find when its on route and out of route in mysql spatial index

enter image description here

MY project is this as you can see bus no 26 is out off route so how can i get the bus is out off route

enter image description here

i think there is two things to do this

  1. convert linestring into a polygon with range and find point in a polygon.
  2. Point convert into polygon and check linestring is intersect the polygon.

so tell how can i do that

my database structure is 

'id', 'int(11)', 'NO', 'PRI', NULL, 'auto_increment'
'route_name', 'varchar(45)', 'YES', '', NULL, ''
'from_id', 'varchar(45)', 'YES', '', NULL, ''
'to_id', 'varchar(45)', 'YES', '', NULL, ''
'line_string', 'geometry', 'NO', 'MUL', NULL, ''

Best Answer

You can create the polygon using Buffer.

Note that the distance is in the units of the coordinate reference system you're using (which is probably degrees since you said its latitude and longitude). So if you want a 10 metre buffer, you probably need to project it (or be willing to accept some inaccuracy in the buffer size if you just want to convert 10 metres to degrees). You can't do the projection inside mysql - it isn't that smart. Other databases (e.g. spatialite or postgis) can do this with ST_Transform. The inaccuracy in trying to do it in degrees will depend on how many degrees of latitude vs degrees of longitude 10m is for your working location.

Once you have that, you can do ST_Disjoint to select any points that aren't within a polygon.

Related Question