Find n Nearest Neighbors for given Point using PostGIS?
Now since PostGIS 2.0, there's a KNN index for geometry types available.
This gives you nearest 5 records with regard to how far they are away from "your location...".
SELECT *
FROM your_table
ORDER BY your_table.geom <-> "your location..."
LIMIT 5;
See <->
operator in PostgreSQL manual.
Find nearest point using PostGIS
Processing records 1 by 1, in a loop, induces a lot of network traffic to the DB.
Instead, try to update all entries at once, in a single statement (which you can send from the pyton script if you wish).
UPDATE table_a
SET id_b = (
SELECT id_b
FROM table_b
ORDER BY table_b.geom <-> table_a.geom
LIMIT 1
)
WHERE id_b IS NULL;
PostGIS: How to find N closest sets of points to a given set?
The answer on how to find the N-closest neighbours in PostGIS are given here:
Postgis SQL for nearest neighbors
To summarize the answer there:
You need to create a geometry object for your points. If you are using latitude, longitude, you need to use 4326.
UPDATE season SET geom = ST_PointFromText ('POINT(' || longitude || ' ' || latitude || ')' , 4326 ) ;
Then you create an index on the geom field
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
Then you get the kNN neightbors:
SELECT *,ST_Distance(geom,'SRID=4326;POINT(newLon newLat)'::geometry)
FROM yourDbTable
ORDER BY
yourDbTable.geom <->'SRID=4326;POINT(newLon newLat)'::geometry
LIMIT 10;
Where newLon newLat are the query points coordinates.
This query will take advantage of kNN functionality of the gist index (http://workshops.boundlessgeo.com/postgis-intro/knn.html).
Still the distance returned will be in degrees, not meters (projection 4326 uses degrees).
To fix this:
SELECT *,ST_Distance(geography(geom),ST_GeographyFromText('POINT(newLon newLat)')
FROM yourDbTable
ORDER BY
yourDbTable.geom <->'SRID=4326;POINT(newLon newLat)'::geometry
LIMIT 10;
When you calculate the ST_distance use the geography type. There the distance is always in meters:
http://workshops.boundlessgeo.com/postgis-intro/geography.html
All this functionality will probably need a recent Postgis version (2.0+). I am not sure though.
Check this for reference https://gis.stackexchange.com/questions/91765/improve-speed-of-postgis-nearest-neighbor-query/
EDIT. This covers the necessary steps for one point. For set of points:
SELECT n1.*,n2.*, ST_Distance(n1.geom,n2.geom)
FROM yourDbTable n1, yourDbTable n2
WHERE n1.setId=1 AND n1.setId=2 //your condition here for the separate sets
AND n1.id<>n2.id // in case the same object belong to 2 sets
ORDER BY n1.geom <->n2.geom
LIMIT 20;
Perform multiple nearest neighbor query by JOIN clause on PostGis in Postgres
You want a LATERAL join, where a subquery can reference a column from an earlier table in the join list.
SELECT * FROM query CROSS JOIN LATERAL
(SELECT * FROM data ORDER BY q<->d LIMIT 1) foo;
Find KNN with a geometry data type using POSTGIS in postgresql
You are missing <->
operator which Returns the 2D distance between A and B
. Make sure your geom
types and SRID
are the same.
SELECT ST_Distance(geom,
'POINT(178.1375 51.6186)'::geometry) as distance,
ST_AsText(geom),
name, id
FROM mylocations
ORDER BY geom <-> 'POINT(178.1375 51.6186)'::geometry limit 10
How to find the nearest point in POSTGIS?
If you are using recent versions of the software, you can quickly find the K nearest neighbors to a point in PostGIS using KNN-GiST techniques. Small values of K are fastest, and 1 is about as small as they get, so this should work very well for you. I've only used KNN-GiST with text trigrams, but I know they work with PostGIS, too -- I just don't know the best page to read to get started with it. A web search for "postgis knn gist" shows lots of likely candidates.
PostGIS: Find nearest point from a list of 3D points
You were really close.
Taking data sample into account, you're doing a lot of SRS transformations to populate your table. So, the stored SRS needs to match the one used in the query as well.
SELECT id,
ST_3DDistance(
geom,
ST_Transform(
ST_SetSRID(
ST_MakePoint(4571547, 5800297, -246,0312),31468),3857)) AS dist
FROM points_list
ORDER BY dist LIMIT 1;
id | dist
----+-------------------
2 | 6.936250729464996
(1 Zeile)
You can avoid using ST_Transform
if you already can provide ST_3DDistance
with the coordinates in the SRS 3857
.
Related Topics
Convert Unknown Number of Comma Separated Varchars Within 1 Column into Multiple Columns
Retrieve the Maximum Length of a Varchar Column in SQL Server
Show Only Most Recent Date from Joined MySQL Table
Odata Case In-Sensitive Filtering in Web API
Calculating Percentage Within a Group
Postgresql Check Constraint for Foreign Key Condition
How to Make Shiny Reactivity Work with SQL Database
Retrieving I18N Data with Fallback Language
Randomly Select a Row with SQL in Access
T-SQL Query:Getting Child Nodes of a Parent
Big Query - Create a Table/View from a Temp Table
Storing Matrices in a Relational Database
Finding the Data Types of a SQL Temporary Table
SQL Server:Check If Variable Is Empty or Null for Where Clause
For Each Quarter Between Two Dates, Add Rows Quarter by Quarter in SQL Server
How to Use a SQL Window Function to Calculate a Percentage of an Aggregate