5

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?)
Community
  • 1
brnt
  • 221
  • 1
  • 2
  • 5

2 Answers2

1

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.

Community
  • 1
RyanKDalton
  • 23,068
  • 17
  • 110
  • 178
  • 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. http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/Connecting_to_a_database/00s500000037000000/ – RyanKDalton 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
1

For the record:

  • Enterprise geodatabase libraries were available for both 32-bit and 64-bit servers at 10.0, and have shipped exclusively 64-bit since ArcGIS 10.1. There has never been a "32-bit bottleneck" in ArcSDE client applications (as of the time of the original post, the increased 64-bit instruction size actually slowed performance, though modern CPUs have made up the difference to break even).
  • There is rarely an advantage to partitioning spatial and non-spatial data. It is always slower to join tables which should never have been split by a well-considered database design.
  • There was no 32-bit limitation in ArcSDE -- A database client only uses one row at a time (though the API is usually transmitting more rows than that in the buffer at one time, it is in no danger of exceeding the process space of a 32-bit application). ArcGIS Desktop remains a 32-bit application, but ArcPy is supported in a 64-bit flavor (in the 64-bit Background Processing module), and ArcGIS Server has been exclusively 64-bit since ArcGIS 10.1. ArcGIS Pro is a 64-bit application as well.
  • If the System Design wiki site says there's no difference between using and not using an application server, it's because they tested it thirty different ways and didn't find a statistically significant difference. The point is moot, since application servers were deprecated at 10.2, and are not available at 10.3 and 10.4.
Vince
  • 20,017
  • 15
  • 45
  • 64