7

I want to clustering locations use ST_ClusterWithin function:

geometry[] ST_ClusterWithin(geometry set g, float8 distance);

My table has column location of geography(Point,4326) type and I don't understand how to convert it correctly to geometry type with ability to specify distance in meters.

The following query can convert geography to geometry but allow to specify distance in degrees that is not acceptable for me:

select ST_AsText(unnest(ST_ClusterWithin(location::geometry, 0.0001)))
from places

So, how to use this function with geography data types?

Community
  • 1
Maxim
  • 171
  • 5
  • 1
    Do you geographic points cross the date line? Or are they restricted to a small geographic area? One option is always to use ST_Transform to convert to some projected coordinate system in meters, along with a cast to geometry. You can always convert back to 4326 and geography afterwards. – John Powell Apr 14 '16 at 08:21
  • All location points places inside of one city and don't cross any date line. Which SRID related to projected coordinate system should I choose to specify distance in meters? – Maxim Apr 14 '16 at 09:27
  • Which City :-) Most counties have their coordinate systems which are appropriate to the geoid, determine type of projection, etc, etc, making measurements as accurate as possible in projected meters. – John Powell Apr 14 '16 at 09:29
  • City: Russia, Saint-Petersburg. Could you please explain how you will find appropriate SRID for this city? – Maxim Apr 14 '16 at 10:20
  • 1
    I don't know much about Russian coordinate systems, but this looks potentially useful: https://epsg.io/3350-5044 Accuracy 3m? In a small area, ie, a city, any distortion should be fairly constant, so for the purposes of cluster distances, the 3m will be a constant (more or less). – John Powell Apr 14 '16 at 10:40

1 Answers1

3

The current implementations of ST_ClusterWithin and its friends ST_ClusterIntersecting and ST_ClusterDBSCAN (PostGIS 2.3) are all Cartesian-based, so they won't work on geographic coordinates. Your best option is to do as @JohnBarça says and transform your coordinates to a planar system in meters. The "private" function _ST_BestSRID might be of some use here. Or for an exploratory first pass, or if your cluster distance is highly approximate, you can do something hackish like distance_in_meters / 111111.0.

Community
  • 1
dbaston
  • 13,048
  • 3
  • 49
  • 81
  • I guess the magical number 111111.0 is approximation of 10,000 km/90 degrees, the distance from pole to equator? This hack is kind of true in north(ing) direction, but in the easting it is accurate only in equator, as around poles you can cover whole 360 degrees easting in one meter, as extreme example. However, specifically for clusters which is never precision science, and if your data is not far north/south, then it may be not that bad approximation. Not many would notice :) But as general hack for st_distance type of calculations this could often give bad results. – JaakL Sep 27 '16 at 09:13