Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have ~1000 fixed points representing data collection stations with multiple sensors, and a csv that is ~4.5GB of values in the format:

id     pointid  sensor  measdatetime      measvalue
50345  500      tempa   1/1/2011 13:55    72.81
50346  501      tempb   10/31/2011 14:00  75.65
55457  500      gaght   10/31/2011 01:00  6.56

(The data is similar to USGS stream gauges, but from a different network and different format.)

I am somewhat familiar with geodatabase design, but have little experience with the capabilities of the different DBMS's that work with ArcGIS. I'm considering using a variation of the Marine Data Model for the geodatabase design, but am a bit fuzzy about the DBMS ArcSDE Architecture I should use.

It seems that hardware wise, I'll want a dedicated machine for DBMS and SDE with a direct gigabit lan connection to a desktop machine running ArcInfo. For cost and "openness" sake, I'd like to use postgreSQL with postGIS (Followed by MS SQL Server Express, as I'm more familiar with T-SQL), but am not sure of the implications because of having such a large dataset. Also, from what I've researched, PostGRE has much better built in "windowing" for queries (eg. calculating a moving-window 2-week tempa average). If it makes a difference, I'd like to be able to load a given dataset entirely into RAM if I know I will be using it extensively for a specific work session. (Similar to This Helpful Answer)

To The Point:

  1. Is there a performance reduction by using PostGIS, since there is only a 32-bit version of SDE for it? Can I use 64-bit PostGRE, authorize it with 32-bit SDE, then use a direct connection to PostGRE to avoid a 32-bit "bottleneck"?
  2. Is there an advantage to keeping the bulk of the data "non-spatial", so I could have 64-bit PostGRE do the queries, then join the results to the points in ArcMap?
  3. Is there a better overall approach to managing a large amount of data using postGRE, considering ArcSDE's 32-bit limitation?

Other considerations:

  • I am using ArcInfo 10.0 with SP3, and have an EDN membership, so I can experiment fairly freely with minimal cost. I also have MS SQL Server 2008 R2 for developers, if needed.
  • The desktop machine will obviously need to be windows, but I can run windows or linux for the DBMS machine if there's an advantage.
  • There will be a number of static queries (eg 2-week moving average) that I may store the results of, but I would also like to be able to perform a specialized query quickly if needed. (eg. What is the minimum of the daily mean temps from stations 003, 007, and 104, betweem 1/1/2011 and 1/31/2011.)
  • I've read this guide, but find it to be mostly ambiguous and not useful for finding any pros or cons regarding specific scenarios (Lots of info, but little useful guidance. Am I really supposed to believe that there is NO difference in performance by using or not using the ArcSDE service?)
share|improve this question
    
I got a nice surprise when I clicked on your "Nice Helpful Answer". Thank you! :) –  Ragi Yaser Burhum Oct 27 '11 at 16:00
add comment

1 Answer

Do you already have ArcSDE for Postgres? Do you need it?

You may be able to keep all of your non-spatial and spatial in the PostGIS db, join the data in the DB, and pull them into arcmap as Query Layers in Arc10.

Alternatively, you might look into ST-Links (more discussion here and here) for connecting ArcMap to PostGIS directly.

Neither of these options will likely give you all of the functionality that ArcSDE has built in, but depending on your situation they may be viable alternatives.

share|improve this answer
1  
I have ArcSDE for PostGRES as part of the EDN Package, but not a production license (yet, if needed). Does using Query Layers require going through the SDE service? If not, I suppose that is a way to get around the 32-bit SDE for PostGRES bottleneck... –  brnt Oct 25 '11 at 18:34
    
I don't believe it needs ArcSDE at all. I think it is an ArcMap function. help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/… –  RyanDalton Oct 25 '11 at 18:40
    
That is apparently a BIG concept I was missing- I had assumed for no good reason that SDE was needed for a Query Layer. Good to know. Not sure if this should be an edit or comment, but where does a Query Layer stand in terms of performance vs SDE? –  brnt Oct 25 '11 at 23:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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