Skip to main content

Questions tagged [knn]

use this tag when dealing with questions related to kNN(K-Nearest Neighbors) a spatial property.

Filter by
Sorted by
Tagged with
4 votes
2 answers
90 views

Calculating accurate distances using various ST_Distance options

I want to get the nearest neighbour information for a point, and I'm getting different results based on different geometry/geography types. Assuming I only care about the most accurate result, which ...
Ruslan's user avatar
  • 695
3 votes
1 answer
87 views

PostGIS query to get the closest data available

I've got three tables in a PostgreSQL/PostGIS database: referencia.comarcas_agrarias_centroides (holds geom centroids of agricultural regions) referencia.estaciones_r_pen (holds geom locations of ...
jpinilla's user avatar
  • 3,230
4 votes
1 answer
98 views

Dynamically drawing the polygon formed by nearest points to @map_extent_center in QGIS

From a DEM raster layer I created a set of random points and informed each point with the value of 'ELEVATION'. I also used the following expression to connect the 10 points closest to the @...
Ingrid Ingravida's user avatar
2 votes
1 answer
85 views

KNN (<->) order by -- possible to use spatial index with a secondary order by?

From reading here as well as the docs, I know how to use the <-> operator in my order by in order to do KNN search, and this is fast and uses the gist spatial index as expected. However, I have ...
Loose_Screw_748's user avatar
3 votes
0 answers
91 views

Slow KNN within fixed radius search (PostGIS) - ST_DWITHIN vs KNN

Ask: I want to return all customers matching certain characteristics within a 50 mile radius of the incoming zip code. This query takes just over a second to complete, and that's because of this line: ...
Loose_Screw_748's user avatar
8 votes
1 answer
265 views

Connecting dynamically the 8 nearest lines to @map_extent_center in QGIS

