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, ''
  • Welcome to GIS.SE. This is a fairly standard (buffer linestring, then do point in polygon) operation. Does it have to be mysql? What have you already tried? – BradHards Dec 3 '14 at 9:58
  • yes i tried this but i am stuck there because this is new for me.and i am use mysql to do this – Ashish Srivastava Dec 3 '14 at 10:05
  • Maybe you can tell us what you've already done. Can you create the buffer for your linestring as a polygon geometry (say in another table)? Do you know how to do a point in polygon test? – BradHards Dec 3 '14 at 10:07
  • i m create a table which is store my linestring and my line string is longitude and latitude no i dont know about buffer – Ashish Srivastava Dec 3 '14 at 10:10
  • can i convert a linestring to polygon – Ashish Srivastava Dec 3 '14 at 10:17

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.