CODEWITHSUNDEEP
mysqllaravellaravel-4geolocationgeospatial
Safari
Safari

Reputation: 11935

Custom SQL query with geoposition using Laravel

I have a question about Laravel 4 and a particular custom SQL query.

I use MySql. I have a table Points and in this table I have also two columns: Latitude and Longitude (as float value).

I would to have a rest api to gets the points nearby a particular geo position and zoom level.

So I have 3 questions, because I'm not practical for this:

1) I think that I have to implement the api into index() method of
my controller in Laravel...is correct this?

2) How can I write a custom sql query using Laravel?

3) What' is the best practice (as query SQL) to get points near by a
geoposition and zoom leavel?

Is possibile to have some suggestions about this questions?

Thanks

Upvotes: 2

Views: 1820

Answers (2)

thijsai
thijsai

Reputation: 1785

You should use the Point data type and the ST_Distance and ST_Within MySQL functions for optimal performance. I've just posted a gist with some code out of my own project.

https://gist.github.com/tscheepers/dd366c0b93c9b1b04c64

I got the information from this article: http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/

// Using fairly new ST_Distance and ST_Within function in MySQL

$lat = 6.0;
$lon = 52.0;
$maximumDistance = 1000;
$lonBound1 = $lon - $maximumDistance / abs(cos(deg2rad($lat)) * 69);
$lonBound2 = $lon + $maximumDistance / abs(cos(deg2rad($lat)) * 69);
$latBound1 = $lat - ($maximumDistance / 69);
$latBound2 = $lat + ($maximumDistance / 69);

$meetings = Meeting::whereRaw("
ST_Within(
    geolocation,
    envelope(
        linestring(
            point($lonBound1, $latBound1),
            point($lonBound2, $latBound2)
        )
    )
)")->orderByRaw("
ST_Distance(
    geolocation,
    GeomFromText('POINT($lat $lon)')
)")->get();

Upvotes: 4

Alexandre Butynski
Alexandre Butynski

Reputation: 6746

Three questions in one... hard to answer to everything ! I will try to do my best.

1) You could implement your API everywhere you want. First, you have to think about what API syntax you want. I think that GET /points?latitude=24.847&longitude=76.9876 is a good one. With that you can have a PointController with an index method to implement that, it's the standard.

But you have to keep in mind to not put so much logic in your controller. This kind of request should be prepared in the model layer or in a service layer. You should just called something like that in your controller : $points = Point::getFromGeoloc(24.847, 76.9876); or $points = Point::around(24.847, 76.9876)->take(10)->get();. You should look at Query Scopes for that.

2) You have several syntaxes to write raws queries :

Complete raw query :

$results = DB::select('select * from users where id = ?', array(1));

Partial raw query :

$users = DB::table('users')
                 ->select(DB::raw('count(*) as user_count, status'))
                 ->where('status', '<>', 1)
                 ->groupBy('status')
                 ->get();

Insert a raw conditions between other wheres :

$users = User::where('age', >, 10)->whereRaw('posts > 2000')->get();

The best practice is to use the less raw queries as you can and use the standard methods. Don't forget to read the doc, read the doc and read the doc again !

3) That's not my speciality but I know that there is built-in support of spatial data : http://dev.mysql.com/doc/refman/5.6/en/spatial-extensions.html. You can also do a basic work with maths, don't forget Pythagoras theorem !

Upvotes: 4

Related Questions