Convert lat/lon mysql columns to geometry, check if point is in polygon, return polygon id

Question

I have a mysql database table with many ids, each id has a "start lat", "start lon" "end lat" and "end lon" column. I want to check in wich polygon the start point belongs (and write this in a "start polygon" column) same for end point. I have a table with polygon geometries.

+------------------+
| Tables_in_sng    |
+------------------+
| geometry_columns |
| polygons         |
| spatial_ref_sys  |
| data             |
+------------------+
mysql> describe geometry_columns;
+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| F_TABLE_CATALOG   | varchar(256) | YES  |     | NULL    |       |
| F_TABLE_SCHEMA    | varchar(256) | YES  |     | NULL    |       |
| F_TABLE_NAME      | varchar(256) | NO   |     | NULL    |       |
| F_GEOMETRY_COLUMN | varchar(256) | NO   |     | NULL    |       |
| COORD_DIMENSION   | int(11)      | YES  |     | NULL    |       |
| SRID              | int(11)      | YES  |     | NULL    |       |
| TYPE              | varchar(256) | NO   |     | NULL    |       |
+-------------------+--------------+------+-----+---------+-------+

mysql> describe spatial_ref_sys;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| SRID      | int(11)       | NO   |     | NULL    |       |
| AUTH_NAME | varchar(256)  | YES  |     | NULL    |       |
| AUTH_SRID | int(11)       | YES  |     | NULL    |       |
| SRTEXT    | varchar(2048) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+

mysql> describe polygons;
+--------------+----------+------+-----+---------+----------------+
| Field        | Type     | Null | Key | Default | Extra          |
+--------------+----------+------+-----+---------+----------------+
| OGR_FID      | int(11)  | NO   | PRI | NULL    | auto_increment |
| SHAPE        | geometry | NO   | MUL | NULL    |                |
| name         | text     | YES  |     | NULL    |                |
| description  | text     | YES  |     | NULL    |                |
| timestamp    | datetime | YES  |     | NULL    |                |
| begin        | datetime | YES  |     | NULL    |                |
| end          | datetime | YES  |     | NULL    |                |
| altitudemode | text     | YES  |     | NULL    |                |
| tessellate   | int(11)  | YES  |     | NULL    |                |
| extrude      | int(11)  | YES  |     | NULL    |                |
| visibility   | int(11)  | YES  |     | NULL    |                |
| draworder    | int(11)  | YES  |     | NULL    |                |
| icon         | text     | YES  |     | NULL    |                |
| descrizione  | text     | YES  |     | NULL    |                |
| fid_1        | text     | YES  |     | NULL    |                |
| fid_1_1      | text     | YES  |     | NULL    |                |
| id_nil       | text     | YES  |     | NULL    |                |
| nil          | text     | YES  |     | NULL    |                |
| areaha       | text     | YES  |     | NULL    |                |
| areamq       | text     | YES  |     | NULL    |                |
+--------------+----------+------+-----+---------+----------------+

mysql> describe data;
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| id                  | int(7)        | YES  |     | NULL    |       |
| longitude_beginning | decimal(24,7) | YES  |     | NULL    |       |
| latitude_beginning  | decimal(24,7) | YES  |     | NULL    |       |
| longitude_end       | decimal(17,6) | YES  |     | NULL    |       |
| latitude_end        | decimal(18,5) | YES  |     | NULL    |       |
+---------------------+---------------+------+-----+---------+-------+

At first I tried using this SQL

SELECT data.id FROM polygons, data WHERE ST_CONTAINS(polygons.SHAPE, Point(data.longitude_start, data.latitude_start));

Then I tried creating a start_coords column and use this command to create the geometry

ALTER TABLE data ADD start_coords Point;

UPDATE  data
SET     start_coords = Point(longitude_begin, latitude_begin);

ALTER TABLE data MODIFY start_coords NOT NULL;

CREATE SPATIAL INDEX sx_data_start_coords ON data(start_coords);

But then it threw errors while checking if the points belonged to polygons (something about SRIDs being different)

To sum up: For every point (described as lat/long) I want to retrieve the polygon.nil (the polygon id) and write it in a dedicated field (creating start_polygon and end_polygon column)

Anyone has any clue and can help me?

mysql> SELECT @@VERSION;
+-------------------------+
| @@VERSION               |
+-------------------------+
| 5.7.17-0ubuntu0.16.04.1 |
+-------------------------+

Answer 1

I'm still not sure this is the best and fastest solution, but:

To create geometry from lat/lon:

UPDATE data SET start_point = PointFromText(CONCAT('POINT(',longitude_beginning,' ',latitude_beginning,')'),1) 

Then I execute two queries, creating a new table to store the results, to check to wich polygon every point belongs:

CREATE TABLE start_point SELECT data.id, polygons.id_nil FROM polygons, data WHERE ST_CONTAINS(polygons.SHAPE, data.start_point)
CREATE TABLE end_point SELECT data.id, polygons.id_nil FROM polygons, data WHERE ST_CONTAINS(polygons.SHAPE, data.end_point)

As an alternative I can use this statement (thus not creating a geometry column)

SELECT data.id, polygon.id_nil FROM polygon, data WHERE ST_CONTAINS(polygon.SHAPE, PointFromText(CONCAT('POINT(',`longitude_beginning`,' ',`latitude_beginning`,')'),1))

Last, I create the right columns in data and update them with values from start_point and end_point

ALTER TABLE viaggi ADD start_nil INTEGER;
ALTER TABLE viaggi ADD end_nil INTEGER;

UPDATE data INNER JOIN trip_start ON data.id = trip_start.id SET viaggi.start_nil = trip_start.id_nil;
UPDATE data INNER JOIN trip_end ON data.id = trip_end.id SET viaggi.end_nil = trip_end.id_nil;

This last step take a lot of time (on 30k rows it's well over 15 minutes).