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 |
+-------------------------+
SELECT data.id, plygons.id_nil FROM polygons, data WHERE ST_CONTAINS(polygons.SHAPE, data.start_point)
→ this outputs id and respective polygon id of the starting point. How do I pipe this in a new column? – fedecupe Mar 31 '17 at 9:54