HomeSoftware


Getting GPS coordinates into Microsoft Access
BirdGuy
We recently purchased tablets running Windows 7 and Microsoft Access for data collection on a wildlife research project (i.e., field personnel won't be connected via the internet and will basically be entering data in the middle of nowhere via Microsoft Access). We've developed various forms for data collection, but as in all research projects getting GPS coordinates for locations of animals, nests, etc is really important. The tablets have a built-in GPS unit which is great and some pretty basic software that allows us to view the current lats and longs, but I'd love to have personnel be able to click a button in a Microsoft Access form and the current Latitude and Longitude's are "imported" from GPS unit. Currently we're kind of stuck with individuals having to read the coordinates off of the GPS program that came with the tablet and copying them over to the Access form, which isn't ideal but will work.

Any suggestions would be great!
t1d
Don't know what accuracy you need, but OTC gps is only accurate to 3-10 meters, or so...

Don't know how to get Access to directly capture the lat/lon from the gps data stream, but others, here, are that smart... So, they will be along shortly...

Here are a couple of knee-jerk responses... I realize that they will require opening separate programs, and it appears that you wish to avoid that extra step, but maybe they will get you pointed in the right direction...

One idea that comes to mind is to find a free tracking program that will output the track to a file... Then, just start and stop the track from a fixed position under the tree that has the nest... Export the file... Delete the repetitious entries...

Another approach would be to use a full navigation capable program, create a waypoint at the tree and export the waypoint coordinates... Again, you can even find free programs for this... I think you can do this with the handheld units, too...

I do not run any topo programs, so search this forum for "topo." I think that may be better suited for your needs.
SpadesFlush
Welcome to the Forum, BirdGuy. It sounds like you have an interesting project. If I get it right, you are trying to reduce the burden on your field surveyors when it comes to transcribing location data from the GPS-reading software to the data base programs that they all will have on their tablets, which I assume are running some generation of Windows operating systems.

I would be inclined to making it easy for the field operatives to capture that information and then they, or someone else, can execute the clerical task of loading the data into Access back at base or at some other location any time after the fact. This is not perfect, but it is better than having to do that "in the field." To handle the capture, I would equip all the tablets with "Irfanview" which is a free application available for download here: http://www.irfanview.com/. In that, you can set the options in a variety of ways, but most relevant for you would be the ability to capture the active window with a hot-key stroke and save that capture as a unique dated file in a variety of formats such as PDF or JPG. Those files can be attached to an e-mail and sent to base for clerical processing if your resources allow for that sort of thing. There are even some software programs that can be set up to convert image (PDF/JPG) data to ansi characters for automated entry into Access. You may be able to create a macro to handle this task.

See a series of captures from my tree house attached.

But it seems to me the main thing is get accurate captures easily in the field that is not dependent on operator skills and that is what Irfanview can do for you.

Let us know if you need more.
Attached Images
capture_004_28042012_101151.jpg   capture_003_28042012_101145.jpg   capture_002_28042012_101137.jpg  
tcassidy
Gee Spades. I thought you would suggest using S&T 2011, creating waypoints at each stop and exporting the GPX file. I'm sure it could be used directly in Access without any troublesome, error prone retyping.

Terry
SpadesFlush
Sorry to disappoint T-Man. Don't think I didn't consider that!

It's just that S&T does not give you a nice clean one-click way of creating waypoints, which it really should. As far as I can see, you have to drag pushpins on to your location on the map and that would introduce a great deal of scope for error.
Marvin Hlavac
Hi BirdGuy, and welcome to the forum. A little bit of custom programming is likely what you need here.
t1d
Thanks, Gurus, good job!
t1d
How about modifying this geocaching program? It can geotag/output coordinates to photo's or other files. I don't know what the noted limitations might be for the MTK Chipset requirement. It seems to me that the output stream should be universal. Maybe GpsGate could adjust it to what you need.

GPS Datalogger Device Control
http://sourceforge.net/projects/bt747/
Description
i-Blue 747 / i-Blue 757 / Qstarz BT-Q1000 / i.Trek Z1 / Konet BGL-32 / Holux M-241 / ... control SW (for Java Phones, PalmOS, WinCe (PPC), Java platforms, Windows, Linux, and MacOS). Compatible with most MTK GPS Chipset based loggers.

GPS Datalogger Device Control Web Site
Features
Download recorded position data from GPS Data Loggers based on the MTK chipset.
Convert GPS position data in many formats (HTML, GPX, KML, KMZ, ...)
Geotag JPG pictures and position any other filetype on the map (voice, documents, ...)
Configure MTK Chipset Based Devices and Loggers (including AGPS data)
Multiplatform - runs on most computers and devices
Integrated OpenStreetMap
tcassidy
As the tablets may not have keyboards, the pushpins could be pre-added to the bottom of the map area in a particular format for each type of location. The user could use their stylus to drag the appropriate one to the GPS location.

Would that create a usable GPX file?

Terry
SpadesFlush
Quote:
Would that create a usable GPX file?
It would still depend on the accuracy of the person in the field placing the pushpin. Depending on the zoom level, that might be a bit iffy.

Also, I was trying to avoid causing OP to shell out some proably-incremental money to purchase S&T licenses.

And I think all tablets have OSKs whether or not they have keyboards so virtual hot-keys should be manageable.
tcassidy
I am not crazy about OSKs but they can do the job. I guess we need more info from the OP as to exactly what hardware is being discussed. I do agree with Marvin that a custom program would certainly be the right answer. As to the datalogger program, I suspect it is for that particular piece of equipment after a quick look.

Terry
Ken in Regina
For what it's worth, GPSGate Express (free) version has a data logging feature. So if data logging would solve the problem you could use GPSGate's data log file.

This would require that the date and time being entered as part of the on-the-spot data entry. If that's not already being done, it should be easy enough to modify the Access application to grab the system date/time as part of the action when creating the empty form to be filled in.

Once that is done, e.g. each data entry has a unique date/time entry, it should be easy enough for the IT folks to create a "tagging" application for running back at the office or at the end of the day in the field.

For the original poster, here's what that means: The "tagging" application would simply rip through the Access data file and for any records with empty location fields it would grab the log entry with the associated date/time stamp.

It's not trivial to do because the tagging application would need some judgement about which of the entries "nearest" (timewise) to the date/time of the data record are actually "nearest" (locationwise) to the item being recorded. Not trivial but really not terribly difficult, either. Just look for a set of location log entries that are clustered very close together - physically - within some minimum proximity of the timestamp in the data record being matched.

I agree with the implication the others are making that it would be nice for the field people to be able to just push a button and have the location coordinates grabbed from the GPS data stream and added to the data record.

But that would also take some custom programming that could be more complex than the "tagging" solution. It requires a direct tie to the creation of the data record as the field person is doing it. I don't know if Access has any capability to do that. So it would likely require a custom program to be written in some other language and use a call from the Access API during the data entry.

If it's possible to get some programming done, there are a number of interesting methods to solve the problem.

Otherwise, the simplest is to have GPSGate or some other program capture a data log and have the field people do the "tagging" manually at the end of the day. Just open the data records that require location data, visually browse the GPS data log or track file for the best fit based on the time stamps and a tight clustering of the location coordinates and cut/paste the location coordinates into the data record.

Rinse and repeat...

...ken...
MrUmbra
If Access can import a comma delimited file the field workers might be able to use the data logger I wrote for malaki86. Then you could import when you're back in the office.

http://www.laptopgpsworld.com/3996-gps-software-save-latitude-longitude-location-name-csv

--- CHAS
SpadesFlush
And then there are geotagging cameras.
Attached Images
geotagging-cameras.20120429.jpg  
laptopgpsworld.com About