Re: Calculation distances
Mike Blezien wrote: Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator The math in the link I gave you will still work for the distance calculations, however if you want what that site does you will need a GIS database that can give you lat long of an address. I am about 90% sure something like that is going to cost you a lot of money and cost a lot to keep it up to date. I'm afraid I don't know where to look for a vendor though. I would search for GIS software or maybe contact some of the mapping people like Google maps or Street Atlas USA, if they don't have a product they can sell you to provide lat long of street addresses, maybe they can tell you who provides them with data and contact them. -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Chris, this is something very similar to what we are attempting to accomplish: http://www.papajohnsonline.com/restlocator/RestaurantLocator Mike - Original Message - From: "Chris W" <[EMAIL PROTECTED]> To: "Mike Blezien" <[EMAIL PROTECTED]>; "MYSQL General List" Sent: Sunday, August 20, 2006 8:59 PM Subject: Re: Calculation distances Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calculation distances
Mike Blezien wrote: Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) The zip code tables usually give a lat long location somewhere near the center of that zip code (which can be HUGE in rural areas) You can then use some math that with give you the distance between the coordinates of 2 zip codes. Here is a web site I found for help when doing similar calculations. Remember that this type of math usually wants angle measurements in radians, not degrees. http://williams.best.vwh.net/avform.htm -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calculation distances
[snip]
we are working on a small project which needs to calculate the closest
distances from an address and/or zipcode entered into a search box. It
will read from a MySQL database of companies, which store their address
and zipcodes.
looking for the best way to approach this. I've seen some zipcode Perl
modules on Cpan, but nothing for helping calculation distances. Can
someone point me in the right direction to accomplish this ... thx's :)
[/snip]
We have started storing latitude and longitude coordinates on our
databases which lends itself to distance calculations. For example, here
is a PHP function which performs the distance calculation
function distance($lat1, $lon1, $lat2, $lon2) {
$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +
cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
return $miles;
}
You may be able to do it entirely in a query by utilizing MySQL's math
functions;
http://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[SOLVED] Re: Should Joins always be using an index? (where possible?)
On Mon, 2006-08-21 at 07:39 +1000, Chris wrote: > Ow Mun Heng wrote: > > On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: > >> On 8/20/06, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > >>> I'm have a query like so > >>> > >>> select > >>> A, > >>> index_A > >>> from > >>> tableA > >>> join tableB > >>> on tableB.indexA = tableA.indexA > >>> > >>> > >>> select > >>> A, > >>> index_A > >>> from > >>> tableA > >>> join tableB > >>> on tableB.A = tableA.A > >>> > >>> whcih would be more efficient? using the where clause which uses the > >>> index or the one which isn't index? > >> If you join using the field that is indexed, this will use an index. > >> > >> If you join using the field that isn't indexed, it can't use an index. > > > > I take it that that means yes, it's more efficient performance wise to > > always use an index. > > Of course, same as any query ;) Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Calculation distances
Hello, we are working on a small project which needs to calculate the closest distances from an address and/or zipcode entered into a search box. It will read from a MySQL database of companies, which store their address and zipcodes. looking for the best way to approach this. I've seen some zipcode Perl modules on Cpan, but nothing for helping calculation distances. Can someone point me in the right direction to accomplish this ... thx's :) TIA, Mike(mickalo)Blezien -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP needed for speeding up a query!
[ always cc the list so others can share the fix or make appropriate comments ] Nicholas Wyatt wrote: hi chris, thanks for answering! however, i do already have indexes on those columns. all my tables use the myisam storage engine. what are the differences you mentioned between these engines when using MIN()? myisam is a non-transactional table type, so only 1 client can update/delete/insert into it at once (other clients attempting to do this will be locked until the insert/update/delete finishes). That means the table itself can keep information about the largest value, the smallest value and the number of rows the table has. innodb is a transactional table type, which means many clients can update/delete/insert into it at once. That means the table cannot keep information about values and the number of rows because at any time it could change in another client. http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html i did find a way to speed up the query, by the way. i joined the tables the other way around. the query now takes about 0,5 seconds. SELECT site.site_id, site.site_title, site_url, site.site_testtype, site.project_id, test.test_kt_points, MIN(test_item.ti_evaluation) as completed, IF (site.site_date < site.site_enddate, site.site_enddate, site.site_date) AS sort_date FROM test_item LEFT JOIN test ON (test.test_id = test_item.test_id) LEFT JOIN site ON (site.site_id = test.site_id) WHERE site.site_testtype IN (-1, 0, 1, 2) GROUP BY site.site_id Interesting. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
select userid,count(text) from blah group by userid; --On August 20, 2006 7:22:59 PM +0100 Peter Van Dijck <[EMAIL PROTECTED]> wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- "Genius might be described as a supreme capacity for getting its possessors into trouble of all kinds." -- Samuel Butler -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query needed to convert varchar to int ....sorry previous posting was incomplete
VenuGopal Papasani wrote:
Dear all,
I have a table with the following structure.
ield Type CollationNullKey Default
Extra Privileges Comment
--- -- -- ---
-- --- ---
idint(11) (NULL) NO PRI (NULL)
auto_increment select,insert,update,references
indicatorName varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
periodNamevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
sourcevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
level int(11) (NULL) YES
(NULL) select,insert,update,references
value varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
numeratorValuevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
denominatorValue varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
The values in value,NumeratorValue and DenominatorValue
Value NumeratorValue
denominatorValue
NaNNull
Null
infinity
null Null
2143.9888 NUll
NUll
0.0 0.0
0.0
Now i need a query which converts the varchar into some numeric
values.For ex for non numeric values like NAN,Infinity, Null get as zero
and
2143.9 is converted into a numerical 2143.9888 and 0.0 is also
converted
to numeric.The resultset should in Numeric value all the above fields,
Can i
do it using a query.If so can any one give me the query
You could use case:
select case when value is null then 0 else cast(value as unsigned) end
AS new_value;
http://dev.mysql.com/doc/refman/5.1/en/case-statement.html
http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: HELP needed for speeding up a query!
Nicholas Wyatt wrote: Hello to all! has anybody got any ideas how i can speed up the following query? it's so awfully slow (about 1 second). the "test_item" table is the main problem. currently, it has about 108.000 entries. is it normal that it takes so long? and if it isn't, how can i optimize the query? i've got indices on all relevant columns. SELECT site.site_id, site.site_title, site.site_testtype, test.test_kt_points, MIN(test_item.ti_evaluation) as completed, IF (site.site_date < site.site_enddate, site.site_enddate, site.site_date) AS sort_date FROM site LEFT JOIN test ON (site.site_id = test.site_id) LEFT JOIN test_item ON (test.test_id = test_item.test_id) WHERE site.site_testtype IN (-1, 0, 1, 2) GROUP BY site.site_id Do you have indexes on these tables/columns: site.site_id, test.site_id, test.test_id, test_item.test_id ? What sort of tables are they (innodb or myisam)? That will make a difference mainly because of the MIN() call. Try creating an index on test_item.ti_evaluation: create index ti_evaluation on test_item(ti_evaluation) or even create index ti_evaluation on test_item(test_id, ti_evaluation) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should Joins always be using an index? (where possible?)
Ow Mun Heng wrote: On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: On 8/20/06, Ow Mun Heng <[EMAIL PROTECTED]> wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. I take it that that means yes, it's more efficient performance wise to always use an index. Of course, same as any query ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting 5 to run
look in /var/lib/mysql for the error file. make sure /var/lib/mysql is owned by mysql Karl Larsen wrote: I am trying to get mysql 5 to run on my Fedora Core 4 linux. I installed both server and client RPM files. The info is good and using that I am trying to do the post-install. I ran mysql_install_db as a user and it failed. Then I tried as root and it seemed to work. But one thing it says to do I cannot find. It says: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system I have no idea where support-files are or where the "right place" is. When I try to start mysqld_safe it errors out with this: [EMAIL PROTECTED] mysql]# mysqld_safe & [1] 6501 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 060820 14:27:48 mysqld ended So I can set up the DB but not start mysqld. Does anyone have an idea what I am doing wrong? I'm just following the info instructions. Karl Larsen -- One day at a time, one second if that's what it takes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting 5 to run
I am trying to get mysql 5 to run on my Fedora Core 4 linux. I installed both server and client RPM files. The info is good and using that I am trying to do the post-install. I ran mysql_install_db as a user and it failed. Then I tried as root and it seemed to work. But one thing it says to do I cannot find. It says: To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system I have no idea where support-files are or where the "right place" is. When I try to start mysqld_safe it errors out with this: [EMAIL PROTECTED] mysql]# mysqld_safe & [1] 6501 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 060820 14:27:48 mysqld ended So I can set up the DB but not start mysqld. Does anyone have an idea what I am doing wrong? I'm just following the info instructions. Karl Larsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
brilliant, that works! Thanks! On 8/20/06, Chris W <[EMAIL PROTECTED]> wrote: Peter Van Dijck wrote: > I have a table with userid and text. Users write text. I want to find > the top 5 users who have the most rows in this table. > > I can't seem to figure out the query.. is there a query possible to do > this? > > Thanks! > Peter > SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query question: most active user
Peter Van Dijck wrote: I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter SELECT Count(*) as Count, UserID FROM table GROUP BY UserID ORDER BY Count DESC LIMIT 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want & give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query question: most active user
I have a table with userid and text. Users write text. I want to find the top 5 users who have the most rows in this table. I can't seem to figure out the query.. is there a query possible to do this? Thanks! Peter -- find videoblogs: http://mefeedia.com my blog: http://poorbuthappy.com/ease/ my job: http://petervandijck.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should Joins always be using an index? (where possible?)
On Sun, 2006-08-20 at 19:59 +1000, chris smith wrote: > On 8/20/06, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > > I'm have a query like so > > > > select > > A, > > index_A > > from > > tableA > > join tableB > > on tableB.indexA = tableA.indexA > > > > > > select > > A, > > index_A > > from > > tableA > > join tableB > > on tableB.A = tableA.A > > > > whcih would be more efficient? using the where clause which uses the > > index or the one which isn't index? > > If you join using the field that is indexed, this will use an index. > > If you join using the field that isn't indexed, it can't use an index. I take it that that means yes, it's more efficient performance wise to always use an index. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HELP needed for speeding up a query!
Hello to all! has anybody got any ideas how i can speed up the following query? it's so awfully slow (about 1 second). the "test_item" table is the main problem. currently, it has about 108.000 entries. is it normal that it takes so long? and if it isn't, how can i optimize the query? i've got indices on all relevant columns. SELECT site.site_id, site.site_title, site.site_testtype, test.test_kt_points, MIN(test_item.ti_evaluation) as completed, IF (site.site_date < site.site_enddate, site.site_enddate, site.site_date) AS sort_date FROM site LEFT JOIN test ON (site.site_id = test.site_id) LEFT JOIN test_item ON (test.test_id = test_item.test_id) WHERE site.site_testtype IN (-1, 0, 1, 2) GROUP BY site.site_id thanks a lot in advance for any suggestions! cheers, nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Прошу помощи
Здравствуйте! Интересует такой момент. Мой сайт хостится у местного провайдера. Они создали базу mySQL где у меня и лежат таблицы для моего портала. Возникла необходимость перенести модули (гостевая книга и фотогалерея) на новый хост. Файлы модулей я скопировал, проблем с этим не возникло. Однако, после импорта таблиц этих модулей, все русские буквы превратились в знаки вопросов. Как я понял дело в кодировке. Кодировка базы - Юникод UTF-8. Импортируемые таблицы находятся к Windows-1251. Но что интересно... После импорта, базы сохраняют свои истинные буквы (нормальные русские буквы). Видимо дело тут на "выходе" с таблицы. Как можно исправит такой глюк? Заранее Вас благодарю! _ С уважением, Гордеев Константин E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] Web: www.gymnasium.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should Joins always be using an index? (where possible?)
On 8/20/06, Ow Mun Heng <[EMAIL PROTECTED]> wrote: I'm have a query like so select A, index_A from tableA join tableB on tableB.indexA = tableA.indexA select A, index_A from tableA join tableB on tableB.A = tableA.A whcih would be more efficient? using the where clause which uses the index or the one which isn't index? If you join using the field that is indexed, this will use an index. If you join using the field that isn't indexed, it can't use an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
