"VisionSet" <spam (AT) ntlworld (DOT) com> wrote


Not sure if this would work, but assuming your coordinate fields are
indexed, you could restrict first on (ABS(P1.X - P2.X) < 666) AND
(ABS(P1.Y - P2.Y) < 666)) which is implicit as long as your coordinates are
real numbers (no imaginary part).
These should compute faster and, as long as MySQL does not perform a
complete boolean evaluation, speed up the query.
The restriction on counties will not work if you have two locations closer
to each other than your limit, each of them in a different county (I guess
this can happen).
Keep us posted on the results
--
Eric Lafontaine

"VisionSet" <spam (AT) ntlworld (DOT) com> wrote

Use postgres. It comes with a set of spatial types and operators, and a
spatial index to speed up the query.

http://www.postgresql.org/docs/view....tatype1681.htm

CREATE TABLE Person (
PKey INTEGER PRIMARY KEY,
Name VARCHAR(32) NOT NULL,
City VARCHAR(32) NOT NULL,
County VARCHAR(32) NOT NULL,
Where Point NOT NULL
);

SELECT P1.Person, COUNT(*)
FROM Person P1, Person P2
WHERE Contains ( Circle ( P1.Where, 666), P2.Where )
GROUP BY P1.Person;

This performs checks that the N-S and E-W distances are within the given
distance, cutting down on the number of rows that need the sqrt check.


SELECT * FROM Person AS P1
LEFT JOIN Person AS P2 ON
abs(P1.coordX - P2.coordX) < 666
and abs(P1.coordY - P2.coordY) < 666
and sqrt(pow(P1.coordX - P2.coordX, 2) + pow(P1.coordY - P2.coordY, 2)) <
666
GROUP BY P1.pKey

"ventre-à-pattes" <news (AT) ventre-a-pattes (DOT) com> wrote

mySQL does not use indexes on

ABS(P1.X - P2.X) < 40

or on

P2.y BETWEEN P1.y - 40 AND P1.y + 40

It would use an index on

P2.y BETWEEN 7000 AND 8000

So I'm still left with a 2000x2000 join, and a 2 minute query time.

There was another suggestion about creating a neighbours table, and I could
do this, despite thinking before I couldn't
Even though the 666 value is actually arrived at at runtime, I can give it
an upper limit, if I use this upper limit to limit the neighbours in the new
table, then this should cut down the number of rows to examine in the
pythag. function.
However this new table will be huge, I estimate possibly 50,000 rows say if
each 'city' has 25 neighbours.
(They aren't really cities of course, thats just an analogy.)

mySQL 4.1 has geometrical abilities that should fit the bill, anyone know
when this will production released?

Cheers,
--
Mike W