22
votes
Accepted
Quick nearest neighbor search in the 150-dimensional space
PostgreSQL 9.6 using cube
First install the cube extension
CREATE EXTENSION cube;
Now we will create some n-dimensional space with 100,000 points in 50 dimensions. In addition we'll add a GIST ...
- 64.7k
5
votes
How to setup solution for multi-dimensional distance search with SQL Server spatial data types
There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value:
Z-coordinates are not used in any calculations made by the library and are ...
- 10k
2
votes
Quick nearest neighbor search in the 150-dimensional space
Consider performing dimension reduction first (eg. Principle Component Analysis).
Then your are doing NN in small number of dimensions with higher performance.
You can use Pl/R to perform PCA inside ...
2
votes
Accepted
Return values at more/less specific time points
I suggest a LATERAL subquery, best with a LEFT [OUTER] JOIN to preserve all input rows (displaying a NULL value in case there should be no match at all):
SELECT v.reference_ts, t.most_recent_x
FROM (...
- 182k
2
votes
Accepted
Add row from table B based on minimum distance between table A and B
That's a nearest-neighbor classic. The basic solution:
SELECT *
FROM tableA a
CROSS JOIN LATERAL (
SELECT b.name, b.geom <-> a.geom AS dist
FROM tableB b
ORDER BY dist
LIMIT 1
...
- 182k
2
votes
Improve performance of this query finding nearby locations
Many parentheses are just distracting noise. Several casts seem unnecessary.
Most importantly, the CTE seems to do nothing useful, remove it and just keep the WHERE condition:
SELECT p.sid,
-- ...
- 182k
2
votes
Accepted
Optimize a trigram search with custom sort order
Trigram similarity and distance operators put more weight on leading matches (prefix) automatically and to a lesser extent on trailing matches (suffix), due to the way trigrams are extracted from ...
- 182k
1
vote
Nearest neighbours (KNN) from a selected point
With a SELF JOIN, in the form of an inner join if you know the distance. Or a cross if you don't.
SELECT spatial_id, geom
FROM rhp_tvp.spatial AS rt1
JOIN rhp_tvp.spatial AS rt2
USING ST_DWithin( ...
- 64.7k
1
vote
Searching for a close numeric match on spatial coordinates
The smart and fast solution for this class of problems is an index-backed "nearest neighbor" search.
For the record: if you want precise results with spatial data use PostGis and operate ...
- 182k
1
vote
Accepted
How to SELECT "closest" rows from another table?
This would achieve it:
SELECT m1.id1, m2.*
FROM map1 m1
CROSS JOIN LATERAL (
SELECT ST_HausdorffDistance(m1.g1, m2.g2) AS h_dist, m2.id2, m2.g2
FROM map2 m2
WHERE ST_HausdorffDistance(...
- 182k
1
vote
Accepted
ORA-13249: SDO_NN cannot be evaluated without using index
Got same error message, but without DB-Link. http://www.orafaq.com/forum/t/154844/ helped (replace c and s with your table aliases and spatial_index with your spatial index name):
/*+ LEADING(c) ...
- 126
1
vote
Quick nearest neighbor search in the 150-dimensional space
Take a look at https://github.com/a-mma/AquilaDB it is a vector database to store Feature Vectors along with JSON Metadata. Keep it along with your RDBMS and do use metadata to maintain cross ...
- 11
1
vote
Quick nearest neighbor search in the 150-dimensional space
Have a look at FLANN and OpenCV.
Unfortunately I am not aware of an integration of that into a RDBMS system. But there is for example integration of chemical structure information with Posgres. So ...
- 430
Only top scored, non community-wiki answers of a minimum length are eligible