Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

MySQL doesn't have nearest neighbor search so. So I try to come up with a "distance" that's reasonable so the number of returned query is around 20-200.

That way innodb doesn't have to compute distance for like 30k rows if we only need 20. The latitude and longitude is also stored in a myisam table called tableauxiliary

Well, even after that, queries still take 13 seconds and sometimes 30 seconds.

SELECT TB.ID ,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong) 
  AND MATCH (TA.FullTextSearch) AGAINST ('re*' IN BOOLEAN MODE) 
AND TA.Prominent >15 
ORDER BY
  Distance

Number of matches are 32 by the way.

Showing rows 0 - 29 ( 32 total, Query took 16.1845 sec)

Also an even simpler query where we do not compute distance also take 16 seconds

SELECT TB.ID
FROM
  `tablebusiness` AS TB
  join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
  MBRContains(
        GeomFromText (
            'MULTIPOINT(-6.1928749092968 106.7651250907,-6.1771250907032 106.7808749093)'
            ),
            TA.Latlong) 
  AND MATCH (TA.FullTextSearch) AGAINST ('res*' IN BOOLEAN MODE)    
AND TA.Prominent >15 

I can understand if mysql cannot do nearest neighbor search. What I am doing now is a simple search points in a box here. What am I missing?

The explanation for the query is:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  TA  fulltext    PRIMARY,Prominent,LatLong,LatLong_2,FullTextSearch  FullTextSearch  0       1   Using where
1   SIMPLE  TB  eq_ref  PRIMARY     PRIMARY     752     gensurv_isikota.TA.BusinessID   1   Using index

It basically says only one row is computed for each of the process.

number of data is 1.6 million, but number of data in a box is only 30

share|improve this question
1  
Can you export out the data and import into a MyISAM MYSQL? then you will have spatial indexing which will speed up the entire process by many times. PostGIS would be even faster. I don't think your queries are wrong - just needs to be a spatial table with index. –  Mapperz Jun 27 '12 at 14:05
    
The table tableauxiliary is MyIsam. The tablebusiness is innodb. Actually that's a good idea. All I need is the id. I can query strictly myIsam table. –  Jim Thio Jun 28 '12 at 3:02
    
Perhaps joining myIsam and innodb is what turn off the speed. –  Jim Thio Jun 28 '12 at 3:02
    
you can turn that into an answer –  Jim Thio Jun 28 '12 at 3:03
add comment

1 Answer

You can export out the data and import into a MyISAM MYSQL this way you will then have spatial indexing which will speed up the entire process by many times. PostGIS would be even faster.

I don't think your queries are wrong - just needs to be a spatial table with index.

Types of Spatial dependent on the database software used:

Spatial indices are used by spatial databases (databases which store information related to objects in space) to optimize spatial queries. Indexes used by non-spatial databases cannot effectively handle features such as how far two points differ and whether points fall within a spatial area of interest. Common spatial index methods include:

Grid (spatial index)
Z-order (curve)
Quadtree
Octree
UB-tree
R-tree: Typically the preferred method for indexing spatial data. Objects (shapes, lines and points) are grouped using the minimum bounding rectangle (MBR). Objects are added to an MBR within the index that will lead to the smallest increase in its size.
R+ tree
R* tree
Hilbert R-tree
X-tree
kd-tree
m-tree - an m-tree index can be used for the efficient resolution of similarity queries on complex objects as compared using an arbitrary metric.

Source: http://en.wikipedia.org/wiki/Spatial_database#Spatial_index

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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