Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a series of geolocation points in a table, and a series of "zones" in another; I'd like to do a join to identify which zone each point belongs in (if at all.)

Points table looks like:

+--+--------+---------+
|id|Latitude|Longitude|
+--+--------+---------+
| 1|37.23221|-81.32323|
+--+--------+---------+    
| 2|36.92321|-81.12423|
+--+--------+---------+
| etc                 |
+--+--------+---------+      

Zones table looks like:

+--+------+------+------+-----+-----+
|id|Name  |LONG_E|LONG_W|LAT_N|LAT_S|
+--+------+------+------+-----+-----+
| 1|Zone 1| -81.0| -82.0| 38.0| 37.0|
+--+------+------+------+-----+-----+
| 2|Zone 2| -81.0| -82.0| 37.0| 36.0|
+--+------+------+------+-----+-----+
| etc                               |
+--+------+------+------+-----+-----+

I'd like to be able to query the points table and join the zones table, to get a result that looks like:

+----+----------+-----------+----+------+
|p.id|p.latitude|p.longitude|z.id|z.name|
+----+----------+-----------+----+------+
|   1|  37.23221|  -81.32323|   1|Zone 1|
+----+----------+-----------+----|------+    
|   2|  36.92321|  -81.12423|   2|Zone 2|
+----+----------+-----------+----+------+
|etc                                    |
+----+----------+-----------+----+------+ 

I'm pretty rusty on my SQL syntax, so I'm wondering if this is possible...I'm using MySQL BTW. Thanks!

share|improve this question
    
Does p.id and z.id relate points and zones to each other? –  JChao Jun 11 at 21:07
    
nvm Joshua's solution answers my question. –  JChao Jun 11 at 21:13
add comment

1 Answer 1

up vote 3 down vote accepted

This should do it:

SELECT p.id, latitude, longitude, z.id, z.name
  FROM points p
  LEFT JOIN zones z
    ON ( latitude BETWEEN LAT_S AND LAT_N
          AND
         longitude BETWEEN LONG_W AND LONG_E ) ;

[EDIT]

Per your comment, suppose you want to add a FK column to your points table that associates the point to its zone id. There are some reasons why this would be not recommended, such as if your zone boundaries could ever change, you would have incorrect/inconsistent data. However for raw performance, maybe you want the zone id denormalized into the points table, keeping in mind that it's like a cached zone id. The MIN() operator is there to protect you in case you have two zones overlapping. In that case, you've got bigger problems, but at least the UPDATE works, and picks the lower zone id by fiat.

ALTER TABLE points ADD (zone_id int references zone(id));

UPDATE points SET zone_id = 
( SELECT MIN(z.id)
  FROM zones z
  WHERE latitude BETWEEN LAT_S AND LAT_N
          AND
        longitude BETWEEN LONG_W AND LONG_E
);
share|improve this answer
    
Joshua - this works wonderfully. Thanks. What if I were to want to add a column to the points table for the z.id as a foreign key, and update that column with the foreign key? –  andrewniesen Jun 11 at 21:41
    
Not sure you'd want to do that, but warnings aside, see (EDIT) above. –  Joshua Huber Jun 11 at 21:52
    
This is really helpful. Thanks Joshua... –  andrewniesen Jun 11 at 21:56
    
Yw. Upvote and accept answer if you likey. –  Joshua Huber Jun 11 at 21:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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