- Hierarchical data is a common relational data pattern for representing tree-like data structures, such as an organizational structure, a project breakdown list, or even a family tree.
- In relational database model, One thing to remember is that flexibility in keys.
- We have the flexibility to design the database, it might be worth having multiple geometry types for certain data types.
- For example, having a city represented as a polygon would be useful to perform spatial queries to determine all points of interest within that city.
- At the same time, it is oftentimes more desirable to represent a city as a point on a map. The database allows you to do this by separating the attribute data from the features into their own table, and linking them together with a Primary Key/Foreign Key structure.
- A Primary Key is an identifier for a record in a table.
- A Foreign Key is a reference to that same identifier in a different table, thus linking them together.
CREATE DATABASE testDB:
- We want to includes this tables to the database:
- Continent – Fields – Geom (Polygon), ID(Primary Key), Name, Etc
Country – Fields – Geom (Polygon), ID, Name, Continent ID(Foreign Key)
- If there are different levels of regions within a country, then we would have them as different layers.
- So, if a country has regions called districts, and a number of districts combine to form a state, which then combine to form the country, you would have a layer for each type.
- Reg_State – Fields – Geom (Polygon), ID (Primary Key), Name, Country ID, Etc
- Have a table that contains attributes for each city, but no geometry.
- Have geometry tables with different geometries, like point or polygon, that link to the main city table.
- City_Info – Fields – CityID (Primary key), Name,Reg_District ID (Foreign Key), Etc,
City_Pt – Fields – Geom (Point), ID, Name, CityID (Foreign Key)
City_Poly – Fields – Geom (Polygon), ID, Name, CityID (Foreign Key)
This is the one of the ways of setting up the database, again it depends on your specific needs for being able to link different pieces of data together.
- Once the table structure is determined, the next part will be to set up queries to retrieve the information you want, for inclusion in a report or some sort of viewing application.