Return the parent id and name for a given geometry

[antoniocarlon]

Return the parent id and name for a given geometry (in the OBS_GetMCDOMVT or in the xyz table generation??)

[juanignaciosl]

Related: CartoDB/Geographica-Product-Coordination/issues/14#issuecomment-415531543

[juanignaciosl]

We have to add the parent names in a semantic way in Tilesets API:

id: '120570051011032'
block_id: '120570051011032'
block_group_id: '120570051011'
county: 'Hillsborough'
state: 'Florida'
...
median_age 27.5
total_pop 317

Keep in mind that a child can have multiple parents.

Implementation:

Two tables, one for the information of each feature, and another for parent/child relationships:

• id, type, name This could arguably be a view over existing x, y, z tables if type is added. type is the level (block, block group...).
• child_id, parent_id, weight. weight is a number that should let clients decide which parent to choose if they only want one. For example, it might be the area of the intersection with the child.

Loading procedure:

0. Make a ordered list with the level tables, from lowest (blocks) to highest (states).
1. For each level table: 1.1 For each feature: 1.1.1 Add it to id, type, name table. 1.1.2 Get a point with point on surface. 1.1.3 For each level above the current one (next 2 steps can probably be done in one single query): 1.1.3.1 Intersect the point on surface to get all the parents. 1.1.3.2 Compute the intersection for each one and insert this in the parent/child relationships table.

Note that...

• There's one different table per country.
• We need some missing boundaries (such as neighbourhoods) to provide better context.

It will be recursively queried to return the tile. If it were slow we'd also precompute it, but that's not likely to happen, as tables should be pretty small.

[javitonino]

How does point on surface reconcile with multiple parents of the same level? This happens with non-hierarchical geometries (IIRC we have some of those).

If you intersect a single point from the child, you are going to only get one parent (assuming a non-overlapping topology). In order to get all parents, you'd need to do a polygon-to-polygon intersection (much slower, I know).

[antoniocarlon]

True, we will need to perform heavy polygon to polygon intersections in those cases. Fortunately, we know them and we can limit those heavy intersections to the non coupled layers, keeping the point on surface approach for the rest

[juanignaciosl]

How does point on surface reconcile with multiple parents of the same level? This happens with non-hierarchical geometries (IIRC we have some of those). ... we know them and we can limit those heavy intersections to the non coupled layers

Which are the geometries where point in polygon is not enough?

[antoniocarlon]

Those where the layer and its parent are not coupled (a feature can have several parents). In US, I think that we won't find this case (except for zipcodes, maybe, I'm not sure)

[juanignaciosl]

ETL: CartoDB/bigmetadata/pull/572 Tiler: CartoDB/do_tiler/pull/38

[juanignaciosl]

Deployed for US.