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

Question

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.

Mike Brant Mike Brant · Accepted Answer · 2012-09-11T17:13:16

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?

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

3 Answers