Spatial Views

ShaunConway · ‎06-02-2010

Hey Everyone,

Thanks in advance for any help. We are looking to take advantage of spatial views and I just wanted to see if anyone would have any helpful insight into whether or not this is an appropriate use.

We are running 9.3 on a SQL DB.

We have a parcel feature class which is updated daily. We also have a table (currently imported nightly) that is updated daily in our CAMA system. We currently run a nightly process that joins these two sources into a single feature class of parcel geometry and CAMA attribution. Would this be a good place to substitute a spatial view? We were considering building a spatial view with the spatial columns of the parcel geometry and the attribution of the CAMA table. We are hoping to eliminate both the building process of the new feature class and also the duplicate geometry and attribution storage this creates.

Some considerations we have taken into account are that we would like to, if possible, move to building this with the CAMA table from its original location (ie not importing it nightly). Another potential issue is that our parcel geometry data is versioned.

Any tips or advice would be great. Thanks!
Shaun

TedCronin · ‎06-02-2010

By chance are you using Sql 2008, or above? If so, wait until 10 ships in a few weeks and implement query layers with 2008 spatial, no sde required, thus would work with your CAMA.

Perhaps have you thought of using a pub box where your CAMA tables are located and where a child replica of your parcel database can be pushed to, so that spatial views can be built at that location, as part of the CAMA/Pub database.

We currently, have a production database, and a publication database, as well as County Access Database. Our Parcels are located in all, but for your response, we have replicas between Production and Publication, and Spatial Views between our Assessor Tables and Parcels on the Pub box. Currently our Assessor Tables function like your CAMA tables, so I think this will work for your needs. Replica synchronization can be scripted in python in 5 lines of code, and then as part of the same script a create view can be run using a bat file along with the subprocess module in python. This all then can be scheduled after hours, super easy. Definitely take a look at Query Layers at 10, coming soon...

ShaunConway · ‎06-03-2010

Thanks for your response. This all sounds great but .......... we aren't on SQL 2008 yet and it will most likely take us 12months or so to adopt 10. We have a lot of third party apps that lag a little behind the ESRI releases in terms of their interoperability.

Your design seems great though. I beleive it is something we have considered looking into down the road. Currently we are running our desktop/day-to-day GIS, internal web app, external web app and third party desktop apps (pictometry, proval) all off of the same production server and database. We just recently converted to ESRI from another vendor and our growth has been significant and it was hard to predict the usage from the start. The seperation of resources that you describe would be a great next step for us to take.

Thanks again riverside ... I'm sure I'll be posting again once we upgrade.

Shaun

TedCronin · ‎06-03-2010

Thanks for your response. This all sounds great but .......... we aren't on SQL 2008 yet and it will most likely take us 12months or so to adopt 10. We have a lot of third party apps that lag a little behind the ESRI releases in terms of their interoperability.

Your design seems great though. I beleive it is something we have considered looking into down the road. Currently we are running our desktop/day-to-day GIS, internal web app, external web app and third party desktop apps (pictometry, proval) all off of the same production server and database. We just recently converted to ESRI from another vendor and our growth has been significant and it was hard to predict the usage from the start. The seperation of resources that you describe would be a great next step for us to take.

Thanks again riverside ... I'm sure I'll be posting again once we upgrade.

Shaun

No worries, we actually are still on SQL Server 2005 SP3, have been running SQL Server 2008 R2 with ArcSDE 10 for about 10 months (Beta for both), but for the time being will be on 2005 till at least December, which is when our SDE will be upgraded to ArcSDE 10. ArcGIS 10 has a lot of nice things to wrap your head around especially stuff that may impact your setup, like GDB table simplification, new Database Replication options, and of course query layers.

Wow, all that stuff on the same server and database, It seems like a natural move would be to just spin off a new database on the same database server. Have fun with the upgrade.