Select all points within a a geojson polygon from Mysql

Question

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)

Answer 1

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:

- store your geometries as mysql geometry (not as geojson)
- use ST_Intersects instead of ST_Within (since points can not be overlapping anyway)
- build spatial indexes on all geometry fiels (as said )

Check the mysql manual on how to do the indexex: https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html