7

Possible Duplicate:
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:

  1. Significantly faster than postgis for raw data processing on large datasets.
  2. 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.
  3. Works on Ubuntu server 12.04+ (I suppose debian would be fine too).
  4. 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:

  1. How do I store geometries in a standard way that I can easily convert to postgis or shapefiles.
  2. How do I create an efficient index on the data.

Thanks.

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

Improve this question
14
  • 11
    Although I have some thoughts about how to leverage MapReduce for doing geo-cluster computation, I am highly skeptical about some operation that is taking 45 minutes in PostGIS and less than a minute in ArcGIS. That just tells me that there is something that is being expressed very differently in PostGIS and that can be highly improved to match the speed of ArcGIS. Can you please elaborate with the exact query you are executing in PostGIS and what tool you are using in ArcGIS?
    – Ragi Yaser Burhum
    Commented Aug 4, 2012 at 4:47
  • 1
    I agree that sounds very strange.
    – Nathan W
    Commented Aug 4, 2012 at 4:52
  • 3
    I'm not sure I understand exactly what you are trying to do, but it looks like there is something badly wrong with the query or indexes for this to occur. You must be doing multiple full table scans for this to happen. Can you show the queries that you are running?
    – BradHards
    Commented Aug 4, 2012 at 7:21
  • 1
    I also agree that it seems unlikely that PostGIS should be slower than ArcGIS when it comes to point in polygon. PostGIS is absolutely not only optimized for single feature operations. Actually, the real benefits of using PostGIS use to show when doing complex nested calculations on bigger datasets. That is because the PostgreSQL query-planner in a majority of cases makes very good decisions about in what order to do things. In Posnt in POlygon example POstGIS also uses a prepared geoemetry algorithm.
    – Nicklas Avén
    Commented Aug 4, 2012 at 10:59
  • 1
    I am using Postigs 1.5.2 on Windows filled with Openstreetmap data by their original installer and it works quiet fast, compared to a self-made spatialite database. But it makes a great difference if the tables, queries and spatial indexes are not in the manner they are designed in the OSM toolchain. So I think your qgis or postgis is missing something.
    – AndreJ
    Commented Aug 4, 2012 at 16:44

0

Reset to default