[GIS] find lat,long inside polygon with regular interval of time using thesql

geofencingMySQL

I have table latest location there i have lat and long with device id.
one more table with fencing name,there i have polygon points.
so now how i can check point lies inside polygon.
i need this with regular interval of time and storing that to new table.
so how to find this with MySQL function,event or with procedure?

Best Answer

Create a trigger for your location table: http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html and inside the trigger function use st_within

Related Solutions

MySQL GIS – Finding Features Within Given Coordinates and Distance Using MySQL

Mapperz's answer is invalid. Sinus must be calculated from latitude and NOT from longitude. So corect SQL statement is:

SELECT
    id, (
      3959 * acos (
      cos ( radians(78.3232) )
      * cos( radians( lat ) )
      * cos( radians( lng ) - radians(65.3234) )
      + sin ( radians(78.3232) )
      * sin( radians( lat ) )
    )
) AS distance
FROM markers
HAVING distance < 30
ORDER BY distance
LIMIT 0 , 20;

[GIS] Using MySQL with GeoServer

You would need to use the MySQL spatial functions to cast your WKT or other attributes(such as float X/Y) to geometry objects, then point geoserver(with mysql plugins installed) to those tables and have geoserver render them.

Something like:

UPDATE myPoints SET the_geom = GeomFromText('POINT(long ,lat)'));

You can refer to this question for a more complete answer for adding a geom column and updating it with whatever geographic information you have now.

Best Answer

Related Solutions

MySQL GIS – Finding Features Within Given Coordinates and Distance Using MySQL

[GIS] Using MySQL with GeoServer

Related Question