Databases
StackOverflow2013
Postsdbo
POHow many "entities" are beneath this hierarchical structure. Nested Set/Adjacency (How many POI are inside of England)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow many "entities" are beneath this hierarchical structure. Nested Set/Adjacency (How many POI are inside of England)
    text
    copied!<blockquote> <p><strong>Development language and DB: PHP/MySQL</strong></p> </blockquote> <p>I have a table <code>geo_places</code> with something like 8 million geographical locations. </p> <p>These places are all hierarchical, and I use</p> <ul> <li>parent_id <strong>(adjacency)</strong>, </li> <li>lft/rgt <strong>(nested set)</strong> </li> <li>and ancestry <strong>(enumerated)</strong>.</li> </ul> <p>Now, I have "points of interest" table called <code>entities</code> which are assigned to a geographical location, and I record against each entity the: </p> <ul> <li><code>lft</code> value of the location in the <code>geo_places</code> </li> <li>and the actual <code>ID</code> of the geographical location.</li> </ul> <p>Now I need a way to provide a directory listing with count EFIFICNETLY (but I will be caching this anyway), of all the places which are beneath a location. </p> <p>For example, if I take Europe, then I should see all places which have a parent_id of Europe, and then also the amount of entities below it. Keeping in mind that a place does not get assigned directly to Europe, but might be assigned to a small village in Italy (which is a child of Europe). </p> <p>You know that it is a child of Europe either because:</p> <ul> <li>the <code>lft</code> value of the small village in Italy is between the <code>lft</code> and <code>rgt</code> value of the location</li> <li>Or because the ancestry maps to the place. </li> </ul> <p>For example, Europe would have an ancestry path of <code>/1</code> and an ID of 5. (The 1 would signify the "World"). And then the small village in Italty would have <code>/1/5/234/28924/124128</code> </p> <p>where 1 = World 5 = Europe 234 = Italy 28924 = Bergamo etc etc...</p> <p>Anyway, this is how I have structured the dataset, and I have already been using a mixture of the hierarchical structures in order to make my queries a lot more efficient (for those of you wondering why am I am supporting nested set, adjacency and enumerated.. it's because I get the best of all structures this way). </p> <p>This is an example of what I am trying to do. <img src="https://i.stack.imgur.com/CfL6D.jpg" alt="Example"></p> <p>I am flexible and can change how I manage locations if neccessary. However, this is also a multi tenant application, so I would like to try and avoid saving counts against the geo_places if it can be avoided. </p> <p>So simply put: Pick a location... then show all locations which have points of interest assigned either to that location, or a child of that location. </p> <p>Any recommendations? </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload