PostGIS: Find & test Spatial Functions (for our agreegation methods)

[mariidpm]

From our tech report: 1) Geometric center; 2) Bounding rectangle; 3) Clustering of points (DBSCAN) [27]; 4) Hexagons; 5) Aggregation per administrative units.

[mariidpm]

PostGIS Spatial Functions defined in chapter 8 of their documentation

1) Geometric center;

• PostGIS function name and link to definition: ST_Centroid
• Examples: https://gis.stackexchange.com/questions/108973/how-to-get-the-centroid-of-a-set-of-points-in-postgis
• Query options: https://www.notion.so/Geometric-Center-Spatial-Function-82f94912c3bd4f46bf2b812d67de7f86

2) Bounding rectangle;

• PostGIS function name and link to definition: ST_Envelope
• Examples: https://postgis.net/docs/manual-1.4/ST_Envelope.html https://gis.stackexchange.com/questions/83387/performing-bounding-box-query-in-postgis https://gis.stackexchange.com/questions/14948/bounding-box-for-postgis-table
• Query options: https://www.notion.so/Bounding-rectangle-box-Spatial-Function-315717263412421ca376238ee5da6c80

3) Clustering of points (DBSCAN) [27];

• PostGIS function name and link to definition: ST_ClusterDBSCAN, there is also: ST_ClusterKMeans and ST_ClusterWithin
• Examples: For KMeans: https://access.crunchydata.com/documentation/postgis/3.1.1/ST_ClusterKMeans.html
• Query options: https://www.notion.so/Clustering-of-points-DBSCAN-Spatial-Function-558facf34d9043ff86b6b03677182169

4) Hexagons;

• PostGIS function name and link to definition: ST_Hexagon
• Examples: https://www.notion.so/Hexagons-Spatial-Function-5cf0095b3bd3483a8f07715bae8edb52

5) Make line (note: as we have points, the line is the same point, not sure how useful it is (as per our dataset) but it's the other function I could make work for us)

• PostGIS function name and link to definition: ST_MakeLine
• Examples: https://www.notion.so/Make-line-9e59cde3f2064770b8c1f1aeae2e56d4

[dancingsushii]

@mariidpm Mari, nice work! I've already mentioned it once with Inga but didn't discuss it with you. Imho the last method is too complex to implement. I used it in the tech report because back then I thought it might look very interesting for our previous use case. But for now, I think we should change it. What do you think?

[mariidpm]

@dancingsushii sounds good, I couldn't find anything in postgis for it, so happy to switch for another method which we can use in postgis