Grouping geographic points along a line based on their distance from one another

[Bdowns]

I conduct ecological surveys at sea.



I have a table of Navigation data with geographic coordinates logged on a one second interval during survey lines

The table has a field for Timecode (hh:mm:ss); transect ID, and Latitude and longitude. I also have the lat/lon in UTM format (northing and easting in meters) from which I can calculate the distance between points using the distance formula.

for statistical sampling purposes, the transect lines have to be divided into 100 meter segments. I would like to add a field named [segment] and populate the field with segment_ids so that I can group the records into 100 meter segments. Because the boat changes speeds and stops often, I cannot simply use time to break the transects into segments based on distance.

Any ideas about how I can populate the segment field to divide each transect into 100 m segments. I would like to populate the field with the name of the transect and sequential #'s (e.g. TranA_1, TranA_2, TranB_1, TranB_2...)

[jzwp11]

If a transect can be divided into many segments, it describes a one-to-many relationship, so by normalization rules the segments should be records in a related table. The table structure would look something like this

tblMain
-pkTransectID primary key, autonumber
-transectname

tblSegments
-pkSegmentID primary key, autonumber
-fkTransectID foreign key to tblMain
-longSeqNo a long integer field to capture the sequence number of the segment (i.e. the number in your TranA_1, TranA_2)


I assume that you may have other fields related to the segment, and I also assume that you would be collecting data at the end or start of each segment.

Now to create the segment records, you will need to use some Visual Basic for Application (VBA) code. You would bring in the distance and use a loop to create the records (via an append query), subtracting 100m from the distance each time through the loop. Once the remaining distance is less than 100m, you know you have created enough segment records. Of course each segment record would have to include the value for fkTransectID so that you know that those segments are related to that particular transect. Typically you would put the code behind a button on a form that is displaying a transect record from your main table.