Results 1 to 13 of 13
  1. #1
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25

    Filtering data and extract names based on geographic coordinates

    Hi,

    Basically, I have a table 1 that holds geographic coordinates (decimal degree) of multiple places. In a second table 2 I have geographic squares defined with four columns for their eastern, western, southern and northern boundary (i.e. geographic coordinates in decimal degree). Each of these squares has a name attached to it.
    Now I would like to extract for each place in table 1 the name of the square in which the place falls. And if the place does not fall within any of the squares, it should return an empty field. Is there any way of doing this?
    I tried it with:


    Code:
    =DLookUp("[SquareName]"; "[Table2_Squares]"; [Table1_Places].[Latitude] < [Table2_Squares].[north] & [Table1_Places].[Longitude] < [Table2_Squares].[east] & [Table1_Places].[Latitude] > [Table2_Squares].[south] & [Table1_Places].[Longitude] > [Table2_Squares].[west])
    However, this did not give any result (and no error). Any ideas?

    Thanks for helping,
    The U

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you post a copy of the database? Or show some sample data from your tables?

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Not possible as you’re referring to a second table. Could we see your tables and get a rough idea of their size

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I wrote a UDF for an example... maybe this will help.
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    On Fly.

    In case you need a squre name to be displayed in form's unbound control, where fields [Latitude] And [Longitude] are bount to text boxes txtLatitude and txtLongitude:
    =DLookup("SquareName", "Table2_Squares", "[North] <= " & Me!txtLatitude & " AND [South] > " & Me!txtLatitude & " AND "[East] <= " & Me!txtLongitude & " AND [West] > " & Me!txtLongitude)

  6. #6
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Sorry for the late reply. Since I am unable to shrink my DB in such a way that I could upload it (my fault, it is not that large), I attach the table 2 (TK25; I hope it is uploaded now). The full table TK25 has 3000 rows. An example row from table 1 (Places) looks like this (the full table Places has 300 rows):


    ID PlaceName Latitude Longitude TK25
    1 Dagebüll 54.73333 8.7





    In the best of all worlds, within a form I would like to have a button called "UpdatePlaces" that starts an update-query which adds to all rows in table Places the correct TKnumber from TK25 based on Latitude and Longitude. In this case, after the update the row in table 1 should look like:

    ID PlaceName Latitude Longitude TK25
    1 Dagebüll 54.73333 8.7 1218




    Best wishes
    The U

    TK25.txt

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    On fly
    Code:
    UPDATE Places
         SET TK25 = (SELECT t.TKnumber FROM TK25 t WHERE t.north <= p.Latitude AND  t.south > p.Latitude AND t.East <= p.Longitude AND t.west > p.Longitude)
    FROM Places p

  8. #8
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Thanks,
    unfortunately this gives a syntax error.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by The U View Post
    unfortunately this gives a syntax error.
    It looks like Access doesn't allow to update with calculated values from non-linked tables.

    I created a saved query (there were comparisions wrong in my previous example - I corrected them)
    Code:
    qPK25 = 
    SELECT p.ID, (SELECT t.TKnumber FROM tblTK25 t WHERE t.north > p.Latitude AND  t.south <= p.Latitude AND t.East > p.Longitude AND t.west <= p.Longitude) AS TK25
    FROM tblPlaces AS p;
    , but when I tried to use it to update tblPlaces

    Code:
    UPDATE 
    tblPlaces p INNER JOIN qPK25 q ON q.ID = p.ID
    SET p.PK25 = q.PK25
    , I got an error again. Access thinks, that qPK25 may have several rows for same ID, and so the query is not updateble.

    A probable solution will be a dummy table where data from qPK25 is inserted into, and which has ID defined as primary key. Then you can update the tblPlaces from this dummy table. Or maybe somebody has a better solution?

  11. #11
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Hi, thanks! I have to admit that I am a bit lost.
    1. I tried post #4 and downloaded the DB. Since my decimals are separated with a , instead of a ., I added str() to all numeric variables in the function GetSquares. Is that correct? If so, I do not understand why I do not get the Square Name back in the last column.
    2. To post #10: I assume this means that I create a query with the name "qPK25", go to SQL mode and enter the code "SELECT p.ID, (SELECT t.TKnumber ...)".? I assume that the resulting "p" variable contains all TKnames, am I right? Why is it not possible to get all TKnames as the result of that first query and use them for the update? How do I get the "p"s into a dummy table?

    Any help is highly appreciated.

  12. #12
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by The U View Post
    I assume this means that I create a query with the name "qPK25", go to SQL mode and enter the code "SELECT p.ID, (SELECT t.TKnumber ...)".? I assume that the resulting "p" variable contains all TKnames, am I right?
    Yes!

    Quote Originally Posted by The U View Post
    Why is it not possible to get all TKnames as the result of that first query and use them for the update?
    A question to Bill?
    Quote Originally Posted by The U View Post
    How do I get the "p"s into a dummy table?
    Create a table p.e. dummy_PK25: ID (Long Integer, Primary Key), TK25;
    Run a script, which
    a) deletes all data from table dummy_PK25;
    b) executes an append query like 'INSERT INTO dummy_PK25 SELECT * FROM qPK25'

  13. #13
    The U is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2017
    Posts
    25
    Great, that worked smoothly. Many thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 02-11-2014, 06:41 AM
  2. Replies: 1
    Last Post: 01-16-2013, 06:48 PM
  3. Replies: 1
    Last Post: 01-16-2013, 04:41 PM
  4. Filtering on multiple tables/queries then extract to csv
    By GraemeG in forum Import/Export Data
    Replies: 7
    Last Post: 10-18-2012, 03:34 PM
  5. How to extract names from 1 large field
    By Tepin in forum Access
    Replies: 1
    Last Post: 12-04-2006, 01:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums