Polygon containment

0
votes

Data is saved in Postgres database (with PostGIS extension). I have a table which contains columns: pid, geometry, parent. pID is primary key, geometry is geographical data which is described with points and parent column contains a pID of this record's parent. That means we want in table order like this:

  • A, square (geometry), C
  • B, square (geometry), C
  • C, polygon (geometry), D
  • D, polygon (geometry), \

The problem we are facing is how to find the closest/smallest parent polygon if we do not know their initial order. If we process C first, we can then add D as a parent, but if we start with A and B, D is not the correct parent.

Polygon containing

If you have any suggestion, how to solve this problem I would be grateful.

1
postgresqlpostgis

1 Answers

1
votes

Use an extra column with the area of the geography, and then in your query use contains, within, && or another contains operation. Finally order desc by this new column and limit 1 to get the parent

For example, a row must be :

  • A, square(geometry),C,Area(100),BoundingBox(square)
  • B, square(geometry),C,Area(100), BoundingBox(square)
  • C ,polygon(geometry), D,Area(2000),BoundingBox(polygon)

The st_area and st_envelope functions can help

The the query must be

Select id 
from Table 
Where st_envelope('parameter') @ BoundingBox 
ORDER BY Area DESC 
LIMIT 1

Form postgis docs http://postgis.net/docs/manual-2.3/ST_Geometry_Contained.html

@ — Returns TRUE if A's bounding box is contained by B's.

With @ you make the container filter, so you have all the 'parents' that contain the geometry, and then get one of them (order by area)