Placing datapoints in a grid

[jcfraun]

I have a table (AllGridPoints) with a defined latitude/longitude grid for the US(864,363 rows). I also have another table (ZipCodes) listing zip codes and their latitude/longitude. I need to place these zip codes within the established grid. Any ideas on how to get this done efficiently?

AllGridPoints:
ID
X (Integer)
Y (Integer)
Latitude (Double)
Longitude (Double)

ZipCodes:
ID
ZipCode (Text)
Latitude (Double)
Longitude (Double)

Thanks!

 

[SkipVought]

Hi,

What application & version?

You have 2 tables and one grid: yes?

What kind of object is your grid, in what object?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jcfraun]

I am using Access 2003.

I have two tables and no official grid. Only a grid in the sense that a grid is defined in one of the tables. For instance, the table AllGridPoints just contains all of the grid points (i.e., latitude/longitude) in my grid.

Is that enough info?

Thanks!
Caryn

 

[SkipVought]

So you have no GRID.

You simple have 2 tables?

So why wouldn't your query be...

Select A.Latitude, A.Longitude, B.ZipCode

From AllGridPoints A, ZipCodes B

Where A.Latitude = B.Latitude
  AND A.Longitude= B.Longitude

If you want to keep all the rows in AllGridPoints, then make it an outer join.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jcfraun]

The reason it isn't that simple is that the points in ZipCodes do not exactly match up with the points in AllGridPoints. I need each Zipcode to have an assigned gridpoint. So, I need to find the latitude/longitude point in AllGridPoints that is closest to each identified zip code.

I can calculate the distance from each zipcode point to each gridpoint, but the number of rows is too large for that to be efficient at all.

I did have the thought to either truncate or round each of the latitude/longitude points and then I could place them with some level of accuracy with a simple query like you've described.

Any other ideas?