PostGIS: How to return polygon nearest the point

Question

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

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


           

Answer 1

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.