Hi everyone,
I have never posted on a site like this before but am unfortunately a bit stuck on this problem... Hopefully someone much smarter than myself can help me out!
I have a table in MS Access 2013 full of latitude and longitude values. I am wanting to use a distance formula (something like one shown below) to loop through all the records in this same table and count entries that are within a user defined distance of one-another. Ideally I could run an update query at the same time to dump the values back into the table for use in visualization software. The table has approximately 200,000 entries. Can't imagine that is enough to cause a problem.
Saying that I am even an amateur in VBA would be a stretch but I can usually figure most basic problems out. This one is a bit outside of my ability level though so any help would be greatly appreciated! Thanks in advance for any help! - Option Compare Database
-
Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double)
-
-
Dim EarthRadius As Double
-
Dim KmtoMiFactor As Double
-
Dim lat1Rad As Double
-
Dim lon1Rad As Double
-
Dim lat2Rad As Double
-
Dim lon2Rad As Double
-
Dim AsinBase As Double
-
Dim DerivedAsin As Double
-
-
EarthRadius = 6371
-
-
KmtoMiFactor = 0.621371
-
-
lat1Rad = (lat1 / 180) * 3.14159265359
-
lon1Rad = (lon1 / 180) * 3.14159265359
-
lat2Rad = (lat2 / 180) * 3.14159265359
-
lon2Rad = (lon2 / 180) * 3.14159265359
-
-
AsinBase = Sin(Sqr(Sin((lat1Rad - lat2Rad) / 2) ^ 2 + Cos(lat1Rad) * Cos(lat2Rad) * Sin((lon1Rad - lon2Rad) / 2) ^ 2))
-
-
DerivedAsin = (AsinBase / Sqr(-AsinBase * AsinBase + 1))
-
-
DistanceFeet = Round(2 * DerivedAsin * (EarthRadius * KmtoMiFactor) * 5280, 0)
-
-
End Function
14 1489
The principal is very simple, but there is a potential problem of speed.
So a few basics.
You need a table of places TblPlaces with say
PlaceID AutoNumber PK
Place Text
Lat Double
Lon Double
You need a second table TblJoinPlacePlace with
PlaceID1 Long Joint PK
PlaceID2 Long Joint PK
Distance Double
Set up a relationship with 2 copies of TblPlace and 1 copy of TblJoinPlacePlace and join PlaceID from the first TblPlace to PlaceID1 and the PlaceID from the second TblPlace to PlaceID2.
This arrangement links every place to every place.
Problem no one is that for n locations, there are n*(n-1)/2 joins required, so with 200,000 locations, there will be approximately 20,000,000,000 records. That's a lot of calculation, and I have no concept of the time to run this calculation.
Again. I'll give you the VBA basics, but if you need further help, please come back.
In code, you need to create 3 queries.
The first one which I shall call OuterQuery reads every record in the TblPaces in PlaceID Order.
The second query, InnerQuery reads every record in the TblPlaces in PlaceID Order WHERE PlaceID > PlaceID in the OuterQuery.
The third query is to create the TblJoinPlacePlace, so you add the PlaceID from the OuterQuery to PlaceID1, the PlaceID from the InnerQuery to PlaceID2, Do your calculation by calling your Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) based on the Lat & Long from OuterQuery (Your Lat1 & Lon1) and the Lat & Long from the InnerQuery (Your Lat2 & Lon2) to get the distance.
I have no idea if your Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) will work, but it needs changing to -
Function DistanceFeet(lat1 As Double, lon1 As Double, lat2 As Double, lon2 As Double) As Double
-
My suggestion is to try it for just 1 record in the OuterQuery to get an idea of timing. The good thing is that on each cycle of the OuterQuery, there is 1 less record to read from the InnerQuery, so it should get faster ... on the other hand, Access has to keep track of more records....
I'm interested how fast it runs
Phil
Do you have the tables with Latitude and Longitude already defined?
If so, you should be able to create a single Query that calls your VBA function and return your results. If you were to provide your table structure, we could provide you a query.
Thank you so much for the quick response! Your idea of having one query that calls the VBA function and returns the results is sort of what I was hoping to accomplish. My knowledge of VBA is just too limited to figure out the exact syntax to make it work.
I do already have a table defined. The name of the table is [AC_PROPERTY] and has approximately 50-60 columns of data in it with about 200,000 entries. For purposes of this function, the only relevant columns I believe would be the unique identifier [PROPNUM], [LATITUDE] and [LONGITUDE].
Thanks again for the continued help with this problem!
That sounds fine. I assume your PropNum is an Autonumber which is the same as the PlaceID to which I referred. It doesn't have to an Autonumber, but it does have to be unique.
What marginally concerns me, but it is not the slightest bit relevant to this subject is your mention of 50 to 60 columns in your AC_PROPERTY Table. I strongly suspect that your data is not normalised.
Have a go at writing the code to create the Join table, and come back when you get stuck.
Phil
I think this query will work for you. I'm assuming you have your function saved off in a code module. If it's not in a code module, it wont be visible by the Query Engine.
Hopefully it will work out of the box. It may run horribly, but then again it may be usable the way it is. If it runs too slow, then there are options, like if the data is loaded from another application, then the Query results can be inserted into a table, after every refresh of the base data and it will be accurate and fast. I guess we can worry about that later.
The Query: - SELECT
-
AC_PROPERTY.PROPNUM
-
, AC_PROPERTY.LATITUDE
-
, AC_PROPERTY.LONGITUDE
-
, AC_PROPERTY_1.PROPNUM
-
, AC_PROPERTY_1.LATITUDE
-
, AC_PROPERTY_1.LONGITUDE
-
, DistanceFeet([AC_PROPERTY].[LATITUDE],[AC_PROPERTY].[LONGITUDE],[AC_PROPERTY_1].[LATITUDE],[AC_PROPERTY_1].[LONGITUDE]) AS DistanceInFeet
-
FROM
-
AC_PROPERTY
-
, AC_PROPERTY AS AC_PROPERTY_1
You both are awesome. Thank you so much for the quick help. Let me play around with this for a few and see if I can't get it to work based on both of your input. Thanks again and I will post back on here when I can tell if it is working or not.
I have been working on this a bit this morning and think that for the most part this appears to work. Ended up having to run three separate queries to get the answer I was looking for but as a whole, it doesn't take as long to run as I feared it might.
However, on the second and third query I keep getting a "Data type mismatch in criteria expression" error. Any idea what might be causing this? Queries are as follows:
Query 1: - SELECT AC_PROPERTY.PROPNUM, AC_PROPERTY.BH_LATITUDE, AC_PROPERTY.BH_LONGITUDE, AC_PROPERTY_1.PROPNUM, AC_PROPERTY_1.BH_LATITUDE, AC_PROPERTY_1.BH_LONGITUDE, DistanceFeet([AC_PROPERTY].[BH_LATITUDE],[AC_PROPERTY].[BH_LONGITUDE],[AC_PROPERTY_1].[BH_LATITUDE],[AC_PROPERTY_1].[BH_LONGITUDE]) AS DistanceInFeet
-
FROM AC_PROPERTY, AC_PROPERTY AS AC_PROPERTY_1;
Query 2: - SELECT AC_PROPERTY.PROPNUM, AC_PROPERTY.DRILL_TYPE, AC_PROPERTY.RESERVOIR, [Q - Distance Between Wells - 1].DistanceInFeet
-
FROM [Q - Distance Between Wells - 1] INNER JOIN AC_PROPERTY ON [Q - Distance Between Wells - 1].AC_PROPERTY.PROPNUM = AC_PROPERTY.PROPNUM
-
WHERE (((AC_PROPERTY.DRILL_TYPE)="H") AND ((AC_PROPERTY.RESERVOIR) Like "*eagle*") AND (([Q - Distance Between Wells - 1].DistanceInFeet) Between 0.01 And 5280));
Query 3: - SELECT AC_PROPERTY.PROPNUM, AC_PROPERTY.DRILL_TYPE, AC_PROPERTY.LEASE, AC_PROPERTY.WELL_ID, Count([Q - Distance Between Wells - 2 (Sel zone and distance)].PROPNUM) AS CountOfPROPNUM
-
FROM AC_PROPERTY INNER JOIN [Q - Distance Between Wells - 2 (Sel zone and distance)] ON AC_PROPERTY.PROPNUM = [Q - Distance Between Wells - 2 (Sel zone and distance)].PROPNUM
-
GROUP BY AC_PROPERTY.PROPNUM, AC_PROPERTY.DRILL_TYPE, AC_PROPERTY.LEASE, AC_PROPERTY.WELL_ID;
I was bored, and realising I had a Db with 249 records including Lat & Long, It took only an hour to create a database.
The advantage of creating the join table (30878 records took 3 seconds) is that it the basis for numerous queries like show me all the places within 5280 feet of Wells.
If you're are interested, I could send it to you, but would have to "butcher" some of the information.
Phil
If you wouldn't mind that would be great! Like I said, I mostly got this to work but keep getting that error for some reason. Would definitely be helpful to see how you made it work if it is not too much trouble.
Thanks!
OK, here is the database with a "butchered" address table.
It's a bit Mickey Mouse, but what do you expect in an hour?
Notice the normalisation of the tables. As I said I was concerned when you mentioned 50+ columns.
There currently is no data in the TblJoinAddressAddress table, you need to open Form1 to populate it, or clear it.
Query 1 is a sample query.
Try it on your database, and if it takes too long to populate, I have ideas that may speed it up, but it could give erroneous results.
Incidentally, not knowing where your locations are situated, I have some really, really slow VBA that gets the distances between places by road, rather than great circle distances.
Note slight change to the first & last lines of your DistanceFeet Function
Phil
NeoPa 32,556
Expert Mod 16PB
This may sound like an obvious question, but why is no-one discussing DCount() ? I'm assuming the title accurately reflects the basic requirement here.
There are always many ways to skin cats (A fairly unpleasant metaphor - I'm sorry for that), but a DCount() using a query returning the distances and criteria that specifies which are to be included seems the most direct route.
I take your point, but the Query1 in the example I posted is a dummy query. It could equally well have been -
SELECT Count(Address.Address1) AS CountOfAddress1, Towns.Town
-
FROM (Towns INNER JOIN Address ON Towns.TownID = Address.TownID) INNER JOIN ((TblJoinAddressAddress INNER JOIN Address AS Address_1 ON TblJoinAddressAddress.AddressID2 = Address_1.AddressID) INNER JOIN Towns AS Towns_1 ON Address_1.TownID = Towns_1.TownID) ON Address.AddressID = TblJoinAddressAddress.AddressID1
-
WHERE (((Towns.Town) Like "frinton*") AND ((TblJoinAddressAddress.Distance)<6000))
-
GROUP BY Towns.Town;
-
to give a count.
My point is that having created the join table, with all the distances in it, it is very simple to create queries to provide whatever information is required.
Phil
NeoPa 32,556
Expert Mod 16PB
Hi Phil.
Let me stress that wasn't intended to sound critical. I'm sure both you and JForbes would use it where appropriate and understand that the fundamental querying underneath is the critical part. SMcClaren, a self-confessed newbie, may not have appreciated all the subtleties though, so I thought I'd just make sure this was one element they could be clear on.
It's easy to forget that we can rabbit on a hundred to the dozen making good sense all the way while someone else can get lost at the first part and wonder what's going on. Anyway, good work all rounf :-)
@SMcClaren.
I will probably have updated your posts to include the [CODE] tags that are mandatory when posting code. Please try to add them yourself when posting going forward.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: webhigh |
last post by:
I¹m not sure if this a PHP question or an MySQL question but here it goes.
I have a repeat region of a table called userid
What I¹m trying to accomplish is being able to edit the record and...
|
by: Radu |
last post by:
Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a...
|
by: Byomokesh |
last post by:
Hi
I am trying to cell count and match in tgroup cols value in XML file. if cell count and tgrou cols value is mismatch, its showing error.
My xml
--------------
<tgroup cols="3">
<colspec...
|
by: KCangelstar |
last post by:
In my database, I am trying to count records from a field that meet the criteria of "SELECT DISTINCTROW WARes016.Stratum, Count((.)) AS CountOfDays
FROM WARes016 INNER JOIN ON WARes016.LRS = .LRS...
|
by: SaltyBoat |
last post by:
Needing to import and parse data from a large PDF file into an Access
2002 table: I start by converted the PDF file to a html file. Then
I read this html text file, line by line, into a table...
|
by: Soccer5 |
last post by:
Trying to Count records on a report that meet a certain criteria.
Have a text box in the Report Footer that has the following in the Control Source: =Count(="S")
This does not work. It...
|
by: markrawlingson |
last post by:
Hopefully someone can help me out with this, it's driving me nuts...
I have two tables - We'll call them table1 and table2. Table1 holds event information, and table2 holds user registration data...
|
by: barmatt80 |
last post by:
I finally got my call to a stored procedure on our db2 to work. However i might have to change what the stored procedure does, if I cannot get it to work how we want. Which i would like to make it...
|
by: Fary4u |
last post by:
code works fine it's counts the records but not bring updated record?
is there any other way to count records ?
Set rs = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT...
|
by: joppeter88 |
last post by:
Please help me solve my problem.
I'm making a program that counts the number of records in my database.
SqlQuery = "SELECT COUNT(*) FROM emp_tbl WHERE lname LIKE '%" & txtSearch.Text & "%' or...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |