Home

Database Schema

The PostGIS database for this project can be accessed directly with the following connection info:

• Host: database.codefornrv.org
• Database: blacksburg
• Schema: parks
• User: blacksburg_read
• Password: nrv

The following are the tables and brief description of the columns, though it is also self-documenting through in-database comments.

parks

• id integer NOT NULL, -- The internal ID of the park, as listed on the spreadsheet.
• osm_info json, -- JSON representation of the OpenStreetMap ID(s) and associate type used to identify the parks in OSM (and associate data from OSM). Example:

{
	"osm_info": [{
		"id": 123456,
		"type": "Way"
	}, {
		"id": 654321,
		"type": "Relation"
	}]
}

• park_name text, -- The official or most used name of the park.
• alternate_names json, -- A JSON representation (array) of alternative or other commonly used names for the park. For example, Blacksburg Municipal Park is sometimes referred to as Caboose Park. Example:

{
	"names": [{
		"name": "Caboose Park"
	}, {
		"name": "Other Park Name"
	}]
}

• description text, -- A description of the park.
• address text, -- The given address of the park, usually from an official website. Not all parks will have an official address.
• url text, -- The park's website.
• steward_id integer, -- The foreign key for the steward of the park, i.e. who is responsible for maintaining the park and who to contact with problems or questions.
• point_location geometry(Point,4326), -- A point representing the park for faster lookups and placement than the polygons found in geometry.
• geom geometry(MultiPolygon,4326) -- The PostGIS geometry of the park as imported from OpenStreetMap.

amenities

• id integer NOT NULL, -- The internal ID of the amenity.
• osm_info json, -- JSON representation of the OpenStreetMap ID(s) and associate type used to identify the amenities in OSM (and associate data from OSM). Example:

{
	"osm_info": [{
		"id": 123456,
		"type": "Way"
	}, {
		"id": 654321,
		"type": "Relation"
	}]
}

• park_id integer, -- The foreign key for the park in which the amenity is located
• amenity_type_id integer, -- The foreign key to the amenity_type table with the list of amenity types.
• description text, -- A description of the amenities.
• geom geometry(MultiPolygon,4326) -- The PostGIS geometry of the amenities as imported from OpenStreetMap.

amenity_type

• id integer NOT NULL, -- The internal ID of the amenity type.
• amenity_name text, -- The name of the amenity, e.g. Tennis Courts, Shelter, or Swimming Pool
• description text, -- A description of the amenity if necessary.

stewards

• id integer NOT NULL, -- The internal ID of the steward.
• steward_name text, -- The name of the steward, i.e. who is responsible for a park.
• url text, -- The website of the park steward.
• phone character varying(20), -- The main phone number of the steward.
• address character varying(200), -- The mailing address of the park steward.
• email character varying(200), -- The email address of the park steward.