1

I have two tables in a PostgreSQL / PostGIS database 1 polygon and the other point data How do I modify the following query so it returns only the smallest polygon nearest the point The red in the picture enter image description here

QUERY

select pt.point_id, ply.polygon_id,ply.admin_level
from point_table pt
join polygon_table as ply
on ST_WITHIN(pt.geometry, ply.geometry)
WHERE pt.point_id = 13424428  -- To Test  only

The above returns

point_id    poly_id admin_level
13424428    -1627120    10
13424428    -295351 8
13424428    -180837 6
13424428    -151336 5
13424428    -58447  4
13424428    -62149  2

    

I need only the first row for this point in polygon with admin_level 10

EDIT This Query will form a link table to join the point to it’s nearest parent polygon It will look something like this, there could be multiply points in the same polygon

point_id    poly_id admin_level
13424428    -1627120    10
13424429    -1627120    10
13424430    -1627120    10
13424422    -1627121    4
13424423    -1627121    4
13424434    -1627121    4


           
1
  • ORDER BY ST_Distance(...) LIMIT 1 would do it, but only for a single point.
    – Vince
    Apr 21 at 14:22
3

If I understand your question right: "so it returns only the smallest polygon nearest the point", you can ORDER BY the size of the polygon (or another criterion you choose) and then return only the first record:

select pt.point_id, ply.polygon_id,ply.admin_level
from point_table pt
join polygon_table as ply
on ST_WITHIN(pt.geometry, ply.geometry)
WHERE pt.point_id = 13424428

ORDER BY ST_AREA(ply.geometry) LIMIT 1

(The last line is what's added to your query).

-- Update --

There may be other ways. But to find the polygon for each point, one way is to use first() aggregate function in PostgreSQL, which is defined here and requires installing a new PostgreSQL extension. If you can install the extension, then you can GROUP BY the id of the points and then take the first polygon out of each group based on the ORDER BY ST_AREA(ply.geometry):

select pt.point_id
   , first(ply.polygon_id ORDER BY ST_AREA(ply.geometry))
   , first(ply.admin_level ORDER BY ST_AREA(ply.geometry))
from point_table pt
join polygon_table as ply
on ST_WITHIN(pt.geometry, ply.geometry)
GROUP BY pt.point_id;

I haven't verified this but I think something along this line will work for your data.

You can probably use sub-queries as described in this related DBA.SE question. It's not exactly the same, but close.

3
  • As stated the WHERE clause is only there to test how do I scale it up if this removed please see Edit
    – Holly
    Apr 21 at 15:38
  • @Holly I've updated the answer according to your edits. Note that it requires a non-standard aggregate function first as described in the link.
    – tinlyx
    Apr 21 at 16:29
  • This could also be implemented without the aggregate library, just by using row_number() over(partition by point.id order by ST_Area(...)) and restricting to the first ordered value for each point.
    – Vince
    Apr 22 at 3: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.