0

I need to display the max of count for this query. I tried a nested way but I could not get it to return the centroid of that cell.

Here flickr_edin table data consists of points and grid100m table data contains polygons

SELECT Max(P_density)
FROM(COUNT(a.geom) as P_density,ST_ASTEXT(ST_CENTROID(b.geom)) 
FROM flickr_edin a JOIN grid100m b ON ST_WITHIN(a.geom, b.geom)
GROUP BY b.geom
ORDER BY P_density DESC) A;

How should I frame it instead so I can get the centroid point returned as well?

5
  • 2
    What are you trying to get? Max of count doesn't make a whole lot of sense as count() gives you the sum of the features you're counting. Can you write out what you expect to get from your query?
    – Encomium
    Nov 22 '21 at 21:34
  • If you just want the highest value returned from the count, try LIMIT 1 at the end. You can also subquery/CTE and select MAX(P_density) from the initial query. BTW if you want that field to have a capital letter, you need to wrap it in quotation marks.
    – Encomium
    Nov 22 '21 at 21:39
  • @Encomium Hi, yes I wasn't able to get the sub query right, I did exactly that but I don't know where to place the ST_ASTEXT(ST_CENTROID(b.geom)) part of my query when I make the sub query for it because I want that returned too.
    – c.j
    Nov 22 '21 at 21:55
  • Ok, will write it out.
    – Encomium
    Nov 22 '21 at 22:12
  • @Encomium Thanks a lot. Do you mind checking out this previous query too? gis.stackexchange.com/questions/417022/… Honestly, Thanks a ton.
    – c.j
    Nov 22 '21 at 22:21
1

You can use Max on geom_centroid as well. See how this:

SELECT 
Max(p_density) as max_density,
Max(geom_centroid) as geom_centroid 
FROM
(
SELECT COUNT(a.geom) as p_density,ST_ASTEXT(ST_CENTROID(b.geom)) as geom_centroid 
FROM flickr_edin a JOIN grid100m b ON ST_WITHIN(a.geom, b.geom)
GROUP BY b.geom
) A;

Compares to this:

SELECT 
Max(p_density) as max_density
FROM
(
SELECT COUNT(a.geom) as p_density,ST_ASTEXT(ST_CENTROID(b.geom)) as geom_centroid 
FROM flickr_edin a JOIN grid100m b ON ST_WITHIN(a.geom, b.geom)
GROUP BY b.geom
) A;

Do you get the same max density from both queries?

4
  • Really appreciate the help. Do you mind checking out this previous query too? gis.stackexchange.com/questions/417022/…
    – c.j
    Nov 22 '21 at 22:23
  • Sure. I think I see the issue
    – Encomium
    Nov 22 '21 at 22:25
  • you're a life saver thank you !
    – c.j
    Nov 22 '21 at 22:26
  • Go have a look now. I didn't recreate your data so I'm not sure if the query returns what you're looking for.
    – Encomium
    Nov 22 '21 at 22:28

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.