Convert complicated query to XPDO or leave as native mysql?

68 Posts

megasteve4 Reply #1, 14 years, 4 months ago

Hi I am making a custom store locator for a web site.

That requires this query -

                SELECT
                    ROUND(
                        $earthRadius * ACOS(
                            SIN( $latitude*PI()/180 ) * SIN( latitude*PI()/180 )
                            + COS( $latitude*PI()/180 ) * COS( latitude*PI()/180 )  *  COS( (longitude*PI()/180) - ($longitude*PI()/180) )   )
                    , 1)
                    AS distance,
                    store_name,
                    postcode,
                    postal_address,
                    latitude,
                    longitude
                FROM
                    stores
                ORDER BY
                    distance ASC
                LIMIT 0, 10";

I have no idea even how to start to convert this to an XPDO format query of if it is worth trying ??

What would be the best practice to do here just use native mysql?

Thanks

863 Posts

Lucas Reply #2, 14 years, 4 months ago

If you've created a model and schema for 'Store' you could load it up and then use something like this (completely untested):

$storeTable = $modx->getTableName('Store');
$query = new xPDOCriteria($modx,
    'SELECT ROUND(
        :earthRadius * ACOS(
            SIN( :latitude*PI()/180 ) * SIN( latitude*PI()/180 )
            + COS( :latitude*PI()/180 ) * COS( latitude*PI()/180 )  *  COS( (longitude*PI()/180) - (:longitude*PI()/180) )   )
    , 1) AS distance,
    store_name,
    postcode,
    postal_address,
    latitude,
    longitude
    FROM
    '.$storeTable.'
    ORDER BY
    distance ASC
    LIMIT 0, 10',
    array(
        ':earthRadius' => $earthRadius,
        ':latitude' => $latitude,
        ':longitude' => $longitude,
    )
);
$stores = $modx->getCollection('Store',$query);

You might run into problems by repeating ':latitude'; if this is the case just use ':latitude1' and ':latitude2' with the same value.

If you don't have a model, you can at least use xpdo to get the results into an array using the same $query from above, and:

if ($query->stmt && $query->stmt->execute()) {
    $stores = $query->stmt->fetchAll(PDO::FETCH_ASSOC);
}

By the way, thanks for doing the math on that... looks useful! laugh

[ed. note: okyanet last edited this post 14 years, 4 months ago.]

☆ A M B ☆
571 Posts

romain Reply #3, 14 years, 4 months ago

Hi,

Just a quick reply in case you want to read some interesting article by Lazyleg : xPDO and MODX with geonames and haversine formula.

That might give your some insights

(reading others Lazyleg's articles isn't a bad idea either ^^).

Cheers

@modx_fr | Github | modx.fr
@rtripault | Github | melting-media.com

863 Posts

Lucas Reply #4, 14 years, 4 months ago

Romain - nice find! It is an interesting read...

68 Posts

megasteve4 Reply #5, 14 years, 4 months ago

@Romain - agreed good find - I will have to go through that looks like a good tut - thanks

@Lucas - Once again a good reply! AIas I can not claim credit for that original sql statement its cut and past from - http://davidsimpson.me/2008/09/28/find-nearest-store-page-using-php-mysql-and-google-maps/ - Great article regarding actually calculating distance between geo-locations and free geo-coding via gmaps.

I do already have a schema - I will experiment with your XPDO solution and post back results.

Cheers.

PS for the sake of anyone else that might find this later there are a couple of Modx addons that will do this out of the box but I just need more custom functionality - example - http://modx.com/extras/package/storelocator

68 Posts

megasteve4 Reply #6, 14 years, 4 months ago

I have now tested Lucas's code and it works perfectly.

I did also add a distance clause so you could limit results to fall within x number of miles from a set point. NOTE that as the col distance is an alias you need to use HAVING rather than WHERE.

FROM storeTable 
HAVING  distance < 10
ORDER BY distance ASC

Thanks [ed. note: megasteve4 last edited this post 14 years, 4 months ago.]