Mixing geometry types in one PostGIS table

If you don't need third party support and don't forsee the need to query by type keeping them in the same table works just fine. Alternatively you could use an inheritance model as discussed in chapter 3 of PostGIS in Action.

http://www.postgis.us/chapter_03_edition_1

From an architecture perspective PostGIS doesn't really care if in a query multiple different types are used. If it performed fine for you in Oracle it will be just as if not better performant in PostGIS.

There are 2 reasons to split it up (and either can be done later as needed): 1) Prevent people from inserting different types you don't want like geometry collections, circular strings and what not (which you could just manually define a constraint)

2) If you have a billion points and 1000 polygons, and do a lot of point in polygon tests, the speed is much better if when you query and do your join -- its against a billion -- to 1000 record table as opposed to a billion to billion record table. This would be the case for any spatial database I think (not specific to PostGIS). It's true for all relational queries I would guess too (not specific to spatial queries).

This one really troubles me. I guess it's because I've seen too many CAD files with data all on one layer, differentiated only by color.

What it comes down to is really a choice between organizing the data by structure, or by attribute.

Given that choice, I would always go for organizing my data through data structure.

For a start, when processing data you have one less hoop to jump through (eg select a,b,c from table where id=X as opposed to select a,b,c from table where id=X AND lid = Y)

Then, consider why databases allow multiple tables - if a data format offers particular data structures you have to think they will process data more efficiently if you use them.

But the big issue (for me) is when you want to move the data out and into another system. Then I think it becomes a real challenge, because the end application might not use data in the same way. I've seen so many people come unstuck in this scenario.

So - in my experience - you'll be able to use and transfer data twice as efficiently when it has a decent (deeper and more structured) data model.