CODEWITHSUNDEEP
mysqlcoordinatesbounds
MacMac
MacMac

Reputation: 35341

Convert or use north, east, south and west coords to north-east and south-west for checking in-bounds

I have two tables, one table which contains countries with their north, east, south and west coords of a rectangle of each country, this is the structure,

+--------------+----------------------+------+-----+---------+----------------+
| Field        | Type                 | Null | Key | Default | Extra          |
+--------------+----------------------+------+-----+---------+----------------+
| boundary_id  | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| country      | varchar(100)         | NO   |     | NULL    |                |
| country_code | varchar(4)           | NO   |     | NULL    |                |
| geo_west     | float(10,6)          | NO   |     | NULL    |                |
| geo_north    | float(10,6)          | NO   |     | NULL    |                |
| geo_south    | float(10,6)          | NO   |     | NULL    |                |
| geo_east     | float(10,6)          | NO   |     | NULL    |                |
+--------------+----------------------+------+-----+---------+----------------+

Now, for the other table is stored locations of users, I need their location (latitude and longitude) to match against the table to check whether they fall in the desired country chosen. In other words, only pick up the users that fall within the bounds of the chosen country.

This is the data from the table (selected 4),

+-------------+----------------+--------------+-------------+------------+------------+-------------+

| boundary_id | country | country_code | geo_west | geo_north | geo_south | geo_east | +-------------+----------------+--------------+-------------+------------+------------+-------------+ | 218 | Afghanistan | AF | 60.478436 | 38.483425 | 29.377470 | 74.879463 | | 224 | Albania | AL | 19.293968 | 42.665615 | 39.648354 | 21.068476 | | 188 | Algeria | DZ | -8.673869 | 37.093727 | 18.960026 | 11.979549 | | 229 | American Samoa | AS | -170.841339 | -14.162115 | -14.382480 | -169.416061 | +-------------+----------------+--------------+-------------+------------+------------+-------------+

The user table,

+---------+-----------+-----------+
| user_id | latitude  | longitude |
+---------+-----------+-----------+
|       6 | 51.495010 | -0.071583 |
|       7 | 51.204731 |  1.251486 |
|       8 | 51.261860 |  1.327887 |
|       9 | 51.487850 | -0.071926 |
|      10 | 51.498699 | -0.201187 |
|      11 | 51.436462 | -0.100937 |
|      12 | 51.331093 |  0.829124 |
|      13 | 51.565121 | -0.065918 |
|      14 | 51.493568 | -0.248566 |
+---------+-----------+-----------+

Would I need to convert the coords to north-east and south-west or not and how to effectively match against those?

I prefer to avoid geolocation services due to their limits.

Upvotes: 1

Views: 1317

Answers (3)

tomfumb
tomfumb

Reputation: 3759

@Mike Brant has just described the main point I was going to raise.

MySQL supports spatial data types so I suggest you take the following approach:

  1. Find a source of country bounding polygons online (Googling would probably get you most of the way there)
  2. Load these polygons into MySQL and store them as spatial features (geometry objects, not just basic coordinate strings)
  3. Store your user locations as spatial features
  4. Use MySQL's spatial relation functions to determine whether a user (point) is within a country (polygon)

If this is the path you want to take you should consider moving over to http://gis.stackexchange.com

Upvotes: 0

Tuncay Göncüoğlu
Tuncay Göncüoğlu

Reputation: 1767

Just an idea... Get a 360x360 bitmap that holds a world map. Paint every country with a different color in RGB, like, #010000 for id 1 country, #020000 for id 2 country etc (yes except water, entire world would end up in shades of red). Then when you want to check a user, take the x-y pixel (remember, it will go from -180 to +180, so it actually will be x+180, y+180), find the color of that pixel and from that the country ?

Preparing that bitmap might be a lot of work, but once that is done, rest should be quite easy.

As I said, just an idea.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71424

You should probably be using some sort of geolocation service for this (i.e. Google Maps). Just knowing the northernmost, southernmost, etc. coordinates of a country does not imply that shape of the country at all.

For example the northernmost coordinates of the US (in Alaska), the southern and westermost coordinates (Hawaii), and the eastermost (Maine) would draw a huge rectangle which could also include large swaths of Canada, Mexico, the Caribbean, etc.

How would you deal with all the overlaps if treating countries like rectangles?

Upvotes: 1

Related Questions