PostGIS: How to find N closest sets of points to a given set?
geospatial, gis, postgis, postgresql, ruby-on-rails
Solution
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;
Problem
I am using PostGIS/Rails and have sets of points with geolocations. ``` class DataSet < ActiveRecord::Base # these are the sets containing the points has_many :raw_data # attributes: id , name end class RawData < ActiveRecord::Base # these are the data points belongs_to :data_set # attributes: id, location which is "Point(lon,lat)" end ``` For a given set of points I need to find the N closest sets and their distance; or alternatively: For a given max distance and set of points I need to find the N closest sets. What is the best way to do this with PostGIS? My versions are PostgreSQL 9.3.4 with PostGIS 2.1.2