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!

Vind meer berichten getagd met

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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