Microsoft Access - Recognizing Coordinates to Prevent Site Duplication RRS feed

  • Question

  • I'm attempting to build a very simple relational database using Microsoft Access for a multi-year set of scientific data. One of the challenges I'm trying to overcome is that new data will be actively entered into this database using forms and a front-end interface and I don't want site duplication. In other words, if we sample a site that we sampled 3 years ago and enter that new data into the database - I want the database to be able to recognize the latitude and longitude of the site coordinates and notify me that the site appears to already exist in the database and we're just adding another series of datapoints.

    Lat/long in decimal degrees is a fairly continuous set of values and I imagine that I'd have to indicate some kind of plus or minus (+/-) range that would represent a plus or minus 50-meter radius around a location. If that makes sense it would be great if someone has some insight into how to solve this.


    Saturday, April 6, 2019 2:44 PM

All replies

  • Each site should be represented by a row in a Sites table, whose columns should each represent attributes which are functionally determined solely by the table's primary key.  These will include locational attributes such as the longitude and latitude of the site.  The latitude and longitude should be stored either as floating point numbers in degrees, or as integers in degrees and a floating point number in minutes.

    Identifying a site by its latitude and longitude co-ordinates is not a good way of going about it.  Each site should be identified by a distinct primary key value, which can then be referenced by a foreign key in your sampling table(s).

    As regards defining an area around a latitude/longitude point, to do so internationally would require some spherical trigonometry, but if the data is confined to a limited area an approximate square around a point can be defined quite easily by first ascertaining the size of a minute of latitude or longitude in the area in question.  In my part of the UK for instance a minute of longitude is roughly 10800 metres, and a minute of latitude is approximately 18600 metres.

    Ken Sheridan, Stafford, England

    Saturday, April 6, 2019 5:51 PM
  • Ken is right about a PK that is not Lat/Long.

    When user enters a new lat/long, you can calculate the distance to all existing records, and if one is < 50m, then you alert the user.

    Distance calculation: https://stackoverflow.com/questions/27928/calculate-distance-between-two-latitude-longitude-points-haversine-formula

    -Tom. Microsoft Access MVP

    Saturday, April 6, 2019 6:42 PM