I'm trying to get all points that are within each polygon from my database, but my sql query is not working. Can some one please help me out?
The following is my sql code.
Column cities_main_db.cities.geo_points has a data type of point
Column geo_nm_prem.polygons has a data type of json as it contains geojson polygons.
SELECT * FROM cities_main_db.cities, geo_nm_prem.polygons
where ST_within(ST_GeomFromWKB(cities_main_db.cities.geo_points),ST_GeomFromGeoJson(geo_nm_prem.polygons.geojson));
Edit: I'm not getting an error, but the query says its running, but runs for an infinite amount of time.
When I hard code the second parameter which is the geojson polygon parameter I get no issues and my data fetches fine. The following is the hard coded parameter.
SELECT * FROM cities_main_db.cities, geo_nm_prem.polygons
where ST_within(ST_GeomFromWKB(cities_main_db.cities.geo_points),ST_GeomFromWKB(ST_GeomFromGeoJson('{"type": "Polygon", "coordinates": [[[21.498, 56.29], [21.229, 56.161], [21.2, 56.077], [21.064, 56.069], [20.984, 56.218], [20.982, 56.523], [21.056, 56.837], [21.393, 57.022], [21.419, 57.292], [21.709, 57.572], [22.604, 57.758], [22.573, 57.684], [22.608, 57.606], [22.93, 57.421], [23.13, 57.361], [23.267, 57.088], [23.59, 56.969], [23.734, 56.967], [23.973, 57.022], [24.401, 57.245], [24.355, 57.874], [25.2, 58.086], [25.256, 57.994], [25.302, 57.993], [25.265, 58.06], [25.298, 58.081], [25.685, 57.904], [26.055, 57.848], [26.024, 57.769], [26.202, 57.714], [26.273, 57.599], [26.478, 57.571], [26.525, 57.516], [26.616, 57.512], [26.737, 57.587], [26.775, 57.56], [26.907, 57.633], [27.341, 57.521], [27.556, 57.538], [27.516, 57.425], [27.867, 57.296], [27.837, 57.162], [27.713, 57.103], [27.77, 57.084], [27.719, 57.004], [27.765, 57.004], [27.66, 56.834], [27.848, 56.879], [27.902, 56.821], [27.967, 56.838], [27.902, 56.751], [28.02, 56.679], [28.043, 56.592], [28.147, 56.572], [28.095, 56.518], [28.193, 56.448], [28.164, 56.379], [28.241, 56.28], [28.175, 56.178], [27.98, 56.123], [27.901, 56.047], [27.808, 56.036], [27.804, 55.976], [27.657, 55.932], [27.62, 55.786], [27.288, 55.785], [27.132, 55.848], [26.921, 55.786], [26.783, 55.677], [26.653, 55.707], [26.597, 55.675], [26.378, 55.705], [26.045, 55.954], [25.691, 56.085], [25.673, 56.149], [25.092, 56.188], [24.886, 56.451], [24.632, 56.375], [24.579, 56.288], [24.453, 56.258], [24.316, 56.3], [24.116, 56.251], [24.019, 56.33], [23.766, 56.324], [23.775, 56.372], [23.545, 56.332], [23.311, 56.383], [23.093, 56.305], [22.969, 56.414], [22.685, 56.354], [22.136, 56.431], [21.498, 56.29]]]}')));
Additional information:
Please note the hard-coded parameter is geojson I borrowed it from my database. It has a data type of json in my table.
Here's an example of a points parameter I borrowed from my db POINT(27.6443 57.1781) its has a mysql data type of point.
Edit:
I realized the issue was with the response time taking way to long.
As promised in my comment the following are the results up the Explain Function.
1 SIMPLE polygons ALL 180 100
1 SIMPLE cities ALL 3958522 100 Using where; Using join buffer (Block Nested Loop)
Best Answer
Although I am not a mysql user, I am pretty sure this can be solved by building an index on your geometry columns. Currently it is doing an 'Block Nested Loop' that checks every geom against every other. When 4 million points have to be checked against 247 polygons for falling inside or outside the polygon, that takes time. The index will first check if it falls inside the bounding box.
Some other tricks that will help:
Check the mysql manual on how to do the indexex: https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html