Convert lat/lng pairs using GeomFromText('POINT(1 1)') and insert in another column
In my previous question Search for range Latitude/Longitude coordinates My solution was to create the table below.
mysql> select * from spatial_table where MBRContains(GeomFromText('LINESTRING(9 9, 11 11)'), my_spots);
+------+---------------------------------+
| id | my_spots | my_polygons |
+------+-------------+-------------------+
| 1 | $@ $@ $@ $@ |
+------+-------------+-------------------+
Now I need to convert and move my existing lat/lng pairs in the table below to spatial_table. How would I structure m开发者_如何学Goy query to acheive this? I am currently using the queries below to insert.
mysql> insert into spatial_table values (1, GeomFromText('POINT(1 1)'), GeomFromText('POLYGON((1 1, 2 2, 0 2, 1 1))'));
Query OK, 1 row affected (0.00 sec)
mysql> insert into spatial_table values (1, GeomFromText('POINT(10 10)'), GeomFromText('POLYGON((10 10, 20 20, 0 20, 10 10))') );
Query OK, 1 row affected (0.00 sec)
Existing table:
+-------------+---------+--------+-----------+----- ------+-------------+--------------+
| location_id | country | region | city | latitude | longitude | name |
+=============|=========|========|===========|============|=============|==============|
| 316625 | US | CA | Santa Cruz| 37.044799 | -122.102096 | Rio Theatre |
+-------------+---------+--------+-----------+------------+-------------+--------------+
Here is the secret recipe to success :) My original table:
mysql> describe gls;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| location_id | int(255) | NO | PRI | 0 | |
| country | varchar(255) | NO | | | |
| region | varchar(255) | NO | | | |
| city | varchar(255) | NO | | | |
| latitude | float(13,10) | NO | | | |
| longitude | float(13,10) | NO | | | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
step 1: Add new POINT column
mysql> alter table gls add my_point point;
Query OK, 247748 rows affected (4.77 sec)
Records: 247748 Duplicates: 0 Warnings: 0
Step 2: Update my_point with values from lat/lng fields.
UPDATE gls SET my_point = PointFromText(CONCAT('POINT(',gls.longitude,' ',gls.latitude,')'));
Step 3: check
mysql> select aswkt(my_point) from gls where city='Santa Cruz';
+--------------------------------------+
| aswkt(my_point) |
+--------------------------------------+
| POINT(-122.1020965576 37.0447998047) |
| POINT(-66.25 -12.2833003998) |
| POINT(-2.3499999046 42.6666984558) |
+--------------------------------------+
Let's say you have a table like this:
mysql> select * from spatial_table;
+------+---------------------------+-----------------------------------------------------------------------------------+---------+--------+
| id | my_spots | my_polygons | lon | lat |
+------+---------------------------+-----------------------------------------------------------------------------------+---------+--------+
| 1 | ?? ?? | ?? ?? @ @ @ ?? ?? | -122.11 | -37.11 |
| 1 | $@ $@ | $@ $@ 4@ 4@ 4@ $@ $@ | -122.11 | -37.11 |
+------+---------------------------+-----------------------------------------------------------------------------------+---------+--------+
2 rows in set (0.00 sec)
If you want to make a geometry column with the lon lat values (as points only, syntax is a little different for other kinds of geometries), you can do this:
mysql> alter table spatial_table add column (go_slugs geometry);
This is a geometry type, if it is all single locations you could make the column type point
. Then just update the new column:
mysql> update spatial_table set go_slugs = point(lon, lat);
Use the aswkt function to get human readable data to confirm this is correct:
mysql> select aswkt(go_slugs) from spatial_table;
+-----------------------------------------------+
| aswkt(go_slugs) |
+-----------------------------------------------+
| POINT(-122.11000061035156 -37.11000061035156) |
| POINT(-122.11000061035156 -37.11000061035156) |
| POINT(-123.4000015258789 37.79999923706055) |
+-----------------------------------------------+
3 rows in set (0.00 sec)
精彩评论