3

I have a location collection tool which send me location data of our survey. Surveyors send multiple data of a single item. Such as they send numarous data for a school like the following image. From the image we can clearly identify few locations those are actually not collected properly or inaccurate location came due to GPS accuracy. Here I have marked red circle which location need to be excluded and the green circle indicates this is the probable location. IF we create a centroid based on all points then it will not indicates the probable correct location due to few highly inaccurate data. I wonder, if any method or tool can exclude the wrong location and create a cluster based on densed location points to create a centroid then the centroid can be established as probable right location of the surveyed item. I have millions of item those have hundreds of location each. I came to know that k-means do something like that but it does not do on a single item. I have four column in table.

  1. fid
  2. item_id
  3. item_name
  4. geom

Is there any method to create item wise clustering where every cluster have a value which indicates its containing points to get the probable right location?

enter image description here

  • 5
    try ST_GeometricMedian; that function tries to compensate for outliers when computing a centroid for MultiPoints. – ThingumaBob Apr 24 at 11:20
  • 1
    What @ThingumaBob says. You could also try running postgis.net/docs/ST_ClusterKMeans.html a few times and finding points that are consistently a long way for computed centroids or get assigned to different clusters. – John Powell Apr 25 at 7:57
  • @ThingumaBob please add it to answer – Devils Dream Apr 25 at 11:35
2

ST_GeometricMedian should compensate for outliers in MultiPoint geometries, e.g. sth. like:

SELECT ST_GeometricMedian(ST_Collect(geom)) AS geom
FROM   <your_table>
GROUP BY
       item_id
;

General info on the concept, and difference to weighting (i.e. ST_Centroid).


As an alternative, using

ST_ClusterDBSCAN(geom, <eps>, 1) OVER(PARTITION BY item_id)

allows for cluster assignement over item_id and with an <eps> distance representing e.g. the specific GPS devices' standard margin of error; getting the centre of the highest populated cluster should be a precise approximation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.