Starting from a points geometry layer, I am working on the following function that has the goal of drawing 8 nearest lines to the @map_extent_center of the map view: make_line( $geometry, @...
Ingrid Ingravida's user avatar
11 votes
2 answers
296 views

Creating buffers to closest distances with two categories in QGIS

I would like to develop an expression that does the following nearest-neighbour analysis: From a layer of points 'point_layer', I would like to calculate for each point the distance to the nearest ...
Ingrid Ingravida's user avatar
2 votes
1 answer
107 views

Trying to get accurate results using ST_DWithin with srid -1

I have a table where each row has a point Geometry(POINT(...)) [long lat]. These geometries don't specify an srid. Which means we're using a cartesian plane to do the calculations. All of this data is ...
Bombadillio's user avatar
0 votes
0 answers
22 views

Using QGIS, what is the simplest way to find the closest point in one layer for every point in the second layer?

In QGIS, I have two layers of points (layer #1 - blue, and layer #2 - pink). How can I find the closest point in layer #1 for each point in layer #2, and then save this new selection as its own new ...
p1unge's user avatar
  • 109
1 vote
1 answer
71 views

Distance unit in KNN using cross join lateral

The underlying geometries are SRID 4326. I want to confirm whether "pma.dist" in the select clause would be a distance in meters. Here is my code: SELECT clusters.areaid, ...
analyst92's user avatar
  • 847
3 votes
2 answers
119 views

Match rows from different tables by comparing similar lat lon coordinates

I am new to PostgreSQL and PostGIS. The first project I am working on is the first I am struggeling with and the one I would like to discuss. I have 2 stops tables I call for simplicity tbl_stops_1 ...
dancesWithCycles's user avatar
2 votes
1 answer
205 views

Select points within a buffer / radius of a point [duplicate]

When a user click on a map, I first want to get closest point (placename), then use that point to get number of points (schools) within 10km buffer / radius ... $str = pg_query($conn, "SELECT ...
Asanda Lamba's user avatar
2 votes
1 answer
136 views

Optimizing KNN query with multiple search conditions

I have two point tables in SRID 4326: Table 1. unjoined_pts & Table 2. midpts. I want to join the attributes from the unjoined_pts table to the closest midpts table based on a few conditions. The ...
MJM's user avatar
  • 975
6 votes
1 answer
377 views

Documentation for <->

I found this question and am wondering, is it for PostgreSQL? Where’s the documentation for <->? Trying to find the fastest way to get the distance between a lat and long earth coordinates in ...
user433342's user avatar
1 vote
2 answers
492 views

Find nearest point of interest for each category in PostgreSQL

I have 2 tables: poi and categories with below schema. POI table: id name category geog 1 poi-1 cat-1 point() 2 poi-2 cat-1 point() 3 poi-3 cat-2 point() 4 poi-4 cat-3 point() .. .. .. .. ...
apaleja's user avatar
  • 405
1 vote
2 answers
157 views

Golden section bandwidth seach in GWR always returning nearly all neightbors

I'm running binary GWR models in MGWR and GWR4, and regardless of the optimization criterion (AICc, BIC, CV) for adaptative KNN, the softwares are always choosing N-1 nearest neighbors, or close ...
Pedro Caliari's user avatar
2 votes
1 answer
375 views

How to snap points to line

I am having a table lines being MULTILINESTRING (2 Dimensions) and a table points being MULTIPOINT (3 Dimensions). The two are in the same CRS. Now, I am trying to snap all the points to the closest ...
four-eyes's user avatar
  • 3,398
2 votes
1 answer
94 views

Classifying lines based on number of points located on them using PostGIS [closed]

I have a lines layer (roads) and a points layer (accidents). I'm projecting the points on the lines with st_closestpoint so that I'm sure the accidents are located on the closest road line. I now want ...
Michele Cordini's user avatar
5 votes
3 answers
770 views

Get the first 100 points ordered by distance in PostGIS

I have a table of clients where I have the location of the client. The table has the latitude and longitude column and the geom field that creates it from latitude and longitude with the following ...
Javier Ferreira's user avatar
4 votes
1 answer
295 views

PostGIS Nearest neighbor really slow

I'm trying to set a link between two tables (db1 and db2) with a nearest neighbor approach and some condition checking. SELECT id, label_shop, linked_rows.* FROM shopitem.shopitem_database shop LEFT ...
Bil11's user avatar
  • 81
1 vote
1 answer
393 views

Spatialite: insert points, do knn search. how to?

So I want to add 20 points, run Spatialite knn and find 10 points. I created a simple test in colaboratory: import os from datetime import datetime import json from pprint import pprint from playhouse....
DuckQueen's user avatar
4 votes
1 answer
632 views

KNN for spatial data and two sets of coordinates

I'm trying to estimate an average value of distances to K nearest neighbors. I have two sets of coordinates and I'm trying to estimate the average distance from each point in one set of coordinates to ...
T72's user avatar
  • 43
7 votes
2 answers
456 views

Plot a point at the boundary of all polygons facing the nearest road line in PostGIS

I want to add/plot a point geometry at the boundary of all home polygons. Conditions: The point should be facing the road geometry. As it will considered as the approximate entry point for that ...
George V's user avatar
  • 339
4 votes
2 answers
388 views

Mapping points to nearest area

I have a bunch of UK postcodes, which are basically lat/lng points. I currently map these to user-defined "areas" as follows: Create a very small box around the point. Search for areas ...
Edward Hibbert's user avatar
0 votes
1 answer
408 views

Smoothing spatial data with KNN and local G statistic

When trying to smooth spatial data, I found this very helpful tutorial regional smoothing. There are two steps to estimate a "smoothed" value for a geographical point: Finding K nearest ...
John Smith's user avatar
1 vote
1 answer
1k views

Simple KNN for spatial data

I try to find an example to estimate an average value associated to a coordinate, with K nearest neighbors. I found this very help tutorial regional smoothing. This code can find the k nearest ...
John Smith's user avatar
1 vote
1 answer
226 views

Select multiple points closest to a set of coordinates

Given a LAS object with x,y,z coordinates, i.e LASfile <- system.file("extdata", "Megaplot.laz", package="lidR") las = readLAS(LASfile, select = "xyz") and ...
user2955884's user avatar
4 votes
2 answers
2k views

Calculating distance between a point and the nearest line, PostGIS

I have to add a column to my points table storing the distance (metres) between each point and the nearest line. Both are in the same projected coordinate system. How can I do it? I've tried this but ...
Luigi Falco's user avatar
6 votes
1 answer
4k views

PostGIS, closest point to another point

I have two tables: point1 and point2. For each point from table point1, I want to find the closest point from table point2. For example: For point with id=1 from table point1, the closest point from ...
Ana's user avatar
  • 79
2 votes
1 answer
352 views

Spatialite KNN - No results

SOLVED: f_table_name was not working as no spatialindex was created/defined using just the tutorial for VirtualKNN - Solution: Run the query SELECT CREATESPATIALINDEX('tablename','geom_col') Original ...
xovut's user avatar
  • 23
1 vote
1 answer
129 views

Location Based KNN script on Leaflet works for Chrome but other browsers and mobiles cause parse error - invalid geometry

As many of you know I have been producing a Leaflet map with a KNN function to show the closest pubs to a user's location. Live example to test for yourself: https://ces-web2.southwales.ac.uk/students/...
WMTS's user avatar
  • 101
1 vote
1 answer
69 views

Mixed SRID's when using users Lat & Lon coordinates to perform KNN through PostGIS when building points to jsonp

I have managed to create a script that passes the users lat and lon to a postgres query that finds the nearest 5 pubs to a users location. However, on execution I realised no matter what lat and long ...
WMTS's user avatar
  • 101
1 vote
1 answer
2k views

PostGIS: How to find Nearest Neighbour but only within a direction range?

I am attempting to find the nearest neighbour to a geometry in a certain direction but am becoming stuck at the planning stage. I understand the basic tools that I need but am failing to find anything ...
Phish's user avatar
  • 165
2 votes
1 answer
260 views

PostgreSQL/PostGIS KNN query using <-> operator in ORDER BY clause not using index

I am trying to set up a KNN query using the <-> operator in the ORDER BY clause and the query planner does not appear to be using the spatial index as expected. I am not using bleeding edge ...
Evan Rosenlieb's user avatar
2 votes
1 answer
168 views

Finding Station order number along way using PostGIS

In QGIS and PostGIS I have two layers: way (id, name, geom (multistring)) station (id, name, geom (polygon)) How do I find the station order along each way? I tried using code of Finding all ...
Daniele Piccolo's user avatar
1 vote
0 answers
170 views

How determine optimal epsilon value in meters for DBSCAN by plotting KNN elbow

Before doing DBSCAN I need to find optimal epsilon value, all the points are geographical coordinates, I need the epsilon value in meters before convert it to radians to apply DBSCAN using haversine ...
Javier Alejandro Rendon Carril's user avatar
1 vote
0 answers
126 views

KNN with "Big Data"

I have a point dataset of over 30mill records and I am trying to select the closest 5 lines to each point. Reading this post there seems to be two methodologies Using <-> however as I want to ...
tjmgis's user avatar
  • 3,330
4 votes
2 answers
830 views

Update points geometry while snapping them to nearest line

I have two tables in the PostGIS database - points and lines. And I want to update points geometry to snap them to line if they lay within a certain distance. By now I managed to create a new table ...
Elena Klimenko's user avatar
2 votes
1 answer
131 views

Replacement for pgr_pointToEdgeNode for pgr 3.0

I am working through the book 'pgRouting: A Practical Guide' and have come across a function that has been deprecated. The function is pgr_pointToEdgeNode. Is there a similar function I can use to ...
jbalk's user avatar
  • 7,583
2 votes
1 answer
610 views

Find all closest points to unique point in PostGIS

I have two tables valid_collisions (POINT) and intrct_pts (POINT) I want to create a new table using a nearest-neighbor type of query to find aggregate values from my valid_collisions. In essence I am ...
sTonystork's user avatar
1 vote
1 answer
75 views

Find kNN having reference in a table

I am working with PostgreSQL 12.2 and PostGIS 3. I have been through a lot of posts talking about "Lateral joins" and other solutions but I am stuck with some incoherent results up to now. I have ...
Pierre H.'s user avatar
2 votes
1 answer
1k views

Join/Summary Nearest Neighbors QGIS

I'm looking to spatial join points to their k nearest neighbors, with attributes summarized (mean, sum, sd, etc.). I have looked at Join by Location (Summary) and Nearest Neighbor Analysis but the ...
Unrelated's user avatar
  • 191
2 votes
3 answers
326 views

Reducing the processing time when handling millions of 3D points to 3D lines in PostGIS

I am trying to calculate the minimum distance between 16 millions of 3D points and 3D lines. The query is working for more or less 1 million data, but after that, it took so long and never ends. All ...
Pin_Eipol's user avatar
2 votes
1 answer
527 views

PostGIS: ST_ClosestPoint join error

I have two tables: one with geom points of species and an other one with height contour lines geoms. My Idea was to find per specie geom the closest point to a contour 'geom' line and then select its ...
Bastiaan Wakkie's user avatar
1 vote
0 answers
92 views

python queries distance the nearest object [closed]

I have 2 point layers, I want to calculate the shortest distance between 2 point layers by python code and enclose the conditions of satisfaction. For example, a point a when there is the shortest ...
User0AB's user avatar
  • 111
3 votes
1 answer
1k views

Find closest polygon from point and get its attributes?

Given a set of points and a set of polygons, I am trying to find for each point what the closest polygon is and bring over the attributes from that polygon to the point (together with the actual ...
Pitrako Junior's user avatar
3 votes
1 answer
550 views

Speedup function to calculate distance from centroid of building to river segment in PostgreSQL

I am trying to measure the distance from the centroid of a building to a river segment using the following function. CREATE OR REPLACE FUNCTION river_foo_mapper () RETURNS trigger LANGUAGE plpgsql AS ...
kartoza-geek's user avatar
  • 1,161
2 votes
1 answer
665 views

Convert lat-long to geom using nodejs pg-promise?

I have a DB that is used as a read only postgis DB. I also have an input of lat & long to search within my db(in which obviously the points are saved in geom type - using nodejs pg-promise if it ...
user1322801's user avatar
1 vote
1 answer
65 views

Nearest Neighbor Two Point Tables Return Line Geometry

I am looking to find the nearest neighbor between two point tables in PostGIS and return the ids of both along with the line geometry. I have been able to determine the distance for the nearest ...
wtgeographer's user avatar
2 votes
1 answer
330 views

ST_Distance does not give nearest node

I am trying to find the nearest point on the road network closest to a given point. I imported the road network into PostGIS from QGIS and am using the SQL window in QGIS DB Manager to query the road ...
Chintan Pathak's user avatar