Not being very strong at math, I have a little problem that I'm not
sure how to solve. Maybe someone can help me.

Basically, given a point (latitude, longitude) and a radius (100
meters) (think circle), I need to compute an equivalent square: That
is, two points that would correspond to two corners of the square.

From: 51, -114 100 meters
To: 51.005, -114.005 NE corner
49.995, -113.995 SW corner

Now, the above is not really accurate, of course, since the earth is
spherical (well, at least most people think so), and I would like
this computation to run in MySQL query, e.g.:

UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude
= (*), sw_longitude = (*)

In the above table, there are already three columns with the centre
latitude and longitude and radius. Any ideas? Thanks.

....Rene

> Not being very strong at math, I have a little problem that I'm not sure how
> to solve. Maybe someone can help me.
>
> Basically, given a point (latitude, longitude) and a radius (100 meters)
> (think circle), I need to compute an equivalent square: That is, two points
> that would correspond to two corners of the square.
>
> From: 51, -114 100 meters
> To: 51.005, -114.005 NE corner
> 49.995, -113.995 SW corner
>
> Now, the above is not really accurate, of course, since the earth is
> spherical (well, at least most people think so), and I would like this
> computation to run in MySQL query, e.g.:
>
> UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude = (*),
> sw_longitude = (*)
>
> In the above table, there are already three columns with the centre latitude
> and longitude and radius. Any ideas? Thanks.

http://www.mathforum.com/library/drmath/view/51711.html

At 03:11 PM 6/28/2007, M5 wrote:
>Not being very strong at math, I have a little problem that I'm not
>sure how to solve. Maybe someone can help me.
>
>Basically, given a point (latitude, longitude) and a radius (100
>meters) (think circle), I need to compute an equivalent square: That
>is, two points that would correspond to two corners of the square.
>
>From: 51, -114 100 meters
>To: 51.005, -114.005 NE corner
> 49.995, -113.995 SW corner
>
>Now, the above is not really accurate, of course, since the earth is
>spherical (well, at least most people think so), and I would like
>this computation to run in MySQL query, e.g.:
>
>UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude
>= (*), sw_longitude = (*)
>
>In the above table, there are already three columns with the centre
>latitude and longitude and radius. Any ideas? Thanks.
>
>...Rene

Rene,
So you're trying to fit a square inside of a circle? See
http://mathcentral.uregina.ca/QQ/dat...9.04/bob1.html

Mike

On 6/28/07, M5 <m5@renefournier.com> wrote:
>
> Not being very strong at math, I have a little problem that I'm not
> sure how to solve. Maybe someone can help me.
>
> Basically, given a point (latitude, longitude) and a radius (100
> meters) (think circle), I need to compute an equivalent square: That
> is, two points that would correspond to two corners of the square.
>
> From: 51, -114 100 meters
> To: 51.005, -114.005 NE corner
> 49.995, -113.995 SW corner
>
> Now, the above is not really accurate, of course, since the earth is
> spherical (well, at least most people think so), and I would like
> this computation to run in MySQL query, e.g.:
>
> UPDATE places SET ne_latitude = (*), ne_longitude = (*), sw_latitude
> = (*), sw_longitude = (*)
>
> In the above table, there are already three columns with the centre
> latitude and longitude and radius. Any ideas? Thanks.


The math of latitude and longitude ain't too bad.

Consulting a globe would help.

A degree of latitude is always the same size in terms of the distance along
the earth's surface. However, a degree of longitude varies in size--longest
at the equator and shortest (actually zero) at the poles. The defining
equations come from that.

Just a few notes:

a)Whatever equations you derive for the corners may break down if the area
includes either pole. You will need to guard against that.

b)My gut tells me that you can come up with some very simple approximations
(sine of this times cosine of that or dimension of the square) that will
work so long as the dimensions of the square are much smaller than the
diameter of the earth and you're not working too close to the poles.
However, if you mark up a spherical surface (such as a basketball or
beachball), I think you'll see that the relationships if either of those
assumptions break down would have to go to higher-order equations and
wouldn't be so simple, even if they can be represented in closed form.

If you want the exact relationships (which I don't believe are in the URLs
cited), you should probably post to sci.math.

Dave.

I have the following proc... when I run it I get a response that says
"You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'NULL' at
line 1".

I just want a programatic way to upgrade db engine to innodb where I
don't know exactly what tables exist...


DELIMITER $$

DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$
CREATE PROCEDURE `MYISAMtoINNODB`()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE table_name VARCHAR(255);
DECLARE cur1 CURSOR FOR select table_name from information_schema.tables
where table_schema='firescope' and table_type='BASE TABLE' and
engine='MyISAM';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

OPEN cur1;
REPEAT
FETCH cur1 INTO table_name;
SET @table_name=table_name;
IF NOT done THEN
SET @stmt_text=CONCAT("ALTER TABLE ", @table_name, " ENGINE =
InnoDB");
PREPARE stmt FROM @stmt_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END $$

DELIMITER ;

Hi,
just try like:
mysql> create procedure mi()
-> begin
-> declare done int default 0;
-> declare table_name varchar(50);
-> declare cur1 cursor for select tables.table_name from
information_schema.tables where table_schema='test' and table_type='BASE
TABLE' and engine='MyISAM';
-> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
-> open cur1;
-> repeat
-> fetch cur1 into table_name;
-> set @table_name=table_name;
-> if not done then
-> set @stext=CONCAT("Alter table " , @table_name, " engine=InnoDB");
-> prepare smt from @stext;
-> execute smt;
-> deallocate prepare smt;
-> end if;
-> until done end repeat;
-> close cur1;
-> end;
-> |
Query OK, 0 rows affected (0.03 sec)

mysql> call mi();
Query OK, 2 rows affected (0.01 sec)

+-----------+
| version() |
+-----------+
| 5.0.18 |
+-----------+
1 row in set (0.00 sec)

Thanks
ViSolve DB Team.
----- Original Message -----
From: "Bryan Cantwell" <bcantwell@firescope.net>
To: <mysql@lists.mysql.com>
Sent: Friday, June 29, 2007 4:03 AM
Subject: dynamic sql in proc


>I have the following proc... when I run it I get a response that says
> "You have an error in your SQL syntax; check the manual that corresponds
> to your MySQL server version for the right syntax to use near 'NULL' at
> line 1".
>
> I just want a programatic way to upgrade db engine to innodb where I
> don't know exactly what tables exist...
>
>
> DELIMITER $$
>
> DROP PROCEDURE IF EXISTS `firescope`.`MYISAMtoINNODB` $$
> CREATE PROCEDURE `MYISAMtoINNODB`()
> BEGIN
> DECLARE done INT DEFAULT 0;
> DECLARE table_name VARCHAR(255);
> DECLARE cur1 CURSOR FOR select table_name from information_schema.tables
> where table_schema='firescope' and table_type='BASE TABLE' and
> engine='MyISAM';
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
>
> OPEN cur1;
> REPEAT
> FETCH cur1 INTO table_name;
> SET @table_name=table_name;
> IF NOT done THEN
> SET @stmt_text=CONCAT("ALTER TABLE ", @table_name, " ENGINE =
> InnoDB");
> PREPARE stmt FROM @stmt_text;
> EXECUTE stmt;
> DEALLOCATE PREPARE stmt;
> END IF;
> UNTIL done END REPEAT;
> CLOSE cur1;
> END $$
>
> DELIMITER ;
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=m...rt@visolve.com
>
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.476 / Virus Database: 269.9.10/875 - Release Date: 6/27/2007
> 9:08 PM
>
>