Needing to find parent zip code of a bunch of missing zip codes in the US?

Question

We're using MapPoint to export zip code / territory combinations. Unfortunately, the export doesn't include PO Box zip codes which mean that we're missing a lot of zip codes The PO Box zip codes are contained within a parent zip code and so I just need to work out what the parent zip code is for each of the missing zip codes. I have 2 SQL tables: - ALL zip codes with Lat/Lng and Geography column. Also has a GeometryShape column - standard zip codes with Lat/Lng and Geography column. Couple of ways I was looking to do it... - either find the closest Geography point to each missing zip code and make the closest one the parent zip (using STDistance) OR - Convert the geometry shape to a geography shape of the standard zips, then use STIntersects to find which shape the missing zip lat/lng belongs to. Having a bit of bother with this, first time I've worked with geospatial querying in SQL

Answer 1

It's been a long while since I've done spatial querying so I can't give explicit answers. Your second approach would give you much cleaner data.

Are you sure that the intersection would be all inclusive to the parent zip? If so, you could simply use a that lat/long as a point (or even a point of the perimeter?) and see if it plots inclusively within the geometric shape of the parent zip. I have to think that would be a simpler query with a STContains.