2

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 |
+-------------------------+
  • UPDATE data SET start_point = PointFromText(CONCAT('POINT(',longitude_beginning,' ',latitude_beginning,')'),1) → with this I have same srid in the shapes and in the point wohoooo! – fedecupe Mar 31 '17 at 9:40
  • 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
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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.