Find all points within radius from latitude and longitude given (from given point)

I had to use these formulas for a few times now, and the DB performance has to suffer when doing these kinds of searches.
However, i've just recently found a less accurate method, which has a great performance improvement on the database searches, the errors (unwanted results, or false positives) are neglectable in my case.

The SQL:
SELECT * FROM interest locations WHERE latitude BETWEEN @minLatitude AND @maxLatitude AND longitude BETWEEN @minLongitude AND @maxLongitude

The idea is to create a box, or, thinking planar - a square. Imagining the earth be plane (a plane map), the query above searches for all points (latitudes and longitudes) within the square the circumscribes our given radius cirlce.

The minimum and maximum latitudes, we calculate as starting (center) latitude plus/minus result of given radius divided by 111 (111 is the distance of one degree of latitude in Km):
@minLatitude = givenLatitude - givenRadius / 111

The minimum and maximum longitudes, we calculate as starting (center) longitude plus/minus result of given radius divided by 78, or if you want to be more exact, the corresponding DeltaLongitude depending on the given Latitude (0 => 111.320, 15 => 107.551, 30 => 96.486, 45 => 78.847, 60 => 55.800, 75 => 28.902, 90 => 0.0001)
@minLongitude = givenLongitude - givenRadius / longitudesArray[floor(abs(givenLatitude)/15)*15]


The funny name that somebody gave it was: 'thinking inside the box' (more info here: http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points , respect to Binary Worrier :).


Surface of circumscribed square / surface of circle = 1.27 (ca. 27% more surface returned [false positives])
Greatest distance error: for 50Km - 20Km, for 100Km - 40Km, for 200Km - 82Km.


The given SQL has great performance improvments, especially because it can use indexes on the latitude or longitude columns, unlike the cosines formula, which does not allow for the database engines to use indexes.

Email subject UTF-8 encoded

I haven't encountered this yet, i've always been able to send email messages WITH utf-8 correctly encoded and displayed characters (umlauts, acutes), but i've also met this technique which renders good results too:
Set your subject's encoding (or another header directive's) like this:
'=?utf-8?B?'.base64_encode($subject).'?='

That means your subject needs to look like:
=?ENCODING?B?base64_encoded_data?=

Pretty easy

Display php email sent subject characters displayed correctly, umlauts.

DataTables jQuery IE8 bug

I've just bumped into a bug while testing jquery.dataTables in Internet Explorer 8 (IE8).

I kept getting:
SCRIPT5007: Unable to get value of the property 'className': object is null or undefined
jquery.dataTables.min.js , line 27 character 288


I had my table set up and sorted at document.ready like this:

    $('#CGITable').dataTable( {
      "sDom": '<"top">rt<"bottom"><"clear">',
      "aaSorting": [colSorted['CGITable']],
      "bPaginate": false,
      "aoColumns": [
        { "sType": "string", },
        { "sType": "string" },
        { "sType": "numeric-comma" },
        { "sType": "numeric-comma" },
        { "sType": "numeric-comma" },
        { "sType": "numeric-comma" },
        { "sType": "numeric-comma" },
        { "sType": "string" },
      ],
      "fnDrawCallback": function( oSettings ) {
        colSorted['CGITable'] = oSettings['aaSorting'];
      }
  } );

After a lot of digging, the problem seemed to rise from the following two issues in the aoColumns parameter array:

• Remove the trailing commas from the array declaration (as somebody noted here, the syntax is invalid).
• But I also had to add these properties to each columns' configuration: 'sClass': "", "bSortable": true

My final, working piece of jquery dataTables setup is:
    
    $('#CGITable').dataTable( {
      "sDom": '<"top">rt<"bottom"><"clear">',
      "aaSorting": [colSorted['CGITable']],
      "bPaginate": false,
      "aoColumns": [
        { "sType": "string", 'sClass': "", "bSortable": true },
        { "sType": "string", 'sClass': "", "bSortable": true },
        { "sType": "numeric-comma", 'sClass': "", "bSortable": true },
        { "sType": "numeric-comma", 'sClass': "", "bSortable": true },
        { "sType": "numeric-comma", 'sClass': "", "bSortable": true },
        { "sType": "numeric-comma", 'sClass': "", "bSortable": true },
        { "sType": "numeric-comma", 'sClass': "", "bSortable": true },
        { "sType": "string", 'sClass': "", "bSortable": true },
      ],
      "fnDrawCallback": function( oSettings ) {
        colSorted['CGITable'] = oSettings['aaSorting'];
      }
  } );


