topic Re: Querying point X,Y data stored in a database in Data Management Questions

Querying point X,Y data stored in a database

GagagDa_Morvi — Tue, 18 Sep 2012 18:36:03 GMT

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!

Re: Querying point X,Y data stored in a database

AnthonyGiles — Tue, 18 Sep 2012 18:50:23 GMT

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

Re: Querying point X,Y data stored in a database

GagagDa_Morvi — Tue, 18 Sep 2012 21:01:38 GMT

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?

Re: Querying point X,Y data stored in a database

AnthonyGiles — Tue, 18 Sep 2012 21:14:03 GMT

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

Re: Querying point X,Y data stored in a database

VinceAngelo — Sat, 11 Dec 2021 09:55:46 GMT

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

Re: Querying point X,Y data stored in a database

GagagDa_Morvi — Sat, 11 Dec 2021 09:55:49 GMT

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.

Re: Querying point X,Y data stored in a database

VinceAngelo — Wed, 19 Sep 2012 16:56:20 GMT

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

Re: Querying point X,Y data stored in a database

BruceHarold — Thu, 20 Sep 2012 14:02:59 GMT

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

Re: Querying point X,Y data stored in a database

BruceHarold — Mon, 24 Sep 2012 15:47:40 GMT

See the attached functions; requires ArcGIS 10.1

Regards

Re: Querying point X,Y data stored in a database

GagagDa_Morvi — Tue, 25 Sep 2012 13:59:15 GMT

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!

Re: Querying point X,Y data stored in a database

BruceHarold — Tue, 25 Sep 2012 17:21:42 GMT

Hi

I updated the functions to be a little more robust.

regards

Re: Querying point X,Y data stored in a database

BruceHarold — Tue, 25 Sep 2012 17:32:33 GMT

Hi

I updated the functions.

Regards