Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have GPS tracking solution and most my customers in US. My reverse-geocoding solution pretty basic:

  1. Table with US ZIP database
  2. Procedure in SQL Server where I search for city/state closest to reported lat/lon

Now I need to add Canada to my service coverage. I will buy Canadian postal codes and plan to store it in separate table.

So, straight-forward solution will be to:

  1. Check if there is close US city/state and report
  2. If not found - check in Canada table

This solution will put extra load on server for those canadian users since US table will be queried first.

For various reasons I don't want to merge tables.

However, if you look at map - you can tell that Canada is above USA :)

So, I was wondering if I can use some hard-coded numbers to know immedeately where this point is.

Right? Wrong? Other ideas on how to tackle this?

share|improve this question
1  
how about adding a Countries Geometry data in SQL and performing an intersect on GPS point and Countries? –  vinayan Jul 13 '12 at 2:54
1  
Detroit is above Windsor, so Canada is not always above USA. –  Mike T Jul 13 '12 at 3:09
    
Countries geometry sounds good. Where do I get it? –  katit Jul 13 '12 at 3:18
    
North America Shapefile gis.stackexchange.com/questions/22863/… just filter for Canada –  Mapperz Jul 13 '12 at 13:45
add comment

1 Answer

up vote 2 down vote accepted

There is an obvious flaw in your algorithm for step 2. Consider, say for instance, Airport Rd, WA where it intersects on the eastern end with the Pullman Hwy map. The nearest city is Moscow, ID which is not necessarily where the mail comes from considering it is a separate state.

If, however, you wish to carry on with that method, you can obtain the Canadian border here (non-commercial purposes). You'll need to import the shapefile to SQL Server. Google will help you there. Be sure to assert that it is not inclusive of Alaska.

I would suggest, in favor of accuracy, that you get the -actual- zip code boundaries, even though they may be a bit out of date, they will at least get you to the relative area. Get those here. Do the same for the Canadian postal codes, Google.

To answer your question, the following code will tell you if a point is inside a table of shapes:

DECLARE @gpsPoint geometry = geometry::STGeomFromText('POINT(40, -74)');
SELECT [code] FROM postcodes WHERE [boundary].STIntersection(@gpsPoint);

or, if you don't take my advice:

DECLARE @gpsPoint geometry = geometry::STGeomFromText('POINT(40, -74)');
SELECT @inCanada = COUNT(*) > 0 FROM canadianBoundary WHERE [boundary].STIntersection(@gpsPoint);

There are alternate 'geo' operations you could preform such as Disjoint, Within, etc. Check those here.

If you have stored all your zip and postal codes in the same table, then for efficiency sake, where you are concerned, simply add an additional element to the WHERE clause.

SELECT [code] FROM postcodes
  WHERE [boundary].STIntersection(@gpsPoint)
  AND [country] = 'USA';

Otherwise, query each table independently.

share|improve this answer
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.