Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It only takes a minute to sign up.
Sign up to join this community
I'm faced with the following problem. I have to migrate from Oracle database to PostgreSQL+PostGIS. Currently all geometries of all types are stored in one table and each record contains a "lid" field which indicates features of the same layer.
What are the pros and cons of using such a method? Should I break up data into multiple tables if I don't need to use the database with third-party software? What about performance of spatial queries, will the indexes help me?
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.
lid values.