2

I am new to GIS, I am using PostGIS(2.3). I have two problems I am not clear with

Problem-1: I have list of locations(longitude, latitude, 4326), what is best/correct projection to use while clustering if I want to use unit in meters? I have looked around, people used 2950, with this when I project points back on map markers are going somewhere else, here is the query I am using

select
    array_to_json(
        array_agg(
            row_to_json(t)
        )
    )
from
    (
        select
            to_char(
                row_number() over(),
                '1'
            ) as id,
            ST_NumGeometries(gc) as count,
            ST_AsGeoJSON(
                ST_Transform(
                    gc,
                    4326
                )
            ) as points,
            ST_AsGeoJSON(
                ST_Transform(
                    ST_Centroid(gc),
                    4326
                )
            ) as center,
            ST_AsGeoJSON(
                ST_Transform(
                    ST_ConvexHull(gc),
                    4326
                )
            ) as border,
            avg_cost as "averageCost",
            min_cost as "minCost",
            max_cost as "maxCost"
        from
            (
                select
                    unnest(
                        ST_ClusterWithin(
                            ST_Transform(
                                geom,
                                2950
                            ),
                            500
                        )
                    ) gc,
                    avg( cost ) avg_cost,
                    min( cost ) min_cost,
                    max( cost ) max_cost
                from
                    user_locations
                where
                    region = 'sanjose'
            ) f
    ) t

Problem-2: I want to control clustering with respect to map zoom, so that I can change distance and minimum number of points in cluster, for this want to move to DBScan algorithm, it is not clear how to construct convexhull along with dbscan

Please help me

1

If you want to go by meters, it really depends on location of your data what is the best projection. some utm projection is generally preferred like for example this utmzone function https://trac.osgeo.org/postgis/wiki/UsersWikiplpgsqlfunctionsDistance will tell you the best utm for your long/lat data or you can use the built in _ST_BestSRID to get an internal suitable meter projection (don't store your data with project _ST_BestSRID gives though)

ST_ClusterDBScan would categorize your geometries into buckets and then to do the convex hull, you'd group by the bucket number.

Here is a simple example - hopefully you can follow:

WITH f AS (SELECT cost, geom, ST_ClusterDbScan(ST_Transform(geom, 2950), 500, 5) OVER() AS bucket
   FROM user_locations
    WHERE region = 'sanjose')
SELECT bucket, ST_ConvexHull(ST_Union(geom)) AS convexhull, 
    AVG(cost) AS "avgCost", MIN(cost) AS "minCost", MAX(cost) AS "maxCost"
FROM f
GROUP BY bucket;
  • Using ST_Collect instead of ST_Union should make this a bit cheaper. – dbaston Sep 10 '17 at 16:53
  • Thanks this is helpful. As @dbaston mentioned ST_Collect is at leat 5 to 10 ms faster. But selecting right projection and eps looks like still a challenge – Srinivas Sep 10 '17 at 17:35
  • What is the challenge? did you have problems using one of the functions I pointed at? regarding ST_Collect, ST_Union it really depends on the overlapping of data which ends up being faster. int case of lots of duplication, I've found ST_Union more efficient because your final geometries will be smaller. 5/10 ms doesn't sound statistically significant. – LR1234567 Sep 11 '17 at 0:37
  • @LR1234567 your solution is working really good, I am not bothered about 5/10 ms difference also. Before using PostGIS, I have tried using in memory clustering solution using this link. This looked good on map, I am not able to achieve any result near to in memory with PostGIS, this has become challenge in terms of finding better projection and cluster parameters. – Srinivas Sep 11 '17 at 1:02

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.