Acquiring ArcGIS-like speed in Postgis
I have been using PostGIS 2.0 for all of my GIS operations. PostGIS is an excellent GIS database, but no matter how I format my queries or design my tables I still have queries that take many hours or days to run. These same operations, when carried out in ArcGIS would take orders of magnitude less time to run comparatively. It is very depressing and stressful to just sit there and wait hours for simple queries to complete when you know they could be completed in much less time and with fewer chances for error. ArcGIS itself is not an option because I don't just have 75,000 dollars to spare for a windows-only program with strict restrictions and an ancient version of python.
Consider a simple point in polygon operation on a parcel dataset of a few hundred thousand parcels with a few hundred thousand points. This can take 45 minutes to an hour (with everything correctly indexed and using st_intersects); whereas in ArcGIS it would take less than a minute.
I have been told that this is because postgis is meant to handle operations on individual features rather than layers of features. In my experience though, QGIS does not perform operations much faster than in postgis and is much more subject to random crashes and generally seems unstable. The other thing that bothers me is that I have 3 local machines, each with an i7 processor and 8-16 GB of ram just sitting around doing nothing when they really are needed for helping out with GIS processing --but nothing I have at the moment can utilize them and I seriously doubt for that matter that my system even uses my single machine efficiently for these purposes.
I am very interested in clusters and distributed map reduce for GIS with NoSQL --from what I can tell it seems like these technologies are probably what I need to build anything that is significantly faster than what is out there. The problem is I really have almost no experience in these areas and am a very new programmer with only a working knowledge of python sql and a little java. Furthermore, the only GIS programs I have ever used are postgis, QGIS and ArcGIS.
The various options seem infinite so I am looking to the GIS community for some direction on where a beginner with no networking experience like myself should start and what technologies I should use to build a faster --or improve an existing-- GIS platform.
My requirements are as follows:
- Significantly faster than postgis for raw data processing on large datasets.
- Is supported in that it uses standard modules like GEOS or JTS, mongoDB etc. --I don't really want to build complex mathematical/geometric algorithms on my own as I lack the background to do this (at present, although I am slowly making an effort to get better at such things). In other words I want to use software that isn't extremely obscure and has a vibrant user/dev community.
- Works on Ubuntu server 12.04+ (I suppose debian would be fine too).
- Uses my unused machines on a local network. --This is not strictly a requirement as ArcGIS doesn't use multiple machines to get blazing speed; however, since they are just sitting there I might as well use them for something if at all possible.
I anticipate that with any of these options I will have the following two questions based on my research:
- How do I store geometries in a standard way that I can easily convert to postgis or shapefiles.
- How do I create an efficient index on the data.
EDIT: Due to all of the follow up questions in the comments being about things I could have possibly done wrong in postGIS, I have posted a question that more specially addresses the problems I have with postgis, so that this question can specially refer to what it would take to create a better platform