Most optimal way to create a (spatial) database for big amounts of data?

Question

I am using QGIS, and on my way to set up a huge spatial database (geographical), where most of the data is points without geometry), along with other types of data (like lines in DXF and areas in SHP).

Until now I have been using single files for all data, but I begin to believe that this will be rather slow (rendering/querying/calculating) when adding more and more data (?). I have read about a few different methods (like this discussion: Database versus Files for Solo Developer). Unfortunately I haven't found any article that clearly states pros and cons when using "single file data import" compared with using a proper database instead.

At the moment, I am the only one going to use this database, but in the future it will probably be more people, and perhaps even we will need to "install" this database on other peoples computers (not sure how, yet).

I'm rather new to using PostGIS, but in my understanding, to use (for example) a PostGIS database I need to save all my data as Shape files, instead of point text files (CSV)? So this is also an extra step that will take some time (and I feel more comfortable and used to making calculations and changing data in a CSV-file using Excel).

1. My main question is: what the best way to build a database for this purpose?
2. A sub-question is: what are the pros and cons when comparing "single file data import" and a proper database (I plan to use a PostGIS database)?

Some clarifications: First of all, I haven't had much education in GIS engineering, so perhaps some of my expressions and technical terms are somewhat wrong...

"Are you just using the csv to view the coordinates as a point?" - Yes, this is what I wrongfully called "single file import" earlier, the point (coordinates) itself is of course geometry as well.

"Really, what's huge?" - Good question, it's in the eye of the beholder I guess :) In this case it will be a database with around 150 000 points separated into many different filter layers. As it is now, each filtered layer reads from the CSV file everytime it loads, and I experience that to be a rather slow process compared to reading from only one database (with shapefiles) instead (correct me if I'm wrong). Other than that there is only about 20 000 - 30 000 line/polyline objects (about 200 Mb data).

Answer 1

Raeding in CSV data is surely the slowest way to import data into QGIS. QGIS has to keep all data in memory, making operations rather slow.

A shapefile is a good improvement. In fact, it is already a database as well: the .dbf file is a database for attributes, stored in a very old dbase format. As a consequence, there are a few limitations in field name lengths, mixed geometry types and database size.

If Postgis is a number too large for you, think about using a spatialite database. It overcomes the shapefile limitations, but offers some SQL functions as well. And it is easily portable, if you want to use the data on different computers, share them with others using different operating systems, or make a quick backup.

You can copy the data between the different solutions inside QGIS, no extra importer is needed.

Answer 2

I think that using postgis is a great choice. As far as building a shapefile from your csv, you should be able to do that inside of QGIS. In QGIS you can import a csv as a layer. So then you should be able to export that csv to a shapefile with simply points and their attributes. There is a utility calls shp2pgsql that will create an SQL file that you can easily us to load postgis. And voila, you are ready to go. Postgis will give you the ability to do a lot of function that you currently do in QGIS. Also, I think that postgis is faster than QGIS for a number of tasks like sorting and queries.