[postgis-users] Question about optimal PostGIS schema

[lynx86]

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?

Thanks!




--
View this message in context: http://postgis.17.n6.nabble.com/Question-about-optimal-PostGIS-schema-tp5002164.html
Sent from the PostGIS - User mailing list archive at Nabble.com.