Complex MySQL join from old table to new

Question

I have an old database named locations with a table inside named locations.

This table has 7 variables: id (int, index), approved(int), city(text), country(text), heading(double), lat(double), lng(double).

I have a new database named complexity. That database has a new table named gsvraw

I need to copy the old table to the new table, however this is where it gets tricky:

The new database is going to use the spatial indexing of MySQL so that I can search within a polygon later. In the new database, instead of lat, lng, I want to add a new column that's called location.

The example statement for creating a new column:

ALTER TABLE gsvraw ADD location POINT NOT NULL;
CREATE SPATIAL INDEX location ON gsvraw (location);

This is an example statement for inserting a new value into the location column:

INSERT INTO gsvraw (location) VALUES( GeomFromText( 'POINT(40 -100)' ));

40 and -100 would be lat and lng.

Any ideas?

I wasted an hour each of three different "guru's" here at MIT and literally got nowhere.

The server I'm running is causing major problems so using python and MySQLdb is out.

Hopefully someone out there is smarter than us and can help with the raw sql statements.

I'm running this on a Mac with Apache MySQL and PHP using MAMP pro. This causes some trouble so really the only solutions that could work are straight php and sql at this point. Any ideas?

Solution:

<?php
$con = mysql_pconnect("localhost","username","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("database1", $con);


$result = mysql_query("SELECT * FROM database1 WHERE ") 
or die(mysql_error());  

$con2 = mysql_pconnect("localhost","username","password");
if (!$con2)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("complexity", $con2);

while($row = mysql_fetch_array( $result )) {

    mysql_query("INSERT INTO table1 (id, approved, city, country, heading, location) VALUES ('".$row['id']."', '".$row['approved']."', '".$row['city']."', '".$row['country']."', '".$row['heading']."', PointFromText('POINT(".$row['lng']." ".$row['lat'].")'))", $con2);

} 
?>

Answer 1

Have you tried the PointFromText function?

set location = PointFromText(CONCAT('POINT(',latitude,' ',longitude,')'));