Get polygons close to lat, long in MySQL

Question

Does anyone know a way to get all polygons in MySQL db at a given distance from a point? The actual distance is not so important, since it is calculated for each polygon found later, but it would be a huge optimization to just perform this calculation for "close" polygons.

I looked at the MBR and it contains functions, but the problem is that some of the polygons are not contained in the bounding box drawn around the point, since they are very large, but some of their vertices are still close.

Any suggestions?

Answer 1

Slow version (no spatial indexes):

SELECT * FROM mytable WHERE MBRIntersects(mypolygon, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance)) 

To use spatial indexes, you need to denormalize the table so that each vertex of the polygon is stored in its own record.

Then create a SPATIAL INDEX in a field that contains the coordinates of the vertices, and just run this query:

 SELECT DISTINCT polygon_id FROM vertices WHERE MBRContains(vertex, LineString(Point(@X - @distance, @Y - @distance), Point(@X + @distance, @Y + @distance)) 

Everything will be much simpler if you save the UTM coordinates in your database, rather than latitude and longitude.

Answer 2

I do not think there is one answer to this. This is usually a question about how to organize your data so that it uses the spatial locality inherent in your problem.

The first idea that appears in my head is to use a grid, assign each point to a square, and check the selection of the square where the point is located and those around it. If we are talking about infinite grids, then use the hash value of the square, this will give you more points than you need (where you have collisions), but still reduce the amount per heap. Of course, this is not immediately applicable to landfills, it is just a brainstorm. A possible approach that could lead to too many collisions would be to have all the hashed values ​​together and select all the entries in which the ANDed hashes with this value are nonzero (not sure if this is possible in MySQL), you can use a lot of bits though.

The problem with this approach is that we are talking about spherical coordinates (lat, as a rule, is long) - these are features, since the grid squares grow as you approach the poles. Easy approach to this ... don't put dots close to the poles ... :)

Answer 3

Create a bounding box for all polygons and (optionally saving these results in a database will make it much faster for complex polygons). Then you can compare the bounding box for each polygon with the one around the point with the desired size. Select all the polygons that intersect the bounding rectangles.