MySQL Search for GeoLocation Overlaps?

Question

I am struggling with a semi-efficient way to do the following. Ideally through MySQL.

Each search that is performed for this system includes a geo coordinate and a range for that individual. (So essentially a radius and center point).

I want to be able to do a search for another coordinate, and get back a count of how many of those searches included that area.

What is the best way to store the data to make this possible? And how would I structure a query to get that result?

Example:

DATA
Search 1 [-56.1, 43.2], 100 mile radius
Search 2 [-78.23, 45.1], 250 mile radius
Search 3 [-80.1, 44], 10 mile radius
Search 4 [-81.2, 44.4], 50 mile radius

How many searches covered point [-80.1, 44]

We are using spatial types, but I am not sure how that would work for this type of search? Perhaps I am missing something. Did you have something specific in mind? — RiddlerDev, Jul 30 '18 at 16:01
That works for polygons... not sure how I would store my circles? That seems to be along the right track though — RiddlerDev, Jul 30 '18 at 16:54
there is not a circle function in mysql .. but you could build a poligon with several point that is similar with a circle .. — ScaisEdge, Jul 30 '18 at 17:07
Yeah, not perfect, but that should at least give me close to expected behavior. Thanks. If you want to add that to an answer, I can approve it. — RiddlerDev, Jul 30 '18 at 17:17

ScaisEdge · Accepted Answer · 2019-10-01T15:29:52.283

1

you could take a look at spatial data types .. for mysql and fot these You could use mysql st_contain function but due the reason that mysql have not a circle funtion you could could build a poligon with several point that is similar with a circle and use this aproximate . this is not a exact solution but pratically shoudl give you the a result near as you expected

edited Oct 01 '19 at 15:29

answered Jul 30 '18 at 17:58

ScaisEdge

131,976
10
91
107

MySQL Search for GeoLocation Overlaps?

1 Answers1