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
12 votes
2 answers
5k views

How to UPDATE with LATERAL Nearest-Neighbour query?

Say I have table gps like: CREATE TABLE gps( gps_id serial primary key, measured_timestamp timestamp, geom Geometry(Point,4326), stop_id int --Null ); And I have a table of stops like: ...
raphael's user avatar
  • 3,407
8 votes
2 answers
2k views

Finding nearest neighbor for each point in the same table

We've got a table of approximately 300,000 points and would like to find the nearest neighbor (within 10km) to each point in the same table. The most efficient query we've found so far is below, but ...
cokrzys's user avatar
  • 657
4 votes
1 answer
2k views

Efficient way to find nearest feature between huge postgres tables

I have two tables (g1 and g2), both with around two million features. I need to know for each feature in g1, which is the closest feature from g2, with a condition. Since they are both so huge, I also ...
A.T.'s user avatar
  • 292
2 votes
1 answer
504 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
1 answer
222 views

Get barrier edge id

I have a table of POINTs that represent barriers. I would like to be able to find the closest edge id on the edge graph so that I can have the routing algorithm ignore that edge (thus route around it)....
Logan M's user avatar
  • 125
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
7 votes
1 answer
2k views

Automatically Snapping Points to Closest Part of Line

I have some line points representing manholes. I also have line features representing roads. Both of these features are currently living in a PostGIS database. I'd like to automatically move these ...
DCooper's user avatar
  • 71
0 votes
1 answer
503 views

Optimizing my script; taking too long; distance between many objects PostGIS

I have many many points and want to calculate the shortest distance from each point to the closest line of a table with many many lines. I think my underneath code is correct, I however never get an ...
Zuenie's user avatar
  • 585
3 votes
1 answer
520 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
325 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
6 votes
1 answer
3k 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
3 votes
1 answer
1k views

PostGIS -> LineString + PointZ = LineStringZ

DATA I have 2 tables in PostGIS: linestable with MultiLineString geom surveypointtable with Point + elevation attribute GOAL The goal is to rebuild the lines as 3D replacing line's vertices with ...
Lukasz Klosinski's user avatar
2 votes
1 answer
349 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,378
5 votes
1 answer
302 views

ST_DistanceSphere and KNN discrepancy

I have a table containing thousands of data points around London. I'm trying to write a query to find the closest data points to a given latitude and longitude. I'm getting different results based ...
Joaquim d'Souza's user avatar
4 votes
1 answer
2k views

Postgis KNN vs ST_DWithin for nearest neighbour search with a radius

We are trying to get nearest neighbors within a user given radius. As far as I understand ST_DWithin degrades as distances are increased. The problem with using KNN is that it keeps on searching even ...
Rahul Bansal's user avatar
4 votes
2 answers
792 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
3 votes
1 answer
2k views

Snap point to point with St_Snap Postgis

I write a request that allows to snap a point to another. Here is the request: SELECT      f.gid as gid,     ST_Snap (f.Geom, g.Geom, min, 5) as geom FROM      boiten as f,      (...
Rania ben othmen's user avatar
2 votes
1 answer
995 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
1 answer
620 views

Distinct st_shortestline

I have 61 pts and numerous rds. I would like to find the shortest distance to the nearest road using st_shortestline. I need 61 unique line segments, one for each point to its nearest road. Instead I ...
wtgeographer's user avatar
2 votes
3 answers
2k views

Projecting GPS point to point in closest pgrouting road (map matching)?

I'm quite new to PostGIS and pgRouting. I'm interested in doing a simple map matching. Precisely, finding the closest point in a given OSM road network. I’ve successfully imported the OSM network ...
Marcos Roriz Junior's user avatar
1 vote
1 answer
63 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
0 votes
1 answer
378 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