Ok, so I guess this kind of a informal query/survey about how big a datasets you are using in your ESRI worlds...
I am building and maintaining a statewide dataset, where I have to process down to the individual house level, not parcel level but multiple mailing addresses per parcel for our systems. In many places I am using theoretical addresses calculated from street network or USPS AMS/AIS data. So my Address List is roughly 13.5 million addresses and growning monthly or quarterly.
Is anyone out there right now maintaining a live system of address/properly lookup information that is this large in a continuous dataset?
I would love to collaborate or talk more about how others are handling such a large dataset. I am seeing issues where ESRI software seems to be blowing up when I try to perform tasks such as intersects or spatial joins. ESRI says they don't see these kinds of issues but I have had these issues since back to 9.3.1 so I can't be the first/only person doing this since I can recreate it across multiple machines.
My Platform right now is ESRI ArcGIS 10 on the Desktop, talking to ArcSDE 9.3.1-sp1 on a SQL2008 backend using the GEOMETRY spatial object. So I am not doing anything really exotic; but still seems to me that in some areas I maybe am pushing the envelope.
[Further]
What I am interested in know is what are other people doing to optimize there processes for dealing with these datasets. I am going to be adding upwords of a million records a month going forward, and while Geocoding etc isn't a problem when you start running other processes and linking data for further analysis you start dealing with complex joins. Well, you output data from Intersects/Overlays/Identities using Only_FID and you get a thin middle table to join too; but when you start trying to divide and conquer the creation of that table you start to hit issues where you need to divide your source data into working areas but then you have repeating IDS that you can't merge back; so you are left with smaller blocks of data that you can't easily make whole again.
Thinking about options that break the data down to County-by-County scale, then using spatial views to join it back together etc... Just curious if other users are looking at the same kinds of problems on such a large scale but on small footprints.
-
360 million addresses geocoded in Oracle Spatial (11g) ArcSDE and Visualized in ArcGIS and Web App (Internal). It is not about the geocoded address but fuzzy (mis-matched addresses) this is a good guide scdhec.gov/gis/presentations/ESRI_Conference_08/tws/workshops/…– Mapperz ♦Commented May 27, 2011 at 1:52
-
I agree, the geocoding never has been the problem. My issue gets to being when you have such a large dataset that you need to have a continuos process that other processes become very difficult. Functions/Tasks like Intersects, Spatial-Joins etc, where you have to then join to other data in a highly normalized environment for modeling.– D.E.WrightCommented May 27, 2011 at 4:59
-
Is your spatial data indexed? According to the docs, SQL Server uses B-Tree indexes. Try loading the data into a PostGIS database with GIST indexes and compare performance. This will tell you if it's a SQL Server issue.– SeanCommented May 27, 2011 at 17:05
-
No problems with that kind of thing, but what I do see overall is that when you are dealing with so many points, and doing deep functions that run so long you look at ways to optimize them. And I am curious as to what other large scale users are doing.– D.E.WrightCommented May 27, 2011 at 19:22
-
If the question is that open-ended it should rephrased and made a community wiki.– SeanCommented May 27, 2011 at 19:30
|
Show 13 more comments
1 Answer
As it's an (old) open ended question I'll give you a open-ended answer:
Using the database properly can save massive amounts of time. The obvious way to do something isn't necessarily the fastest, for instance when I recently wanted to delete a lot of rows from Oracle, turns out that just sending: delete from TABLE1 where ID = 123 for each feature was incredibly slow and that there's some fancy Oracle stuff I can do to make it orders of magnitude faster.
So basically if you find a particular problem that's a bottleneck, ask a specific question relating to that bottleneck to the experts. So for the ArcGIS side that would probably be here (or the ESRI forums, or your ESRI support), but for a database-side issue (and things will usually be faster if you do them there) you'd want to ask at http://www.stackoverflow.com
-
Not so much open ended; but looking more for better theoretical ways to handle this topic. My most recent path had me building my own fuzzy-lookup logic to talk to my own SQL2008 DB. Removing the dependency on the ESRI engine to rely on well tuned index's to try and make this faster. Since we can't know enough about the internals of BING's or Google's engines we can only assume they would use there own fine-grained logic. Commented Apr 17, 2012 at 15:44
-
You can figure out quite a bit of the behind-the-scenes of Google from their research papers - research.google.com/pubs/papers.html Commented Apr 17, 2012 at 15:55