spatially enabled databases

hello! i am wanting to know if folks can share insights and comments regarding spatially enabling an OLTP database. There are some developers who are contemplating having this installed on our informix oltp database which seems to me like it may run into issues in regards to affecting performance of the database and the web application that runs on it, etc. Currently, the idea is to spatially enable the warehouse data that is a pull of information from the oltp system but they are looking into the idea of doing this directly within the oltp database at a possible future date. Thanks in advance! Tom

Tom, Mixing data warehouse and OLTP is never a good idea, both have different performance goals. I have done a bit of work building spatial enabled data warehouses.... It can give you a whole different perspective on what you are trying to understand in your data. Adding the spatial data blade by itself does not add much overhead, adding spatial data (streets, images) that you join with your text data is where the benefit comes in and also where additional performance load happens. http://www.advancedatatools.com/Solutions/GeoEnabledSolutions.html Regards - Lester Tom Lehr wrote: > hello! > i am wanting to know if folks can share insights and comments > regarding spatially enabling an OLTP database. > > There are some developers who are contemplating having this installed > on our informix oltp database which seems to me like it may run into > issues in regards to affecting performance of the database and the web > application that runs on it, etc. > Currently, the idea is to spatially enable the warehouse data that is > a pull of information from the oltp system but they are looking into > the idea of doing this directly within the oltp database at a possible > future date. > Thanks in advance! Tom > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________

