Distance to Point, Great Cricle Distance, Cosine Theorem, Haversine formula
Great Circle Distance Calculation
http://code.google.com/apis/maps/articles/phpsqlsearch.html
Finding locations nearby with MySQL
To find locations in our markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere. An in-depth mathemetical explanation is given by Wikipedia and a good discussion of the formula as it relates to programming is on Movable Type’s site.
Here’s the SQL statement that will find the closest 20 locations that are within a radius of 25 miles to the 37, -122 coordinate. It calculates the distance based on the latitude/longitude of that row and the target latitude/longitude, and then asks for only rows where the distance value is less than 25, orders the whole query by distance, and limits it to 20 results. To search by kilometers instead of miles, replace 3959 with 6371.
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) – radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;
SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS($lat * PI() / 180) * COS(`lat` * PI() / 180) * COS(($lon – `lon`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM `members` HAVING distance<=’10′ ORDER BY distance ASC
Necessary reading for Great Circle Distance Calculations
http://code.google.com/apis/maps/articles/phpsqlsearch.html
http://www.movable-type.co.uk/scripts/latlong.html
http://zcentric.com/2010/03/11/calculate-distance-in-mysql-with-latitude-and-longitude/
http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL
MySQL Group Count
SELECT
table1.AppointmentDate,
Count(table1.IDfield)
FROM
table1
WHERE
table1.seen = ’1′ AND
table1.AppointmentDate >= ’2007-12-01′ AND
table1.AppointmentDate GROUP BY
table1.AppointmentDate
DELETE QUERY WITH 2 TABLES
MYSQL DELETE QUERY OVER MULTIPLE TABLES
DELETE calllog
FROM calllog
JOIN locum ON calllog.LocumID = locum.LocumID
WHERE locum.LastName = “ANAME”
Error Message – add-in could not be installed or loaded
Error Message – add-in could not be installed or loaded
eg
the add-in “C:\PROGRA~1\ESET\ESETSM~1\EPGOU~2.DLL” could not be installed or
loaded
or
The Add-in ‘ESET Outlook Plugin’ (C:\PROGRA~1\ESET\ESETNO~1\EPLGOU~1.DLL) cannot be loaded and has been disabled by Outlook. If no update is available, please uninstall the Add-in.
THEN JUST DO THIS
Tools > Options > Other > Advanced Options > Add-In Manager. Uncheck the bastard plugin that is giving you the trouble
:)
MYSQL to UPPER CASE and LOWER CASE
How to convert MYSQL Columns to Upper Case and Lower Case
SELECT LOWER(name) FROM mytable
SELECT UPPER(name) FROM mytable
UPDATE mytable SET name = LOWER(name);
UPDATE mytable SET name = UPPER(name);
Trixbox VMware
you must configure you new VM-ware session as other linux 2,6 kernal whith one SCSI drive off 8Gb and the network bridged.
Might need
The summary is this. You need to add the following options to your linux boot command:
acpi=off noapic nosmp nolapic clock=pit
i.e. edit /boot/grub/grub.conf and make the line beginning with “kernel” look like this:
kernel /vmlinuz-2.6.9-34.0.2.EL ro root=LABEL=/ acpi=off noapic nosmp nolapic clock=pit
(Don’t cut and paste the whole line – you might be running a different rev of the kernel.)
Char and VARCHAR
Where the data varies significantly in length from one record to the next with the longest being significantly longer than the average then you need to use a VARCHAR. There is no point in using a CHAR(40) if the average length is only 15 since that will resuult in a lot of wasted space in most records. Using a VARCHAR(40) instead means that you can still fit the leng 40 chharacter values while the average space used will be 15 plus the record length marker overhead.
summary use varchar for variable length fields and use char if you are expecting it to always be the same eg CHAR (ASSS, DASS, FASS, TASS) this is good for a char ie fixed length fields
Disable fopensock pfopensock
Disable php Directives(fopensock) & (pfopensock) We were victims of a clever spammer, who was able to upload a php script that simulated/emulated SMTP. It also had code that bypassed the syslog.
I finally caught them, after a month of searching in our own server.
I tarred up the scripts for further analysis, and deleted the account, and more.
When I finished breaking down this script I saw that the whole concept depended on one php directive.
fopensock()
Just disable the fsockopen() in your /etc/php.ini
disable_functions = phpinfo,fsocket,fsockopen,pfsockopen




