Querying point X,Y data stored in a database

[GagagDa_Morvi]

Due to some reasons, I am required to store X,Y point data in a database (and not convert them to point features). The X,Y data represents points in a UTM projection. I need to search for a point that is nearest to a given point in a query. The given point is also in the same UTM projection. Is there an efficient way of accomplishing this using SQL query? Thanks!

[AnthonyGiles]

Gagag,

Not tested but you will need to do some pythagoras to work out the distance, ie:

SELECT sqrt(pow(inputxvalue - fieldx,2) +(pow(inputyvalue - fieldy,2)) AS Distance FROM tablename ORDER BY Distance LIMIT 1

Regards

Anthony

[GagagDa_Morvi]

SELECT sqrt(pow(inputxvalue - fieldx,2) +(pow(inputyvalue - fieldy,2)) AS Distance FROM tablename ORDER BY Distance LIMIT

Thansk, anthony. This query involves full table scan (I think). Is there a way to avoid that?

[AnthonyGiles]

Gagag,

Not that I know of, but I am not by any means a SQL expert, maybe there is someone else that may know better

Regards

Anthony

[VinceAngelo]

In software engineering terms, the "big O notation" of a query on the closest distance from any
single point is inherently "O(N)" [the effort required to solve the problem is proprtional to the
number of rows in the table -- aka, full table scan]. 

The only to way to reduce processing is to put a limit on the magnitude of the distance by
indexing both X and Y ("CREATE INDEX foo ON table(xcol,ycol)") and adding a WHERE
clause to the query:

WHERE xcol between (inputx - theshold) and (inputx + theshold)
  AND ycol between (inputy - theshold) and (inputy + theshold) 

This is, in essence, what you're losing by not being able to generate a buffer about the search
point and use the spatial index to limit return results [an unbounded query on a spatial relationship
of distance(inputpt,ptcol) would also be O(N)].

- V

[GagagDa_Morvi]

In software engineering terms, the "big O notation" of a query on the closest distance from any
single point is inherently "O(N)" [the effort required to solve the problem is proprtional to the
number of rows in the table -- aka, full table scan]. 

The only to way to reduce processing is to put a limit on the magnitude of the distance by
indexing both X and Y ("CREATE INDEX foo ON table(xcol,ycol)") and adding a WHERE
clause to the query:

WHERE xcol between (inputx - theshold) and (inputx + theshold)
  AND ycol between (inputy - theshold) and (inputy + theshold) 

This is, in essence, what you're losing by not being able to generate a buffer about the search
point and use the spatial index to limit return results [an unbounded query on a spatial relationship
of distance(inputpt,ptcol) would also be O(N)].
- V

Thanks! I do have the index as you have suggested - I will try out the where clause and see how it goes.

[VinceAngelo]

The index won't do any good without a where clause constraint to exploit it
(in fact, it would just slow down INSERT performance).

- V

[BruceHarold]

Hi

If you have the freedom to alter the schema of your table you could implement a geohash-based query:

http://code.google.com/p/python-geohash/

You would need to calculate a hash onto each point, with a precision to suit your data.

Then your nearby query would devolve into a string range query.

Regards

[BruceHarold]

See the attached functions; requires ArcGIS 10.1

Regards

[GagagDa_Morvi]

Hi

If you have the freedom to alter the schema of your table you could implement a geohash-based query:

http://code.google.com/p/python-geohash/

You would need to calculate a hash onto each point, with a precision to suit your data.

Then your nearby query would devolve into a string range query.

Regards

Very interesting approach. Will try. Thanks!