Determine if GPS coordinate in Canada

Question

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?

Answer 1

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.