On 1/3/2013 10:36 AM, lynx86 wrote:
Hello I'm very new to GIS and have a question concerning the optimal design of the schema of my PostGIS geo database. The data are about traffic models and consist of geometric objects (roads, parkings, POIs, traffic sign positions) and related scalar and textual data (e.g. name/description of traffic sign, lane number of a road,...). At the moment, the data are partitioned into different tables depending on the traffic object which they describe (one table for roads, one for lanes, one for traffic signs). Each of the tables containes about 50000 to 100000 entries, but probably the number will grow in future.The goal is a more generic schema in which it's easier to - adjust positions of objects (e.g. roads, signs) - add new (geometric) traffic objects (e.g. public transport stations/lines, deviations, sensors) - add new metadata to existing traffic objects (e.g. data from a traffic census) - describe routes Main purpose of the database: - presentation of the geometric data via a GIS server and web application (e.g. Geoserver, OpenLayers) (I need fine-grained partition of the data into different layers, e.g. successive addition of different road types or traffic signs in the presentation map should be possible) - extraction of traffic data and conversion in data formats of simulation applications I got inspired by the schema of OSM, especially by the idea of simple geometric objects (points, ways, relations) which are tagged by key-value-pairs (metadata). As there are three different schemas (apidb, pgsnapshot, osm2pgsql), I wonder which of them is the most suitable to my requirements: - osm2pgsql allows easy presentation via Geoserver because it uses geometries, but extension of the metadata is difficult (no key-value-pairs) - pgsnapshot seems to be more suitable for data extraction but I'm not sure about the efficiency disadvantage of the hstore columns (e.g. change of one single value) - how can apidb and pgsnapshot tables be transformed into layers which can be displayed by OpenLayers (is there a efficiency disadvantage if I use views to convert the apidb data to displayable views?). Can you give me any hints about best practices?
This is totally my opinion and it is not meant to denigrate the good work of the OSM team and contributors.
The OSM schema is very simple with its key-value pairs but I find this simplicity a serious down-side for the following reasons:
1. there is very little standardization of keys and values - this makes it extremely hard to collect similar items.
2. While I can see why in the beginning this made it easy to get started, it lacks any formal definition of structure and relationships. For example how is a sign related to a road segment, if I need to split the segment in half, can I easily check to see if the sign is associated with it? can I easily re-associate it with the correct new segment.
3. polygons are not directly defined, but implied by collecting all edges that belong to a given boundary and then constructing a polygon from that. What does it mean if an edge was split in two and only one part is still assigned to the polygon or an edge is deleted for some unrelated reason. These implied relationships are not rigorous and will cause issues as entropy takes hold over time.
While it is more work up front, I think you would be better advised to think about your use cases and build a model that supports them. You could start with a list of distinct objects that you need to model and each of these becomes a table with attributes and geometry. If you need to add relationships between objects, then add little tables like:
obj1_to_obj2 table with columns obj1_id, obj2_id. These are very fast to use in table joins.
YMMV, but this is my 2 cents. -Steve _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
