I am trying to implement a radius search feature on a website like this:
I have a clonable template 'Zone' with the following editable regions:
1. Zone name
2. Location/City
3. Location I'd
4. Longitude
5. Latitude

These fields will be populated using a databound form and Google maps location autocomplete API. Use types in a location and the Google API gives suggestions and then goes on to provide the location city, id, longitude and latitude into hidden fields which are in turn persisted into a cloned page.

Now on a separate page I want users to be able to see other Zones in a certain radius of a given Zone's(page) coordinates using Google maps API and SQL query like this:

Code: Select all

SELECT
`id`,
`name`,
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 AS `distance`
FROM `stations`
WHERE
ACOS( SIN( RADIANS( `latitude` ) ) * SIN( RADIANS( $fLat ) ) + COS( RADIANS( `latitude` ) )
* COS( RADIANS( $fLat )) * COS( RADIANS( `longitude` ) - RADIANS( $fLon )) ) * 6380 < 10
ORDER BY `distance`

<!-- 
where $fLat is the current pages(Zone's) latitude and $fLon the current page(Zone's) longitude. The ten in the where clause is the range you want to search within. Remember: This search is based on kilometers.
-->

I believe this is doable in Couch. My problem is how to run queries in Couch using query and php since I'm not a Mysql and php person.

Any help here . Thank you

Hi KK, I managed to pull off this after hours of going through Couch and MySQL online:

Code: Select all

<!-- IN PAGE VIEW -->
<cms:php>
    $GLOBALS['fLat'] = "<cms:show latitude />"; //The current page's latitude value
    $GLOBALS['fLon'] = "<cms:show longitude />"; //The current page's longitude value
</cms:php>
<cms:query
    sql="SELECT p.id pid,t.name tname,
     ACOS( SIN( RADIANS( d0.value ) ) * SIN( RADIANS( <cms:php>echo $GLOBALS['fLat'];</cms:php> ) ) + COS( RADIANS( d0.value ) ) * COS( RADIANS( <cms:php>echo $GLOBALS['fLat'];</cms:php> )) * COS( RADIANS( d1.value ) - RADIANS( <cms:php>echo $GLOBALS['fLon'];</cms:php> )) ) * 6380 distance
        FROM <cms:php>echo K_TBL_PAGES;</cms:php> p
        inner join <cms:php>echo K_TBL_TEMPLATES;</cms:php> t
        on p.template_id = t.id
        inner join <cms:php>echo K_TBL_DATA_TEXT;</cms:php> d0
        inner join <cms:php>echo K_TBL_DATA_TEXT;</cms:php> d1
        WHERE (t.name='gmap.php')
        AND (p.template_id=t.id)
        AND ACOS( SIN( RADIANS( d0.value ) ) * SIN( RADIANS(<cms:php>echo $GLOBALS['fLat'];</cms:php> ) ) + COS( RADIANS( d0.value ) )
* COS( RADIANS( <cms:php>echo $GLOBALS['fLat'];</cms:php> )) * COS( RADIANS( d1.value ) - RADIANS( <cms:php>echo $GLOBALS['fLon'];</cms:php> )) ) * 6380 < 10
        AND publish_date < '<cms:date format='Y-m-d H:i:s' />'
        AND NOT publish_date = '0000-00-00 00:00:00'
        ORDER BY publish_date desc;"
    limit='10'
    paginate='1'>

    
    <cms:pages masterpage=tname id=pid>
       <a href="<cms:show k_page_link />"><cms:show k_page_title /></a><br />
    </cms:pages>

    <br />
    <cms:paginator />
</cms:query> 

However the problem I am facing now is this: The results are repeating instead of just showing the pages I want.

Code: Select all

300-gmap.php/// 5.564540//4.6211196061780315
Accra

300-gmap.php/// 5.564540//4.614847579822749
Accra

300-gmap.php/// 5.564540//0.2405203049156388
Accra

300-gmap.php/// 5.564540//0
Accra

299-gmap.php/// 5.564540//4.614847579822749
Apedwa

299-gmap.php/// 5.564540//0.2405203049156388
Apedwa

299-gmap.php/// 5.564540//0
Apedwa

299-gmap.php/// 5.564540//4.6211196061780315
Apedwa

298-gmap.php/// 5.564540//4.614847579822749
Circle

298-gmap.php/// 5.564540//0.2405203049156388
Circle

« prev12next »

What could I be doing wrong? Thank you

[KK]

Perhaps using 'DISTINCT' would help? e.g.

Code: Select all

sql="SELECT DISTINCT p.id pid,t.name tname,
...

Ok so I used DISTINCT as you suggested but the result were still the same.

[KK]

I had a closer look and found that the problem is with your query itself - you need to constrain the inner joins further.
Anyway, I think it would be easier for you to adapt @cheesypoof's code found here - viewtopic.php?f=8&t=7504

In particular, his code found in https://github.com/cheesypoof/CouchCMS- ... l-code.php should help you adapt it for your needs.

Hope this helps.

Thanks very much KK,
I have multiple times but this solution from cheesypoof never showed up. I'm happy you helped and thank you cheesypoof for the elegant solution

Unfortunately due to the low to no knowledge I have in mysql and php, I am unable to tweak cheesypoof's solution to suit my problem.

[trendoman]

Unfortunately due to the low to no knowledge I have in mysql and php, I am unable to tweak cheesypoof's solution to suit my problem.

I know very well mysql and php and can help privately.