How to index this MySQL table for use with GEO data?

My table currently looks like this:

CREATE TABLE IF NOT EXISTS `x_geodata` (
  `post_id` int(11) NOT NULL,
  `post_type` varchar(20) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY  (`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

How to index this table correctly and efficiently?

The only query that runs in this table is as follows:

if(!empty($_SESSION['s_property_radius'])) {$dist = $_SESSION['s_property_radius'];}else{$dist = 50;}
$orig_lat = $_SESSION['s_property_address_lat'];
$orig_lon = $_SESSION['s_property_address_lng'];
$lon1 = $orig_lon - $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lon2 = $orig_lon + $dist / abs( cos( deg2rad( $orig_lat ) ) * 69 );
$lat1 = $orig_lat - ( $dist / 69 );
$lat2 = $orig_lat + ( $dist / 69 );


$sql = "

SELECT `t`.`post_id`, 3956 * 2 * ASIN( SQRT( POWER( SIN( ( ".$orig_lat." - `t`.`lat` ) * pi() / 180 / 2), 2 ) + COS( ".$orig_lat." * pi() / 180) * COS( `t`.`lat` * pi() / 180 ) * POWER( SIN( ( ".$orig_lon." - `t`.`lng` ) * pi() / 180 / 2 ), 2 ) ) ) AS `distance` FROM (
    SELECT `post_id`, `lat`, `lng` FROM `x_geodata` WHERE `post_type` = 'some post type' AND `lng` BETWEEN '".$lon1."' AND '".$lon2."' AND `lat` BETWEEN '".$lat1."' AND '".$lat2."'
) AS `t` HAVING `distance` <= ".$dist."

";

The request is checked to make sure that we are looking at the correct message type, and then we will check the square radius on lat and lng. The results are then performed by checking the circular radius.

What I'm looking for is updating CREATE TABLE SQL or UPDATE TABLE SQL to get it INDEXED correctly.

Any help is greatly appreciated.

EDIT: Was there EXPLAIN in my request based on arnep response, and I got the following:

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    PRIMARY    <derived2>    ALL    NULL    NULL    NULL    NULL    3   
2    DERIVED    zch_geodatastore    range    post_type    post_type    70    NULL    3    Using where

I don't know what that means, though ...

+3
source share
2

SELECT post_type, lat lon WHERE, .

EXPLAIN [QUERY], , .

+1

MySQL SPATIAL MySQL.
.: http://dev.mysql.com/tech-resources/articles/4.1/gis-with-mysql.html

: http://dev.mysql.com/doc/refman/5.1/en/create-index.html

MyISAM, InnoDB, NDB ARCHIVE , (POINT GEOMETRY. ( 11.17, " " ) .)

:
• MyISAM. SPATIAL INDEX .
• NOT NULL.
• MySQL 5.1 . .

, https://gis.stackexchange.com/ ,
() .

+2

All Articles