I am trying to write a query in Access (or SQL) that works on a table
like this:
Location Gridreference
Ben Nevis NQ1234512345
Ben Doon NQ1230012300
and so on for several thousand records.
I have a function that calculates the distance between two points.
What I want to do is write a query that will list for each point in
the database, which other points are within 500 metres.
I've managed to get the number of points within 500 metres and I can
list out the first point in the database within 500 metres, but I have
not managed a full list.
Any ideas?
Max 1 1403
Anne,
Thanks for the advice - and the clear explanation! I will try it out
this weekend - hope the size of the intermediate table doesn't scare
my old PC.
Thanks again,
Max
"Anne Nolan" <an************ ***@AOL.COM> wrote in message news:<2i******* *****@uni-berlin.de>... One approach:
Create a query (call it qryAllPairs, e.g.) that lists all pairs of points, and the distance between them. This query will have your Locations table in the query twice, with NO join between the two (this is called a "Cartesian Product"):
SELECT Locations.Locat ion AS FirstPoint, Locations_1.Loc ation AS SecondPoint, DistanceCalc(Lo cations.GridRef erence, Locations_1.Gri dReference) AS Distance FROM Locations, Locations AS Locations_1;
Note that each pair of points will appear twice, and each point will also be paired with itself once. I've assumed a table name of "Locations" and a function name of DistanceCalc.. change to whatever you've named yours.
SELECT FirstPoint, SecondPoint, Distance FROM qryAllPairs WHERE (FirstPoint < SecondPoint AND Distance < 500);
Then you can use this query as the basis for a 2nd query, which just gets the point pairs where the Distance is less than 500.
The duplicate pairings are elimnated by including "WHERE FirstPoint < SecondPoint ". This will also eliminate the records that pair a location with itself.
If your distance calculation takes a long time, you may want to eliminate the duplicate point pairs in the first query, so that you cut the number of calls to the function in half.
Hope this helps,
Anne Nolan
"maxmarengo " <ma********@yah oo.co.uk> wrote in message news:e5******** *************** ***@posting.goo gle.com... I am trying to write a query in Access (or SQL) that works on a table like this:
Location Gridreference Ben Nevis NQ1234512345 Ben Doon NQ1230012300 and so on for several thousand records.
I have a function that calculates the distance between two points. What I want to do is write a query that will list for each point in the database, which other points are within 500 metres.
I've managed to get the number of points within 500 metres and I can list out the first point in the database within 500 metres, but I have not managed a full list.
Any ideas?
Max This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: vbnetrookie |
last post by:
I have a table name in SQL Server 2000 that has a space in it
ex: aim international
I had trouble just in the query analyzer with this..I had to place the
name in brackets for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An example of what I'm trying to do is below:
update (tbl_ind_mananas LEFT JOIN (select count(*) as count, (dubicacion ||
zona || manzana) as cod_manzana from tbl_censo_poblacion_1993 group by
dubicacion, zona, manzana) tbl1 on relacion = cod_manzana) as...
|
by: Kent |
last post by:
Hi!
I want to store data (of enemys in a game) as a linked list, each node will
look something like the following:
struct node
{
double x,y; // x and y position coordinates
struct enemy *enemydata; // Holds information about an enemy (in a game)
// Its a double linked list node
|
by: CSN |
last post by:
I have a pretty simple select query that joins a table
(p) with 125K rows with another table (pc) with almost
one million rows:
select p.*
from product_categories pc
inner join products p
on pc.product_id = p.id
where pc.category_id = $category_id
order by p.title
|
by: Crash |
last post by:
Hi,
..NET v1.x SP1
VS 2003
SQL Server 2000 SP3
Server 2000, XP, Server 2003
I would like to programmatically execute {possibly many} SQL Server
batch scripts. Aka I have many scripts that drop/add stored procedure
definitions, alter table definitions & constraints, etc... and I would
| |
by: windandwaves |
last post by:
Hi Folk
I have a query:
SELECT COUNT( `SIS`.`ID` ) c, D
FROM `SIS` , `SID`
WHERE `SID_ID` = `SID`.`ID`
AND `BRO` <> "bot"
GROUP BY SID.ID
|
by: Frankie |
last post by:
I'm trying to run the following search query:
$query =
sprintf ("SELECT itemNumber, thumbnailURL, title, description, price
FROM apparel,hats
WHERE apparel.title OR apparel.description OR hats.title OR
hats.description
LIKE '%s'",
$_POST);
|
by: gerbski |
last post by:
Hi all,
I am relatively new to ADO, but up to now I got things working the way
I wanted. But now I've run into somethng really annoying.
I am working in MS Access. I am using an Access frontend separately
from a backend. The tables from the backend database are linked in the
frontend database.
In the frontend there is a Form with a listbox in it. The listbox
rowsource is a query that selects all the records from a (linked)
|
by: Ubi |
last post by:
I am looking for a list of countries and nationalities, against the top
level domain.
es:
..ar ==Argentina ==Argentinian
..al ==Albania ==Albanian
etc. etc.
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |