What is the better table layout for polygons inside polygons

Question

enter image description here

I'm pretty new to spatial stuff so forgive the beginner question. If I expect to have many polygons that look like the picture, is it best to have 1 table with a column defined like

POLYGON ((35 10, 45 45, 15 40, 10 20, 35 10),(20 30, 35 35, 30 20, 20 30),(20 30, 35 35, 30 20, 20 30),(20 30, 35 35, 30 20, 20 30),(20 30, 35 35, 30 20, 20 30))

or Two tables, one which has the outside polygons and one which has the inside polygons and the id of the containing polygon from the first table?

My use case will often be queries asking "Is this point in the outer polygon?" and "Which polygon(s) contain this point?". For the latter, I would like to only return the inner-most polygon if possible.

So for my first case (1 table) I'm thinking sudo code would look something like.

len = SELECT NumInteriorRings(g1)

and then loop through each interior polygon using something like

for(i = 1; i < len; ++i){
    SELECT ST_CONTAINS(InteriorRingN(poly,i),point)
}

My Second case (2 tables) would look more like

SELECT sub_poly_id, ST_CONTAINS(poly, point) as contains 
FROM sub_poly_table WHERE parent_poly_id = 23;

The second case seems more clean but am I just butchering my first case? Is there a better way to accomplish this?

Are the green bits supposed to be inner rings of the blue polygon or are they separate polygons? — John Powell, Sep 19 '14 at 13:18
I still don't understand. Are they inner rings, or separate polygons, that happen to be inside other polygons, the concept of children polygons is new to me. — John Powell, Sep 26 '14 at 08:22
So maybe my use case would be helpful to clear my use of terms. I will be running queries against these polygons to check if a point is inside them. So if a point is outside the large polygon, do 0. If a point is inside only the outer polygon, do 1. If a point is inside the outer polygon and one of the inner polygons, do 2, 3, 4 or 5 (depending on which green polygon). The inner polygons will always be completely inside the outer polygon. — Geoffrey Ochsner, Sep 26 '14 at 14:33
I have added an answer. Let me know if I have missed the point of your question. — John Powell, Sep 29 '14 at 07:09

John Powell · Accepted Answer · 2014-09-29T06:06:49.650

There is no need to create a second table to accomplish what you need. It adds extra complication for no extra gain, because you can always test for containment with the whole polygon, just the outer ring or any of the interiorings. It will also make updates messier, as you will need to update the geometry's in two tables, rather than a single geometry.

You can use a counter table (just containing the numbers 1, 2, 3, 4, ...,) to extract each inner ring in order to test for the intersection of any interior ring against some input point and avoid using any loop constructs. For example,

insert into test (geom) values (geomfromtext('POLYGON 
  ((0 0, 100 0, 100 100, 0 100, 0 0),
  (10 10, 10 20, 20 20, 20 10, 10 10),
  (80 80, 80 90, 90 90, 90 80, 80 80),
  (50 50, 50 60, 60 60, 60 50, 50 50),
  (20 30, 35 35, 30 20, 20 30)) '));

create table counter (id int primary key);

Put some numbers in counter, in practice, you would probably want many more.

insert into counter values (1), (2), (3), (4), (5);

select x.id as ring_num, 
       contains(interiorringn(geom, x.id), geomfromtext('POINT(15 15)')) as contains
from test, 
     (select id from counter where id <=
      (select numinteriorrings(geom) from test)
    ) x ;

which returns:

ring_num contains
1        1
2        0
3        0
4        0

in this example, where I have changed you polygon's inner rings so they are actually different. Clearly, you can add the parent_poly_id back into the where clause of the query, once you have established that the outer polygon contains the point. You would also want to add 1 to x.id, so that inner rings 1 corresponds to 2, inner ring 2, corresponds to 3 (as in your comments) and add contains=1 to the where clause

Using a counter table like this is something of a hack, but MySQL lacks a function to generate arbitrary length series on the fly (unlike, say, Postgres), but still cleaner than using looping constructs or secondary tables.

What is the better table layout for polygons inside polygons

1 Answers1