1

I'm creating a PostGIS database which will hold thousands of different shapes (imported from Shapefile, GeoJSON, etc), and they'll stay there and be queried over time.

  • Do I need to create a different table for each file that's imported?

It seems a bit weird that each "file" should have it's own separate table, but this is perhaps natural?

The data is thousands of different geojson- or Shapefiles, with different properties/columns. We plan to do all kinds of queries on the data, but mostly on one file (which translates to a few rows) at a time.

Improve this question
6
  • 2
    This could have become really interesting question, it's a pity it was put on hold.
    – Michal Zimmermann
    Jun 30, 2015 at 13:49
  • Thanks. I edited it down to one question only, I don't know if someone will "unhold" it for me. Personally I don't understand why it's so important to close down questions five minutes after they've been asked, even if they are a bit broad. I'm part of this community also, and I sincerely need answers to this question, and this is the best forum for it.
    – knutole
    Jun 30, 2015 at 14:30
  • 6
    To get useful answers, it would be necessary to include details about the data you are planning to store, e.g. do all those files have the same attributes or are they all different? what kind of queries are you expecting? currently the only answer is: "it depends"
    – underdark ♦
    Jun 30, 2015 at 20:36
  • There's nothing "natural" about storing all your data in one-row tables. The purpose of databases is to permit manipulation based on rows, in the context of a database design. If a relational design results in thousands of one-row tables, you should just uninstall PostgreSQL and use a different technology.
    – Vince
    Jul 1, 2015 at 3:29
  • 1
    @underdark The data is different - it has different columns. They all have a geom column, but otherwise different.
    – knutole
    Jul 1, 2015 at 10:27

1 Answer 1

Reset to default
1

I would approach this by creating a single table, or possibly a few tables if the separate files have some sort of obvious grouping, with 3 columns, a geometry column, a file column containing the filename, and an attributes column that contains the attributes associated with each feature.

Postgres has several datatypes that can store multiple values without a set schema: json, jsonb, and hstore. Any of these types would work for the attributes column, but if your using postgres 9.x jsonb is probably the way to go, as it is faster to query and can be indexed.

If you need to store information about each imported file you may also want a file table and have the file column in the other table reference the primary key from this table.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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