I have to agree with Lester (and not because he's my new boss ;-). Mixing OLTP and DW queries on the same server is almost always a bad idea. Ideally you should be running the DW server on a separate machine with separate storage, next best is a separate server instance on the same machine. If neither option is feasible, and you really have to share the database on disk, I would startup an SDS server and run all of the DW queries on that instance to keep the DW and OLTP caches separate. Hopefully the extra licensing cost of adding a MACH11 license will convince the powers that be to let you have a DW machine. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Feb 10, 2010 at 9:11 AM, Tom Lehr <tomcaml@gmail.com> wrote: > hello! > i am wanting to know if folks can share insights and comments > regarding spatially enabling an OLTP database. > > There are some developers who are contemplating having this installed > on our informix oltp database which seems to me like it may run into > issues in regards to affecting performance of the database and the web > application that runs on it, etc. > Currently, the idea is to spatially enable the warehouse data that is > a pull of information from the oltp system but they are looking into > the idea of doing this directly within the oltp database at a possible > future date. > Thanks in advance! Tom > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

I may have not been clear on what some folks are looking at here . We have a separate warehouse arena (it is db2 actually) - while the OLTP system is not apart of the warehouse and runs on Informix with a web front end. What some here have suggested is spatially enabling the informix oltp system rather than doing so in the warehouse. my concern was that spatially enabling the oltp system would cause enough overhead to be detrimental to the performance of the oltp system. does that make sense? thanks again

Tom, Yes, both Art and I were agreeing, spatially enabling the OLTP area to use it as a data warehouse would cause performance concerns. I my experience however, you will get better performance spatial enabling Informix then db2. And if you need the spatial for the OLTP, then got for it. I have supported OLTP systems that are spatially enabled. Thats how real time tracking and routing systems work. Like any other process you add to a system, it will take some more resources, but I would go for it if you need it. You can tune most Informix servers to gain a bit more performance to make up for adding process. Regards - Lester Tom Lehr wrote: > I may have not been clear on what some folks are looking at here . We > have a separate warehouse arena (it is db2 actually) - while the OLTP > system is not apart of the warehouse and runs on Informix with a web > front end. What some here have suggested is spatially enabling the > informix oltp system rather than doing so in the warehouse. my concern > was that spatially enabling the oltp system would cause enough > overhead to be detrimental to the performance of the oltp system. does > that make sense? > thanks again > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________

Tom, Call me slow... but what exactly do you mean when you say 'spatially enabling' your OLTP database? The short answer is that it depends on what you are doing and how you plan on using the spatial data. Its possible that you will have no impact other than the additional bytes stored in the row, or it could have larger performance impact if you're attempting to query against the data in a spatial query as part of your OLTP system. Full disclosure: This post represents my opinions only and does not reflect those of my client. (A company involved in spatial data) To give you an example... if you're geocoding a customer's address on an order entry system. You can expect to see a hit on inputting records because you need to find the lat,long,z of the address. How much of an overhead depends on your ability to get this information. If you're not using the geocoded information, you don't have any additional overhead, except for the obvious storage. If you are using the geocoded information, then depending on how you use the data, you will have additional overhead. How much will vary based on the use of the data, how it is indexed, etc ... I totally disagree with Art and Lester because there are a lot of reasons to geocode an OLTP database. Again, it all depends on the use case. I do agree with Art and Lester that IDS is much more efficient than Oracle and DB2 when it comes to extensibility and handling spatial data. But hey! What do I know? Its not like I'm dealing with spatial data on a daily basis for one of the world's largest supplier of spatial data. ;-) -G > From: tomcaml@gmail.com > Subject: spatially enabled databases > Date: Wed, 10 Feb 2010 06:11:59 -0800 > To: informix-list@iiug.org > > hello! > i am wanting to know if folks can share insights and comments > regarding spatially enabling an OLTP database. > > There are some developers who are contemplating having this installed > on our informix oltp database which seems to me like it may run into > issues in regards to affecting performance of the database and the web > application that runs on it, etc. > Currently, the idea is to spatially enable the warehouse data that is > a pull of information from the oltp system but they are looking into > the idea of doing this directly within the oltp database at a possible > future date. > Thanks in advance! Tom > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list _________________________________________________________________ Hotmail: Trusted email with powerful SPAM protection. http://clk.atdmt.com/GBL/go/201469227/direct/01/

thank you all for your posts. this would be used in the sense mentioned by Lester "Thats how real time tracking and routing systems work." we are a transportation (trucking) and logistics company. I would have to do exactly as you kind folks have done, which is pose these kind of specifics to the community pondering this idea. Regards -

one more twist to this thread - what do you think of these two postings? 1) Microsoft SQL Server 2008 has been uniquely designed to support spatial data. Overview Microsoft SQL Server 2008 delivers comprehensive spatial support that enables organizations to seamlessly consume, use, and extend location- based data through spatial-enabled applications which ultimately helps end users make better decisions. http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx 2) http://spatialnews.geocomm.com/community/lists/viewmessage.phtml?id=22912 The idea of a "spatially enabled" database is seriously obsolete architecture. I don't blame the vendors who would like to codify this approach, since after all they are only trying to protect their aging products against more modern competition. But, if you look at what is possible with modern technology it's clear that there are many approaches that provide all of the same technical benefits with much greater performance. If you are working with modern GIS software you can work with very high speed with any enterprise class database. In fact, the last thing you want is to have a handful of trivial DBMS "spatial" hacks standing in the way of intense, scalable geoprocessing performance. > The regular spatial database will allow you to store spatial data > in a vendor specific (closed) manner - generally as a BLOB > (optianally using a middleware such as SDE) while afore mentioned > spatial databases implement the open OGC standards: thus not > locking you down to a specific desktop GIS or middleware database > extension. > That's true, but the price you pay for using OGC is that it locks you down to an obsolete, slow and non-scalable architecture. The gain of going vendor-specific is often a hundred-fold or thousand-fold increase in performance, superior integration, dramatically expanded capabilities and reduced cost of ownership. A good example is the difference between Manifold's Enterprise Edition using standard Oracle (or SQL Server) as opposed to, say, an ESRI OGC client/middleware combination working with Oracle Spatial. If you have 100 users doing real geoprocessing with the OGC approach your performance will drop to unusuably slow levels. With Manifold, because it is a truly distributed and scalable solution, your geoprocessing performance with 100 users will be the same as with one user. Not only is Manifold 100 times faster, it is much less expensive, it provides many more capabilities and you have a better choice of DBMS vendors.

