When geographically mapping data, for business or (unlikely) pleasure, a common file format used is the ESRI “shapefile”, typically recognised by ending with .shp. This file format allows one to specify custom polygons, with associated data. For instance, the counties of the United Kingdon, the territories of your business, or the areas covered by sales reps - anything effectively 2-dimensional. Alongisde the polygon spec you can store data – an obvious example being the name of the county in case 1 above.

What is less joyous though is generally the packages that handle these files with any form of style and grace are not free, or even cheap. There are some freeish viewers of various quality out there but if you wish to automate any sort of activity on these often gigantic files then usually it doesn’t come cheap. However, the Poorhouse did discover a free GIS program called MapWindow which is probably not a bad program in itself, but most usefully it contains a free GIS ActiveX control complete with API. This means you can link to it with any application that can interact programmatically with such things, not least the Microsoft Office applications suite.

A task cropped up where it became important to take a long list of longitude / latitude co-ordinates and work out, for each one, which custom polygon in the shapefile it belongs in. Not relishing examining a map and partially guessing for each one (not having the best sight or boredom threshold) it proved simple to use this control from Microsoft Access to go through each point and automatically store the name of the polygon it is in. It should work in any other program too, but here’s an Access VBA example solution.

Firstly, after installing the MapWindow control (you need at least “power user” access to do so) you should, in the VBE editor of your database, add a reference via the Tools -> References menu. It’s called “MapWinGIS Components” at present.

Now you have access to the MapWin objects. And guess what, there is a built in function “pointinshapefile” that takes a point (co-ordinates) and returns the relevant polygon from whichever shapefile you choose to load. Not entirely sure what happens if the point occurs in more than 1 polygon within the file, but that wasn’t an issue in the case at hand.

Below is a simple function you can paste into such an application to steal this point-to-polygon functionality. Be sure to change the 2 bits of information within square brackets to your filename and field index respectively. Field index is a number that says which field of the polygon with the point in you wish to return. For instance, if you store the name of the county in field 2, you should use 2 as your [field_index]. If you’re not sure, a bit of trial and error doesn’t hurt, starting low generally.

After including this in your project simply use it by calling

If the point isn’t in any of the polygons in your file (for instance, outside the boundaries of the UK if you only have the UK county shapefile) the function return will be the text “Could not find co-ordinates in shapefile”. Otherwise it will be the data in the specified field index.

As proof of concept, below you can download a mini-app wherein you can select your shapefile and either enter 1 set of co-ordinates along with a field index as above. Then it will locate the relevant data of the shape. Alternatively it can batch process a list if you have a lot to do. You must have MapWindow’s control installed first (as well as Microsoft Access) for this to work.

The batch mode will take the co-ordinates you enter in table tblInputCoords (put whatever you want as UID, just something to uniquely identify the record), and dump them out in tblOutputCoords complete with the name of the shape each of those co-ordinates are in, in the field “location” here.