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)
ST_GeomFromWKB(ST_GeomFromGeoJson
in your second query? It might be that you simply have so many records the query takes very long. Can you tell how many records there are? And what is the output when you put 'EXPLAIN ANALYZE' in front of your query?