Greetings everyone, I'm fairly new to PostgreSQL, but I'm currently doing some research ontools to be used for an upcoming project and could really use yourhelp with a possible database design. So the scenario is this. We have two projects starting that will dealheavily with mapping spatial regions. One of the reasons I'm lookingat PostgreSQL is the PostGIS extension that may help us in dealingwith all the geometry. The first project will be mapping the outsideenvironment. Think along the lines of Google Maps, but intended foruse by pedestrians rather than cars. The other project, one that I'llbe managing, will be doing the same thing but with interiors ofbuildings. For now, the two projects will be separate, but eventuallythe plan is to merge them and actually allow the system to plan pathsthat incorporate outside and interior maps. That's the project description in a nutshell. What I'm doing now istrying to figure out the best way to store all the spatialinformation. I want to have a foundational layer that is as simple aspossible, and that can later be extended for any special needs. The current plan is to do all the mapping via a graph structure usingnodes/vertices and edges. A node will be defined simply in terms of aGUID (UUID in PostgreSQL). Each node will have a unique id, but noother "mandatory" information. An edge is defined by the ids of itstwo end points. This setup will be common to both projects, and theuse of UUIDs will ensure that any database merges in the future willnot result in conflicts. Now here's the tricky bit. We have to be able to associate anarbitrary number of properties with each node and edge. So an exampleof a node property would be that node's position defined by alatitude/longitude pair. We will define all the possible propertiesbetween our two projects, but just because a property is valid,doesn't mean that it will be present in or be relevant to every nodein the system. For instance, nodes located inside of a building willlikely have their position defined by something other than latitudeand longitude, since capturing that data with a GPS will not bepossible. The simplest design would be to create two tables, one for nodesanother for edges, and create a column for every possible property.This, however, is huge waste of space, since there will not be asingle node or edge that will make use of all the defined properties.There may be hundreds of properties, but each node may use ten onaverage. That's the question - how do you represent this informationin the database in a space-efficient manner. We still have to be ableto search for specific nodes given their properties. For example –find all the nodes located within radius z of coordinate x,y. PostGISwill handle the special bit, but there has to be an efficient way ofaccessing this information/property for each node that has it. One other possibility I thought of was defining a master node tablethat would contain just the node ids. Then for each property I wouldcreate a separate table with the id column referencing the master.Here we're still wasting space for the extra storage of UUIDs, and ifI want to run a query that selects nodes with several differentproperties, it would require a long list of JOINs. I looked at table inheritance that PostgreSQL offers, but it turnedout to be something different from what I was hoping for. What I needis some sort of data inheritance based on a common unique key, but Idon't think that's possible. Same thing with partitioning. If therewas a way to partition the columns (with data) of a single largetable, then that would be an ideal solution. But in the absence ofthat feature, what design would you recommend? Thanks,Max ---------------------------(end of broadcast)---------------------------TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
