Mysql or Mongodb lbs fast implementation solution

http://www.wubiao.info/470

Two previous articles:

Find nearby xxx spherical distance and Geohash scheme discussion (http://www.wubiao.info/372)

, MO MO Architecture Scheme analysis (http://www.wubiao.info/401)

discussed, lbs looks for nearby xxx, which includes, MySQL custom storage function scheme, and through Geohash, Redis self-built indexing scheme.

===============================================================

Today we share two kinds of simple schemes that use Geohash encapsulation as built-in database functions;

A:mysql built-in function scheme, suitable for existing business, new add lbs function, increase latitude and longitude field to avoid data migration

B:mongodb built-in function scheme, suitable for small and medium-sized applications, fast implementation of LBS function, performance better than a (recommended)

===============================================================

Scenario A: (MySQL Spatial)

1, first resume a table: (MySQL 5.0 above only support MyISAM engine)

123456789

create table address (       address Code class= "SQL keyword" >char not null ,        address_loc point not null       primary key ( Address)

Spatial index:

1	ALTERTABLE address ADDSPATIAL INDEX(address_loc);

Insert data: (Note: Here point (latitude, longitude) standard notation)

123	INSERTINTO address VALUES(‘Foobar street 12‘, GeomFromText(‘POINT(30.620076 104.067221)‘)); INSERT INTOaddress VALUES(‘Foobar street 13‘, GeomFromText(‘POINT(31.720076 105.167221)‘));

Query: Find (30.620076,104.067221) around 10 km

12345678910111213141516171819

SELECT*    FROMaddress    WHEREMBRContains                    (                    LineString                            (                            Point                                    (                                    30.620076 + 10 / ( 111.1 / COS(RADIANS(104.067221))),                                    104.067221 + 10 / 111.1                                    ),                            Point                                    (                                    30.620076 - 10 / ( 111.1 / COS(RADIANS(104.067221))),                                    104.067221 - 10 / 111.1                                    )                             ),                    address_loc                    )

Programme B:

1, first set up a simple table user, two data are as follows:

1234567891011121314151617

{  "_id": ObjectId("518b1f1a83ba88ca60000001"),  "account": "[email protected]",  "gps": [    104.067221,    30.620076  ]}{  "_id": ObjectId("518b1dae83ba88d660000000"),  "account": "[email protected]",  "gps": [    104.07958,    30.653936  ]}

Where GPS is a two-dimensional array, respectively, longitude, latitude

(Note: This must be stored in (longitude, latitude) order.) We usually express latitude and longitude, are (latitude, precision), here this way there are very close to the people of Wood)

2, before use, establish a two-dimensional index

Build index maximum range in longitude -180~180

1	db.user.ensureIndex({"gps":"2d"},{"min":-180,"max":180})

Delete Index

1	db.user.dropIndex({"gps":"2d"})

3, MongoDB has two ways to find nearby xxx, where Scenario 2) will return distance (recommended)

1) standard query, for the Earth latitude and longitude query built-in; parameter one for the query condition using $near find nearby, parameter two $maxdistance is the latitude and longitude (1°latitude = 111.12 kilometers) that is 1/111.12, means to find a kilometer nearby.

1	db.user.find({ gps :{ $near : [104.065847, 30.657554] , $maxDistance : 1/111.12} })

2) Execute the naming method, simulate into a sphere; parameter one specifies geonear mode and table name; parameter two coordinates, parameter three is spherical, parameter four radians (radians = arc length/radius 1-kilometer radian 1000/6378000), parameter five specify spherical radius (earth radius)

1	db.runCommand({geoNear:‘user‘, near:[104.065847, 30.657554], spherical:true, maxDistance:1000/6378000, distanceMultiplier:6378000});

This entry was posted on May 28, 2013. belong to the DB, architecture, algorithm classification, was posted Geohash, LBS, Mongodb, Mysql tag.

Mysql or Mongodb lbs fast implementation solution