24

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?

  • Which kind of "types" are you talking about? Is it POLYGON, LINE and POINTS? Or is it the types like "roads", "rivers", etc.? – Pablo Jun 26 '11 at 20:14
  • I mean types of geometries such as Polygons, Lines and Points. – drnextgis Jun 26 '11 at 23:55
24

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).

  • 1
    For the benefit of people coming back to this now: in PostGIS in Actions 2nd edition, this was moved to ch 14. – yeedle Aug 18 '17 at 18:09
11

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.

  • 1
    I agree with you in that the OP's scenario is arguably dirty (we don't know the backstory), but you're review of it is a little dramatic. It's not nearly the cataclysmic upheaval you describe it as. I don't care if it's for day-to-day use or for ETL into a new system/architecture, this whole thing can be simplified easily with a few views and a few proper indices, and these can be written in minutes...even if there are several unique lid values. – elrobis Nov 3 '16 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.