Tom, You really need to define your requirements in terms of number of users and performance. I have used SQL Server with spatial. For small offices with 4 or 5 users it will work. ESRI has a great system architecture sizing document that helps identify the limits of this approach. I also know a client trying to do a 10TB data warehouse on SQL server that gave it up. And I know of some large one's using Informix that are working well. I have not tested Manifold's Enterprise Edition so I cannot comment in it. But it looks like another form of middle ware like ESRI's ArcSDE.... Regards - Lester Tom Lehr wrote: > one more twist to this thread - what do you think of these two > postings? > > 1) > Microsoft SQL Server 2008 has been uniquely designed to support > spatial data. > > Overview > Microsoft SQL Server 2008 delivers comprehensive spatial support that > enables organizations to seamlessly consume, use, and extend location- > based data through spatial-enabled applications which ultimately helps > end users make better decisions. > > http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx > > 2) > http://spatialnews.geocomm.com/community/lists/viewmessage.phtml?id=22912 > > The idea of a "spatially enabled" database is seriously obsolete > architecture. I don't blame the vendors who would like to codify this > approach, since after all they are only trying to protect their aging > products against more modern competition. But, if you look at what is > possible with modern technology it's clear that there are many > approaches > that provide all of the same technical benefits with much greater > performance. > > If you are working with modern GIS software you can work with very > high > speed with any enterprise class database. In fact, the last thing you > want > is to have a handful of trivial DBMS "spatial" hacks standing in the > way of > intense, scalable geoprocessing performance. > >> The regular spatial database will allow you to store spatial data >> in a vendor specific (closed) manner - generally as a BLOB >> (optianally using a middleware such as SDE) while afore mentioned >> spatial databases implement the open OGC standards: thus not >> locking you down to a specific desktop GIS or middleware database >> extension. >> > > That's true, but the price you pay for using OGC is that it locks you > down > to an obsolete, slow and non-scalable architecture. The gain of going > vendor-specific is often a hundred-fold or thousand-fold increase in > performance, superior integration, dramatically expanded capabilities > and > reduced cost of ownership. > > A good example is the difference between Manifold's Enterprise Edition > using > standard Oracle (or SQL Server) as opposed to, say, an ESRI OGC > client/middleware combination working with Oracle Spatial. If you have > 100 > users doing real geoprocessing with the OGC approach your performance > will > drop to unusuably slow levels. With Manifold, because it is a truly > distributed and scalable solution, your geoprocessing performance with > 100 > users will be the same as with one user. Not only is Manifold 100 > times > faster, it is much less expensive, it provides many more capabilities > and > you have a better choice of DBMS vendors. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > -- ______________________________________________________________________ Lester Knutsen lester@advancedatatools.com Advanced DataTools Corporation Voice: 703-256-0267 x102 Visit our Web page: http://www.advancedatatools.com ______________________________________________________________________

Thanks again.

Tom, Post 1 is just Microsoft marketing fluff. Post 2... I have no clue what manifold is. With respect to scalable architectures that can handle geospatial data... What I would like to say is ... [REDACTED] ... (Sorry but I am legally limited to what I can say.) In general terms... There are some dot com companies that are working with alternative technologies with respect to location based services that do scale much better than traditional databases. The key issue is how much scale do you need? You're not talking Yahoo! or Google volumes of data. (Sorry Lester, but it was reported that back in 2007 Google was doing more than 30TB of net new data a month. God only knows how much they ingest now on a daily,weekly and monthly basis.) Yahoo! and others are using Hadoop(HBase,Hive & pig) to process massive amounts of data. The really sad thing is that if Informix had completed their project Arrowhead prior to the acquisition by IBM, the XPS/IDS hybrid would have been a good tool for this kind of work. That is not to say that traditional relational databases are not good or are not scalable to meet your specific needs. You can use the geospatial datablades in IDS and they will perform well enough to meet or exceed your stated needs. The issue is that accurate geocoding is probably going to be a large expense and more of a pain. The other pain is that when you query against a table, you can't use two indexes and you can't combine a geospatial index with another column. So if your subset of data is large enough, you end up doing a sequential scan. But that's a different issue and rumor has it that IDS will have this feature in a near term release. But hey! What do I know? Definitely more than I'm legally allowed to say. ;-) Maybe this time next year I can talk about parts of it. -G > From: tomcaml@gmail.com > Subject: Re: spatially enabled databases > Date: Wed, 10 Feb 2010 12:28:50 -0800 > To: informix-list@iiug.org > > one more twist to this thread - what do you think of these two > postings? > > 1) > Microsoft SQL Server 2008 has been uniquely designed to support > spatial data. > > Overview > Microsoft SQL Server 2008 delivers comprehensive spatial support that > enables organizations to seamlessly consume, use, and extend location- > based data through spatial-enabled applications which ultimately helps > end users make better decisions. > > http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx > > 2) > http://spatialnews.geocomm.com/community/lists/viewmessage.phtml?id=22912 > _________________________________________________________________ Your E-mail and More On-the-Go. Get Windows Live Hotmail Free. http://clk.atdmt.com/GBL/go/201469229/direct/01/