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

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

• I think there are a couple of clarifications we need. first the term "single file import" doesn't seem to make sense in the context. are you doing an actual import into a data set/database? or are you just using the csv to view the coordinates as a point? Also you state that most of the data is points without geometry. the point is geometry. so maybe you have a row of information that has no coordinates. ? I would like to respectably request that you look into some of these GIS options. If you don't have points or coordinates for HUGE data you will need to geocode for it to even show on a map. – Brad Nesom May 8 '14 at 19:39
• Really, what's huge? I've uses shapefiles with tens of thousands of record happily, fGDB with half a million records in one feature class no problem. A numeric scale on how big is big would help gauge whether a database has any benefit over folder/file based library. – Michael Stimson May 8 '14 at 22:38
• Thanks for your input, I have made some clarifications in the original post above about coordinates @BradNesom – andrelar May 9 '14 at 7:31
• And also made clarifications about the amount of data @MichaelMiles-Stimson :) – andrelar May 9 '14 at 7:32

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.

• "QGIS has to keep all data in memory, making operations rather slow." - That makes sense. It seems that I should try to go for the database solution. I have a PostGIS going already, so I haven't really investigated the SpatialLite option. You say that it "overcomes shapefile limitations" - like 10 character limitation of field names (because this is one annoying issue I've discovered with shapefiles)? What are the drawbacks for SpatialLite when comparing with PostGIS, any idea? Thanks for all valuable input! – andrelar May 9 '14 at 10:56
• For the shapefile: Predefined accuracy for float data, 2GB-Limit. I have postgis (with OSM data) and spatialite running. Postgis has a lot of built-in geometry functions, but I have not yet utilized them. Spatial indices are a great benefit when you deal with huge data, but that works on both databases. It is a bit difficult for both to add custom CRS and datum shift outside of the EPSG codes, that one goes to the shapefile. – AndreJ May 9 '14 at 11:16

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.

• I would totally go the PostGis based on the volume of data, also the ability to have multiple databases.. like Survey, Field, Reference, etc.. helps keep your data in small bites and a significant portion of the hard work when displaying is done on the server side. It can be a lot of work setting it up but it will be worth it in the end. Try to put your data on a SSD if you can, performs at the same speed as RAID but a fraction of the cost. – Michael Stimson May 9 '14 at 9:11
• Thanks, I've managed to get a PostGIS database going, and it seems to work well. – andrelar May 9 '14 at 10:44
• Yes, that seems to be a good reason to use PostGIS for the future development at least @MichaelMiles-Stimson – andrelar May 9 '14 at 10:46