nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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!

タグ付けされた投稿をもっと見る

Accepted answers

All comments

bharold

Hi

I updated the functions.

Regards

geohashfunctions.py.zip

PCHarcgisSOE.zip

bharold

Hi

I updated the functions to be a little more robust.

regards

geohashfunctions.py.zip

1942.png

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!

bharold

See the attached functions; requires ArcGIS 10.1

Regards

geohashfunctions.py.zip

Survey Form Screenshot.png

bharold

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

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

AdamGebhart_0-1769521420528.png

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

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

AntGiles

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

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?

AntGiles

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

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.