Has this helped you?
Then please drop me a line :)


Keywords: jquery, dataTables, dataTable, sorting, tables, auto-sorting, aoColumns, IE8, error, unable, className

php_memcache.dll for Windows 5.4

For those who cannot find the Memcache libraries for php 5.4, here is a link to the dll's php_memcache.dll for Windows:

https://www.dropbox.com/sh/0y53d29wsa1eim5/S3C0-vuIwv 


PHP, 5.4., Windows, Memcache

Illegal mix of collations - how to solve it?

With some queries in MySQL one might encounter an 'illegal mix of collations' error similar to this:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' 


It occurs when joining, comparing, concatenating strings of different (incompatible) collations (assembly of information in a proper logical/numerical order).

The 'utf8_general_ci' and the 'utf8_unicode_ci' collations above can differ from case to case, it just happened so in my specific case.

In my case, I was trying to join two tables on those columns with different collations, e.g. (query 1):

SELECT * FROM a LEFT JOIN b ON (a.email=b.email);


Where ,
Table a:
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

And table b:
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL

The solution is forcing the same / compatible collations on the two columns, query 1 becomes:

SELECT * FROM a LEFT JOIN b ON (a.email COLLATE utf8_unicode_ci =b.email);


There are other solutions, where possible, like changing the collation of the columns / tables by design. e.g.:

ALTER TABLE a CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

or:

ALTER TABLE a MODIFY COLUMN email varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;


Good luck!

How To Enable Google Toolbar for Firefox 13 and above

For Windows Vista users:

1. You should have Google Toolbar for Firefox version 7.1.20110512W installed. You can still find it on the internet, download and install it.
   to check for the toolbar version installed,
   
   1. Type about:addons in Firefox's address to bring up the addons page
   2. Select the Extensions  tab from the left menu
   3. You should see there the Google toolbar version
2. Use your text editor (Notepad) to edit this file C:\Users\[your username]\AppData\Roaming\Mozilla\Firefox\Profiles\Default Profile\{3112ca9c-de6d-4884-a869-9855de68056c}\install.rdf . Replace in this path the [your username] part with your actual Windows username. Mine is Alex.
3. Change line 17 of this file from:
   <em:maxVersion>4.0.*</em:maxVersion>
   To this: 
   <em:maxVersion>24.*</em:maxVersion>
4. Save and close this file.
5. Restart Firefox, and you should find the Google Toolbar back among your Firefox toolbars.

Typo3. Installing. Headache

About Typo3.

I must say i have tried several times to install Typo3 CMS and get it to work on my local server. And yes, i have to confirm it has been a real pain in the ass every time, and yes, sometimes i didn't even succeed tottally.

And i even ain't the dumbest kind of computer user and i'm pretty familiar with installing and configuring software. I even thought that 'duuh how hard can it be, its PHP dang i can find my way around that'.

Well not really.

I am in the middle of about the tenth battle with this CMS to get it to work and to work it on my computer and i hope i win this time.

But just as I am going through the Typo3 Introduction Manual, (wiki Introduction/Matthew), and after enough painful manual operations before seeing the first 'HELLO WORLD' page, i bump into this phrase in the manual.

Chapter: Edit TypoScript constants
Quote: <<You need to add this line of code and remember to set the pid number to the actual pid number of your sys_folder. You can see the pid number when you hover your mouse over the folder icon. >>



WHAAAT?  I can see the pid number when i hover? WTF ?

That's too much for me. I gave it a laugh but then also a reality check. Nevertheless to say that the folder in case should be seen in an image that's missing from the manual page, as other images also are, that there are language mistakes there (missing words?)

And the actual code is this:

#add to constants template
plugin.feadmin.fe_users.pid = 37

While at the top of the page, the author tells us that this manual is for:

"Who this document is for

Newbies! Congratulations!"

Newbies? Congratulations? What for? The headache? No, siree, thank you!

I'm giving up today because of this.