Optimising latitude and longitude selection query I have table as follows the schema below. 

CREATE TABLE `gdata` ( 
`alarmTypeID` tinyint(4) NOT NULL DEFAULT '0', 
`fleetID` smallint(11) NOT NULL, 
`fleetGroupID` smallint(11) DEFAULT NULL, 
`fleetSubGroupID` smallint(11) DEFAULT NULL, 
`deviceID` mediumint(11) NOT NULL, 
`vehicleID` mediumint(11) NOT NULL, 
`gDateTime` datetime NOT NULL, 
`insertDateTime` datetime NOT NULL, 
`latitude` float NOT NULL, 
`longitude` float NOT NULL, 
`speed` smallint(11) NOT NULL, 
(see full text) 
ALTER TABLE `gdata` 
ADD PRIMARY KEY (`vehicleID`,`gDateTime`,`alarmTypeID`), 
ADD KEY `gDateTime` (`gDateTime`), 
ADD KEY `fleetID` (`fleetID`,`vehicleID`,`gDateTime`); 
COMMIT; 


My issue now is that I can run a query as below EXPLAIN select vehicleID,latitude,longitude from gdata where  gDateTime between '2017-07-03 00:00:00' and '2017-07-04 00:00:00'
and the results of the explains is 

1   SIMPLE  gdata       range   gDateTime   gDateTime   5       1251    100.00  Using index condition   


I have the following function which works to test if the a latitude and longitude is withing a rectangle.

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`root`@`localhost`
    FUNCTION `geoProcessor`(pt POINT, mp MULTIPOLYGON)
        RETURNS int(1)
        DETERMINISTIC
BEGIN 

DECLARE str, xy TEXT; 
DECLARE x, y, p1x, p1y, p2x, p2y, m, xinters DECIMAL(16, 13) DEFAULT 0; 
DECLARE counter INT DEFAULT 0; 
DECLARE p, pb, pe INT DEFAULT 0; 

SELECT MBRWithin(pt, mp) INTO p; 
IF p != 1 OR ISNULL(p) THEN 
RETURN p; 
END IF; 

SELECT X(pt), Y(pt), ASTEXT(mp) INTO x, y, str; 
SET str = REPLACE(str, 'POLYGON((',''); 
SET str = REPLACE(str, '))', ''); 
SET str = CONCAT(str, ','); 

SET pb = 1; 
SET pe = LOCATE(',', str); 
SET xy = SUBSTRING(str, pb, pe - pb); 
SET p = INSTR(xy, ' '); 
SET p1x = SUBSTRING(xy, 1, p - 1); 
SET p1y = SUBSTRING(xy, p + 1); 
SET str = CONCAT(str, xy, ','); 

WHILE pe &gt; 0 DO 
SET xy = SUBSTRING(str, pb, pe - pb); 
SET p = INSTR(xy, ' '); 
SET p2x = SUBSTRING(xy, 1, p - 1); 
SET p2y = SUBSTRING(xy, p + 1); 
IF p1y &lt; p2y THEN SET m = p1y; ELSE SET m = p2y; END IF; 
IF y &gt; m THEN 
IF p1y &gt; p2y THEN SET m = p1y; ELSE SET m = p2y; END IF; 
IF y &lt;= m THEN 
IF p1x &gt; p2x THEN SET m = p1x; ELSE SET m = p2x; END IF; 
IF x &lt;= m THEN 
IF p1y != p2y THEN 
SET xinters = (y - p1y) * (p2x - p1x) / (p2y - p1y) + p1x; 
END IF; 
IF p1x = p2x OR x &lt;= xinters THEN 
SET counter = counter + 1; 
END IF; 
END IF; 
END IF; 
END IF; 
SET p1x = p2x; 
SET p1y = p2y; 
SET pb = pe + 1; 
SET pe = LOCATE(',', str, pb); 
END WHILE; 

RETURN counter % 2; 

END$$

DELIMITER ;


My issue now I want to find out which vehicle is within a rectangle within a time range value. I have to run two separate queries to first run the first queries get the lat, long value then test if its within the rectangle using the second query. How to opmtimise this?
To find which vehicle(s) are in a specific 'rectangle' ("bounding box") during a particular day,

SELECT ...
    FROM gdata
    WHERE gDateTime &gt;= '2017-07-03'
      AND gDateTime  &lt; '2017-07-03' + INTERVAL 1 DAY
      AND latitude &gt; 34.567
      AND latitude &lt; 35.678
      AND longitude &gt; 123.456
      AND longitude &lt; 124.567;


The optimizer cannot do a perfect job of optimizing the query, but you can give it 3 choices and hope that it picks the best:

INDEX(gDateTime),
INDEX(latitude),
INDEX(longitude)


Since each column is used in a "range", there is no advantage in putting two columns in a single index.  (Only the first column will be used.)

Or, with SPATIAL index...

If it is a polygon, not a simple rectangle, then something like

SELECT ...
    FROM gdata
    WHERE gDateTime &gt;= '2017-07-03'
      AND gDateTime  &lt; '2017-07-03' + INTERVAL 1 DAY
      AND MBRWithin(pt, ...);


with


pt being an extra column containing a POINT
SPATIAL(pt)
the '...' being a pre-calculated polygon from somewhere else


Do not call your FUNCTION in the WHERE clause; that will be terribly inefficient.

Since you only want a list of vehicleID, not all the locations, this is probably better:

SELECT DISTINCT vehicleID
    FROM gdata
    WHERE ...


DISTINCT is sort of a degenerate case of GROUP BY.  I prefer to use DISTINCT in this situation since it makes it clearer what is going on.

Partitioning?

PARTITION BY RANGE(TO_DAYS(gDateTime)) with 20-50 partitions may help performance, especially if the table will be billions of rows long.  The partitioning would narrow the focus to one (maybe more) partition, then do the spatial (or lat/lng) lookup within just that partition.

Also, partitioning is an excellent way to purge 'old' data.

Use cases 1 and 2 of my partition blog discuss those two ideas.