Upper limit of geography data type conversion?

[OwenNeedsVBAHelp]

Hi,

I am trying to find the distance between zip codes in separate columns in a work book. To do that I need to convert a full column of text zip codes into the geography data type.
The problem is that my dataset is large, 300k+ rows, and Excel cannot handle converting the entire column at once, and ultimately converts no values at all. The error message tells me that the selection is too large to convert to geography data, and recommends I try again with a smaller selection of cells.

Does anyone know what the upper limit of values in a selection would be to convert text zip codes -> geography zip codes? After some extensive googling and a few YouTube tutorials, I haven't seen this question addressed. I can condense the data, but not by much and it would take some legwork. Trying to avoid that sort of trial/error approach to save a few hours.

Thanks in advance for your time.

 

[Bosquedeguate]

Hi,

I am trying to find the distance between zip codes in separate columns in a work book. To do that I need to convert a full column of text zip codes into the geography data type.
The problem is that my dataset is large, 300k+ rows, and Excel cannot handle converting the entire column at once, and ultimately converts no values at all. The error message tells me that the selection is too large to convert to geography data, and recommends I try again with a smaller selection of cells.

Does anyone know what the upper limit of values in a selection would be to convert text zip codes -> geography zip codes? After some extensive googling and a few YouTube tutorials, I haven't seen this question addressed. I can condense the data, but not by much and it would take some legwork. Trying to avoid that sort of trial/error approach to save a few hours.

Thanks in advance for your time.

Years ago I worked on a system for the USPS. They provided the latitude and longitude for each 5-digit zip code. With this value you can calculate the distance between two zip codes. Is this the kind of geo data that you have?

 

[OwenNeedsVBAHelp]

Years ago I worked on a system for the USPS. They provided the latitude and longitude for each 5-digit zip code. With this value you can calculate the distance between two zip codes. Is this the kind of geo data that you have?

I just have zip codes, which I need to convert to the geography data type within excel in order to extract the long/lat, then from there I plan to use radians to calculate distance, which I assume is what you did at USPS but luckily you had long/lat already and no need for the geo data type

 

[Bosquedeguate]

I just have zip codes, which I need to convert to the geography data type within excel in order to extract the long/lat, then from there I plan to use radians to calculate distance, which I assume is what you did at USPS but luckily you had long/lat already and no need for the geo data type

Regarding your response to my question ... Yes! I worked on a contract for the USPS for 14 years where we developed the first retail systems for the USPS (they were in service from 1988 to 2022). The system needed to calculate Zone Charts for each origin PO to the destination Post Offices. So we had a database, provided by the USPS, of the 3-digit ZIP code latitude and longitudes. With this information we could calculate the Zones (distances from one 3-digt (first 3 digits of a ZIP code) to another ZIP or Zone.

I assume this data is still available from the USPS for people doing software development and systems development of various kinds of postal applications. Have you contacted the USPS to see if the information you need is available?

I think someone at the USPS National Customer Service Center might be able to tell you if what you need is available from the USPS.
800-331-5747 or devsupport.ncsc@usps.gov;
USPS Order Form for different types of data.
I hope this helps!!