{"action":"created","issue":{"url":"https://api.github.com/repos/nationalparkservice/places-data/issues/543","repository_url":"https://api.github.com/repos/nationalparkservice/places-data","labels_url":"https://api.github.com/repos/nationalparkservice/places-data/issues/543/labels{/name}","comments_url":"https://api.github.com/repos/nationalparkservice/places-data/issues/543/comments","events_url":"https://api.github.com/repos/nationalparkservice/places-data/issues/543/events","html_url":"https://github.com/nationalparkservice/places-data/issues/543","id":133380492,"number":543,"title":"Places Boundaries Database Reorganization","user":{"login":"mtaylorlong","id":4741443,"avatar_url":"https://avatars.githubusercontent.com/u/4741443?v=3","gravatar_id":"","url":"https://api.github.com/users/mtaylorlong","html_url":"https://github.com/mtaylorlong","followers_url":"https://api.github.com/users/mtaylorlong/followers","following_url":"https://api.github.com/users/mtaylorlong/following{/other_user}","gists_url":"https://api.github.com/users/mtaylorlong/gists{/gist_id}","starred_url":"https://api.github.com/users/mtaylorlong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtaylorlong/subscriptions","organizations_url":"https://api.github.com/users/mtaylorlong/orgs","repos_url":"https://api.github.com/users/mtaylorlong/repos","events_url":"https://api.github.com/users/mtaylorlong/events{/privacy}","received_events_url":"https://api.github.com/users/mtaylorlong/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/nationalparkservice/places-data/labels/enhancement","name":"enhancement","color":"84b6eb"},{"url":"https://api.github.com/repos/nationalparkservice/places-data/labels/places-boundaries","name":"places-boundaries","color":"5319e7"}],"state":"open","locked":false,"assignee":{"login":"mtaylorlong","id":4741443,"avatar_url":"https://avatars.githubusercontent.com/u/4741443?v=3","gravatar_id":"","url":"https://api.github.com/users/mtaylorlong","html_url":"https://github.com/mtaylorlong","followers_url":"https://api.github.com/users/mtaylorlong/followers","following_url":"https://api.github.com/users/mtaylorlong/following{/other_user}","gists_url":"https://api.github.com/users/mtaylorlong/gists{/gist_id}","starred_url":"https://api.github.com/users/mtaylorlong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtaylorlong/subscriptions","organizations_url":"https://api.github.com/users/mtaylorlong/orgs","repos_url":"https://api.github.com/users/mtaylorlong/repos","events_url":"https://api.github.com/users/mtaylorlong/events{/privacy}","received_events_url":"https://api.github.com/users/mtaylorlong/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/nationalparkservice/places-data/milestones/2","html_url":"https://github.com/nationalparkservice/places-data/milestones/Park%20Tiles%203.0.2","labels_url":"https://api.github.com/repos/nationalparkservice/places-data/milestones/2/labels","id":1409340,"number":2,"title":"Park Tiles 3.0.2","description":"","creator":{"login":"mtaylorlong","id":4741443,"avatar_url":"https://avatars.githubusercontent.com/u/4741443?v=3","gravatar_id":"","url":"https://api.github.com/users/mtaylorlong","html_url":"https://github.com/mtaylorlong","followers_url":"https://api.github.com/users/mtaylorlong/followers","following_url":"https://api.github.com/users/mtaylorlong/following{/other_user}","gists_url":"https://api.github.com/users/mtaylorlong/gists{/gist_id}","starred_url":"https://api.github.com/users/mtaylorlong/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mtaylorlong/subscriptions","organizations_url":"https://api.github.com/users/mtaylorlong/orgs","repos_url":"https://api.github.com/users/mtaylorlong/repos","events_url":"https://api.github.com/users/mtaylorlong/events{/privacy}","received_events_url":"https://api.github.com/users/mtaylorlong/received_events","type":"User","site_admin":false},"open_issues":13,"closed_issues":1,"state":"open","created_at":"2015-11-12T21:58:40Z","updated_at":"2016-02-13T01:16:09Z","due_on":"2016-02-29T07:00:00Z","closed_at":null},"comments":2,"created_at":"2016-02-13T00:59:12Z","updated_at":"2016-02-13T18:18:14Z","closed_at":null,"body":"attn: @nateirwin, @cwlawlis802, @jimmyrocks, @jakecoolidge:\r\n\r\nLast fall, we reorganized our `places_boundaries` database and made great progress in opening up control over how parks show up in PT3. We were so organized, in fact, that we got greedy and started trying to use the `parks` table to add secondary labels to the map. I've thought a lot about this, and I think we need a different approach to handling these labels. **I think it's time we separate labels into their own table.**\r\n\r\nSeparating labels into their own table means that min_zoom settings have to come with them. If we do this, then we might as well be consistent and move min_zoom settings that deal with polygons into the polygons table.  Below, I've presented my outline for a reorganized the database:\r\n\r\n## Database Structure\r\nHere's what I think we need:\r\n- `parks`\r\n- `alt_unit_codes`\r\n- `parks_poly`\r\n- `parks_line`\r\n- `parks_point`\r\n- `parks_label`\r\n\r\n### `parks`\r\nThe master table that goes out to cartodb along with polygon boundaries. This table includes records for units and subunits only (no sites). Subunits are noted by their `subunit_of` field. `unit_id` is the primary key, used to relate geometries to these records. This table doesn't need any geometries.\r\n- `unit_id`\r\n- `unit_code`\r\n- `subunit_of`\r\n- `unit_name_short` fully formed for cartodb\r\n- `unit_name_long` fully formed for cartodb\r\n- `unit_designation`\r\n- `unit_area`\r\n- `unit_region`\r\n- `unit_state`\r\n\r\nNote: The unit names broken apart for labeling in Park Tiles are stored in the `park_label` table.\r\n\r\n### `alt_unit_codes`\r\nThe alternate unit codes table stores handles cases where a park has switched unit codes and may use the old one in one context and the new one in another.\r\n\r\n### `parks_poly`\r\nThe parks polygon table stores polygon geometries for each record in the parks table (units and subunits). There is a one-to-one relationship between `parks` and `parks_poly`.\r\n- `unit_id`\r\n- `unit_code`\r\n- `min_zoom_poly`\r\n- `min_zoom_border`\r\n- `min_zoom_tintband`\r\n- `simp_type`\r\n- `data_source`\r\n- `date_created`\r\n- `last_updated`\r\n- `geom_poly`\r\n\r\nNote: a subunit polygon will duplicate a part of its parent unit's polygon, but the parent unit's polygon will be a multipolygon that includes its subunits.\r\n\r\n### `parks_line`\r\nThe parks line table stores line geometries for each record in the parks table (units and subunits). Only certain parks (those with `parks_geom.simp_type=line`) need a line geometry at smaller scales. There is a one-to-one relationship between `parks` and `parks_line`.\r\n- `unit_id`\r\n- `unit_code`\r\n- `geom_line`\r\n\r\n### `parks_point`\r\nThe parks point table stores a point geometry for each park that represents its true geographic centroid. There is a one-to-one relationship between `parks` and `parks_point`.\r\n- `unit_id`\r\n- `unit_code`\r\n- `geom_point`\r\n\r\n### `parks_label`\r\nThe parks label table stores all the point geometries used to place labels in parks. It stores labels for units, subunits, and sites, all with their own min_zoom settings. There is a one-to-many relationship between `parks` and `parks_label`.\r\n- `label_id`\r\n- `unit_id`\r\n- `unit_code`\r\n- `label_type` options: unit, subunit, site\r\n- `label_name_short`\r\n- `label_name_long`\r\n- `min_zoom_label`\r\n- `min_zoom_label_long`\r\n- `min_zoom_label_center`\r\n- `ldir`\r\n- `ldir_enforce`\r\n- `geom_label`"},"comment":{"url":"https://api.github.com/repos/nationalparkservice/places-data/issues/comments/183717389","html_url":"https://github.com/nationalparkservice/places-data/issues/543#issuecomment-183717389","issue_url":"https://api.github.com/repos/nationalparkservice/places-data/issues/543","id":183717389,"user":{"login":"nateirwin","id":10443,"avatar_url":"https://avatars.githubusercontent.com/u/10443?v=3","gravatar_id":"","url":"https://api.github.com/users/nateirwin","html_url":"https://github.com/nateirwin","followers_url":"https://api.github.com/users/nateirwin/followers","following_url":"https://api.github.com/users/nateirwin/following{/other_user}","gists_url":"https://api.github.com/users/nateirwin/gists{/gist_id}","starred_url":"https://api.github.com/users/nateirwin/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/nateirwin/subscriptions","organizations_url":"https://api.github.com/users/nateirwin/orgs","repos_url":"https://api.github.com/users/nateirwin/repos","events_url":"https://api.github.com/users/nateirwin/events{/privacy}","received_events_url":"https://api.github.com/users/nateirwin/received_events","type":"User","site_admin":false},"created_at":"2016-02-13T18:18:14Z","updated_at":"2016-02-13T18:18:14Z","body":"This all sounds good to me.\r\n\r\nTo make sure I'm clear: CartoDB would basically just be the `parks` table plus the polygon from the `parks_poly` table - if it exists?"}}