1

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)
4
  • Mark, please update your question with what exactly is not working, error messages and an example of the data (in plain text). Also, I assume you are using postgresql, not mysql, so please update the tags as well.
    – tilt
    Jul 25, 2017 at 12:48
  • I'm using mysql. I'm not getting an error message the query just runs for infinite amount of time and does not display any output, But when I hard code the second parameter(the geojson polygon parameter) it fetches the data with out any issues. Jul 25, 2017 at 17:52
  • In that case the postgis tag is misleading. Are you sure there is 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?
    – tilt
    Jul 25, 2017 at 20:04
  • My apologies about the postgis tag. I have close to 4 million records in my cities table and 247 records in my polygons table. Yup I just realized the issue is due to the query taking extremely long to load. I created a spatial index for my points and created a new geometry field for the polygons, For instance I just ran a query before and after 30 minutes I stopped the query, sql only loaded 40,000 records. Is the long load time normal in my case? Do you have any recommendations on optimizing the query response time? P.S I'm editing my question with the results of the EXPLAIN function. Jul 26, 2017 at 2:58

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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