Darb Dabney's 3d GIS – OpenSim

The CPU cycles are burning as I set up some test cache building for our local edition of ESRI Community Maps – Large-Scale Topographic Base Map.  It’s rather a treat to see a bunch of execution threads and trying my best to keep them all busy!

As I work through glitches here and there, I’m catching up on some of the good information that is online to help folks in my situation, like Advanced Map Caching from the 2010 ESRI Developer Conference.

Months ago I’d noticed some cool oblique aerials showing up in Google Maps in the vicinity of Mountain View.  So nice to imagine a world with Pictometry-type imagery that was not just in Bing Maps.

Well, today I saw that Marin county, and at least Berkeley, CA and Gettysburg, PA now have oblique imagery in Google Maps, somewhere between the vertical aerial scales and the StreetView Scales.  It’s great to see, but that’s not all I noticed.

As I poked around, I hovered near the Maps button and Traffic view, and out pops an Earth button.  Have I seen this before?  I tried it out and whoosh!  I got the Google Earth plugin going with no effort.  Almost made me glad to be using Windows ;^)

Google Earth view of Berkeley, with 3D Warehouse buildings—as part of Google Maps (in Windows)

The interface tweaks to Earth in Maps are brilliant IMHO.  There is a hyperbolic path that brings people in from a vertical view to something reasonably horizontal.  All the keyboard navigation controls are there, and the two buttons to modify look direction and look location are exquisite.  Props to the Earth plugin team!

It’s pheonomenal to have both excellent-quality obliques and seamless Earth integration show up all at once.  I hope that the folks at Cal GIS 2011 in Fresno find out about this in time to collectively digest it all.  Here at Marin, we’re recognizing a big jump in the importance of having decent building masses uploaded for public consumption.

Update:  Yes, I have realized that Google Maps Earth View was announced way back in 2010.04, nearly a year ago.  I guess that it’s a measure of how much time I’ve spent in Unbutu Linux or on Android that I haven’t realized it — because Earth View still hasn’t arrived in those platforms!

The enormously tragic megathrust event east of Honshu island in Japan has been in my thoughts throughout the weekend.   It is natural to focus on the human loss and images of civic destruction; I’ve got little to add to that story.

When I saw the first news items, it was already Friday morning and eight hours past the event.  My concerns were for Hawaii and the arrival of a tsunami.  I was shocked by the energy released, as reported at NEIC:  Mo=3.9×10^22 Nm,  Mw=9.0 — for as many earthquakes as have been recorded and studied in Japan, this was a much larger event.  For disaster planning, that is Not A Good Thing.

The beach ball diagram shows compression back up the thrust. Depth of event is 24km

While there was some concern for Hawaii, there is something to be said for the diffraction caused by the long line of seamounts extending northwesterly from the big island.  While the energy won’t be cancelled out, the height of the crest would be widened some.  By the time the tsunami reached California, it was a real concern and a literal wake-up call for emergency workers.  Our damage here was mostly an economic annoyance, with Del Norte county once again taking the brunt of the damage in Crescent City harbor.

With a check-in call to relatives in Hawaii saying everything OK, their eyes and mine turned towards Japan.  As I watched shocking videos of tsunami damage, I struggled to calibrate what I was seeing with my mental model of how fragile most human habitation structures are.  I’ve thought much about the effects of shaking, liquefaction, and occasionally about wildland fire.  I’ve read about losses in Marin county during 1982 flooding where debris flows destroyed houses or literally rolled them end-over-end down a slope.  I’d seen some videos from near Sumatra of debris flowing up streets after waves climbed up the beach.  Yet images from Japan recorded damage unfolding at an entirely worse scale.

My concern became much more engaged when I saw helicopter video of the south end of nuclear power reactor facility Fukushima Daiichi (No. 1).  You see, in the video clip I recognized not the reactor, but the cut slope behind it.  In a very strange sense of model-based deja vu, my memories were unequivocal: “I know that place!”.

Why?  Because about 17 or 18 years ago, I was fortunate to be part of a site design project for the (still yet to be constructed) Unit 7 and Unit 8 reactors.  Fortunate for me, because it was a first opportunity to learn not just ESRI Arc/INFO, but how to work with Bentley CAD software to create a 3D cut slope design.  My small contribution was to create a realization of slope that was  not simply faceted as a buffer surrounding the building footprints, but instead create a semblance of the natural cliff slopes  adjacent to the plant, while meeting the engineering requirements for not-to-exceed slope steepness, and a more natural-looking accumulation of drainage from the slope.

So apparently, after spending a couple of weeks learning to use 3D design software and creating a design that extended the existing cut farther southward, I had an image of the plant, its cliffs, and the breakwater that guided cooling water that stuck with me.  After having the flash of recognition with the video, I opened up Google Maps and found the site on my first inward zoom.  It was a bit spooky.

So now when I watch coverage of hydrogen venting that leads to building explosions, I feel a curious terrain-based sense of connection with the site.  I wish them well, and the safest possible return to production.  The TEPCO power is needed by many people.

And more ominous for the Pacific Northwest, I can’t help but reflect on what a similar megathrust event would mean for Cascadia.  Both Portland and Seattle would be in some sort of peril, although I don’t have a clear understanding of how tsunamis are modeled for either lower Columbia River or for Puget Sound.  But the possibility of a 9.0 megathrust event along the Cascadia subduction zone was a whole lot more abstract for me until last Friday.  It may not be the best time to reflect on it now while Japan is suffering—but the risk to the northwest has been a matter of public record for several years now and it not the time to forget that, either.  An event of that size and location would have tsunami implications both locally, and perhaps a far greater risk for windward Hawaii.

—————-<>—————-

Closer to home,  this past week we’ve created a prototype 3D product that provides a facsimile of our 45cm terrain model—imported to Google SketchUp 8 with a georeferenced orthophoto texture on the terrain.  Things are not fully tuned up yet, but we are able to take TIN and decimated TIN surfaces out of ArcGIS 10, by way of clipping polygons that are interpolated to multipatch (3D multi-polygon features) and then exported to Collada with a KML point for georeference.  The Collada can be imported to SketchUp, at least up so some limit of detail.  Once there, I’m trying now to find the right way to smooth the facets and improve the rendering of the surface without having high-contrast dark facets—because the orthophoto textures are arriving intact and draping over the surface.  Only at the moment, many black facets are covering up almost half of the orthophoto in an aggravating triangular patchwork.

Next step: smoothing within SketchUp.

And inspiration from others who have gotten gridded terrain into SketchUp.

As models of surface water flow for all Marin County are getting ready for review, there’s also a convergence with the large-scale topographic base (LSTB) map effort.

In fact, the hydrologically-enforced flow lines (HEFL) are getting their first chance to fit into the LSTB context.  This has helped immensely to clarify the uses that most folks will see the flow lines within.

Also, the Spring 2011 class in Spatial Analysis at College of Marin has gotten underway for three weeks now, and the students have turned out to be the best-qualified of any I’ve ever had the pleasure of teaching.

While cleaning up some web presence matters, I stumbled across this interview that relates to OpenSim and the sorts of themes that got this blog going in the first place.

The Android phone is getting some heavy use with its rooted install of Android 2.2/Froyo, in the form of Chromatic 4.5.  It feels like the G1 hardware is really near its limit, and I’m not anxious to move it too much farther forward.  It really is time to start looking where to move next—and Android will most likely be the foundation, but exactly what mix of GPS, compass, gryro, and connectivity will be there when I upgrade this summertime?  It’s a bit exciting to start tracking developments now to help make a better-informed choice when the time comes.

The Cr-48 has been amazingly fun.  It’s been moved to a home-built image of Chromium OS, and back, and forth, and back again.  It’s been made to work on T-Mobile network, although only at EDGE speeds.  It’s doing a very fine job of scrounging print resources with Google Cloud Print.  Hey, it’s even got its own blog going now, to save clutter here.

Keeping up with class work, and preparing for lecture and lab time each week, is taking time from writing, while at the same time providing so much more to write about!  Most likely it will all balance out by June when the semester is finished.

The ESRI Community Basemap program’s new ArcGIS 10 template for Large-Scale Topographic Mapping is somewhat streamlined and cleaner when compared to its ArcGIS 9.3.1. predecessor.  But the template map document still has the ability to bring my workstation to its knees when doing stuff like printing.  I find myself unable to print 11×17 or export a B-size PDF without having incomplete results.  Mercifully, I can print and export to A-size so people are starting to get a taste of what is to come when we start building cache tiles.  Everyone who has seen the base maps was quite pleased, and some were almost gushing over them.

Preparing input features to pour into the template geodatabase that  are good enough to be worthy of 1:1200 scale mapping is every bit the challenge it might seem.  Each feature class that is making its way into the template seems to require a unique bit of spatial analysis to get pulled together, and usually this involves combining our best available input data in ways that we haven’t before.  This week’s challenge has been water polygon and water line feature classes.  For utility, we’re choosing to split out the water polygon from its water line.  For lakes and ponds, they will coincide, but for our tidal coast, we’re conveying extra information by setting the polygon to represent Mean Low Water, and the water line to represent Mean High Water.  This helps to convey the widely varying slopes that Marin County has at the tidal shores, and provide some hint as to the width of the public access way below the high tide line.

These tidal shores have been compiled from the 25cm-interval topo contours that were generated for the Community Basemap, and in NAVD88 we used nominal 50cm contour for Mean Low Water, and 175cm contour for Mean High Water.  Between these elevations one often finds various “water” polygons depending on the application; in our base map we’ll have a purposeful gap.

Along the Sonoma borderlands I struggled for a few hours hand-tracing ponds using NAIP 2009 near-infrared grayscale—the one where standing water is basically black.  I was a bit sloppy and found myself tracing at 1:4000 on screen but it was a fairly thorough job through the adjacent shared watersheds along Estero Americano, Stemple Creek, San Antonio Creek, and Petaluma River.  I was just about fed up with drawing lines around standing water when I checked in with MarinMap member Bill Voigt of San Rafael for other reasons.  He provided an inspired suggestion that I use the water lines from our 2004 photogrammetry.  Of course!  I had used them in the terrain, but they were not hard constraints and the sparse contouring in west county lands tended to wash over stock ponds in the contours.

Empowered by Voigt’s suggestion, I found that the 2004 photogrammetric water line work, which was only available within Marin County proper, had very high fidelity with 2009 NAIP summer ponds, and it was vastly easier to select the pond-circumscribing lines, sometimes 300 segments at a time, copy and paste them into the Inland_Waters_li features, Merge them all at once into a single compound line, then Explode the multi-parts into discrete ponds.  With that approach I was able to harvest hundreds of Marin stock ponds, reservoirs, and vernal pools, and have geometry that appears smooth and accurate at better than 1:2400.  There are also quite a few meandering creek features in the west county that are well represented horizontally in the water break lines, but my impression of them was that they took a lot of upgrading to serve as 3D guides in the terrain, and construction rules broke the pond lines wherever a drainage reached its edge.  So there were a great many segments that needed to be merged to create closed loops that could build pond polygons.

By the end of this week, we had over 1400 water bodies identified in the watersheds that touch Marin county, and this exercise that was initially motivated by improved cartography may augment our set of candidate sites for wetland inventory.  These  water features are set into a regional water layer from The National Map that is valid at 1:24k and includes a refined coastline from Big Sur to north of Point Mendocino, and inland to the Sierra crest, from San Luis reservoir up to Mendocino Lake.

We refined Marin and adjacent shorelines (as both MHW and MLW boundaries) from Sears Point, up to and down from Petaluma, along San Pablo, San Rafael, Richardson and San Francisco Bays, the Golden Gate, Gulf of Farallones, Drakes Bay, outer Point Reyes, Tomales and Bodega Bays, and Bodega Head using 25cm interval topographic contours.  All Farallon Islands were traced near 1:2400 scale as visible in NAIP 2009 near infrared band.

Next week should find the last couple of administrative layers (Parks and Open Space) and refinements to road centerlines to reflect docks and proper arterial status, and then it will be time to start making test tiles.  With any luck, we’ll take some CPU cycles from production server to give us the best shot at getting good tiles fast.  With 120 layers, several of which use representations, and many of which use complex Maplex rules for labeling, the Community Basemap Large-Scale Topographic template for ArcGIS 10 is by far the most demanding map document I’ve ever manipulated.  Everyone is looking forward to getting the tiles generated for testing, and sending them off to ESRI for review while sending samples to MarinMap members for their comments as well.

Each year for the past 40 or so years, the American Geophysical Union has met in San Francisco around December for what is now the Fall Meeting.  I’ve been an AGU member for about 28 years, and for a time was attending each and every Fall meeting—but these days it’s about once every three years.

This was one of those years, and it was a great pleasure today running into a good handful of friends and former school colleagues from years past!  Also, it was much fun to present a poster that summarized an analysis of synthetic flow lines built on the integrated topographic-bathymetric surface model.  Basically, with a very detailed 3D surface grid that runs continuously from mountaintop to offshore out to the 3-nautical-mile legal boundary of California counties, it is possible to draw streams as they would have flowed when sea level was lower, like 7000 years ago.

Much of the interesting topography from those streams got clobbered by sea level rise.  As the Ice Age retreated and continental glaciers melted out, the waves from the Pacific Ocean pounded the coast back to where it is today, and planed off much of where the streams once ran.

With ArcHydro-style drainage analysis on our terrain model that has fused detailed multibeam bathymetry from the California Seafloor Mapping Project, it is possible to identify extremely subtle signatures in the portion of the offshore platform that is Santa Cruz mudstone formation, a harder Miocene formation that expresses bedding in its surface.

With the analysis, when synthetic drainage paths are symbolized to emphasize flow lines with greater catchment area one can observe suggestions of right-lateral offset.  In California, this is a signature pattern for tectonic offset of drainages that cross strike-slip faults with right-lateral offset.  Because the formation where the analysis has detected possible offset is older (Miocene is more than 5 million years old, but the offset is perhaps only in the last 1 million years), this result should not cause much excitement with regard to modern seismic hazard.  It could however prove helpful to those who would decode the geologic structure of the Point Reyes peninsula.

(comments on the Cr-48 have been moved to its own blog at http://cr.bq.bz

Many thanks to Singularity U, director Matt Rutherford, and to Randall Hand who brought it to my attention After chatting at SLCC 2009 this past summer, I appreciate the immediacy of this lecture.

It’s hard for me to listen to the entire talk and retain the best explanations – but clear and current they are.

Sounds great!  Less work!  And yet, frequently, it often doesn’t work because valid SDE geometry must still be made OGC-valid.  Three days ago I found myself with a working syntax to apply the (.NET CLR invocation of) MakeValid() method to my existing GIS data, and so I had valid OGC spatial tables available.  However, these OGC tables were missing a couple of things that needed to earn their registration back into the SDE fold.  Things needed, or just good-to-have when invoking sdelayer to register an OGC table include:

1. A defined primary key field with its own clustered index
2. OGC Spatial indexes (up to four levels available in OGC tables, and three in ArcSDE), with a name to pass along to SDE
3. A numeric expression of the geometry’s extent, as bounding rectangle limits
4. A pointer to the geometry’s projection “srid”  in the SDE_spatial_references table, linking it to real SRID as “auth_srid”
5. A friendly 63-character name to describe the spatial features
6. A known instance of ArcSDE, such as port ‘5151‘ or DirectConnect ‘sde:sqlserver:servername‘
7. A resolvable name or IP address for the ArcSDE server
8. The name of the ArcSDE database on the server
9. Login credentials for the database under the ArcSDE instance, most useful with CREATE privileges

One last detail: you’ll likely need to have the administrative parts of ArcSDE installed on your workstation.  This means installing ArcSDE 10, but not configuring it to run as a server locally.  Mercifully, this type of install does not require access to an ArcGIS Server license.  Of course, you’ll use it to perform actions a server instance where that license was required.  But after that install, your workstation should be able to run command line administrative tasks on a remote SDE server, often with either the ArcSDE monitor ‘sdemon’ or the Feature Class utilities of ‘sdelayer’, each of which has very many options.

1) Update the OGC table to ensure you have the correct SRID on every Geometry feature

UPDATE [SpatialTesting].user.PARCEL_OGC_LI
	SET Shape.STSrid = 2872

2) Define the primary key in your OGC table using SQL Server Management Studio (SSMS), and doing so will create a cluster index for you. In SSMS 2008 R2 this could mean a right-click on the OGC table name, selecting the Design view (second choice), then highlighting the OBJECTID attribute row by clicking its box along the left, right-click and choose Set Primary Key.  Save the change, or close the view and accept to save the change.  This should provide the “gold key” icon for OBJECTID as a Primary key, add an entry in the table’s Keys folder, and list an index named like “PK_your_table name  (Clustered)” in the Indexes folder.
Please note that you can’t skip this step and succeed in building a spatial index.   The spatial index appears to require a clustered index on a defined Primary Key.

3) Create the spatial indexes for the OGC table by right-clicking the table’s Indexes folder and choosing “New Index…” option.
With the General page chosen in “Select a page”, give the index a name (e.g. “spx_50″) and choose Index type: Spatial, then click “Add…”
In the “Select Columns from” popup, hopefully you will only  have one spatial column’s row to choose from (e.g. Shape).
Check its box and click OK.
Then in “Select a page”, choose Spatial, and here you will enter bounding coordinates for the OGC Geometry extents.
(e.g. 5816131, 2125631, 6028424, 2312384 as X-min, Y-min, X-max, Y-max)
For Geometry, ensure that the “Geometry grid” Tessellation Scheme remains chosen, and 16 Cells Per Object seems OK thus far.
While SQL Server 2008 offers four levels of spatial index grids, it only has qualitative labels for its options; by contrast ESRI file or SDE geodatabases have three grid levels, but one can specify precise index grid scales.
Given the prompts in the interface, I have typically understood Level 1 / top-level grid to be the coarsest, and chosen “Low”, then at Level 2 chosen “Medium”, and for both Level 3 and Level 4, chosen “High”.   Once you’ve chosen, click OK and wait while the index is built.

4) Once the spatial index has been built on the OGC spatial table, it should be ready to register with SDE and return it to ArcGIS service. The example below (less credentials) is what worked for us with line feature class with over 275,000 features.
Just open up a ‘CMD’ command-prompt window  and invoke the ‘sdelayer’ command with options along these lines:

sdelayer -o register -l PARCEL_OGC_LI,SHAPE -e l -t GEOMETRY -C OBJECTID,SDE
 -E 5816131,2125631,6028424,2312384 -R 2 -S "OGC Version of 2010 10 Parcels"
 -i sde:sqlserver:sqlgisdev -s SQLGISDEV -D SpatialTesting -u <user> -p <password>

And that should do it.

Another bit of syntax that I feel more comfortable with is making use of “UNION ALL” to chain queries together, so that they are easier to visualize in SSMS as overlays.

select Shape.STCentroid().STBuffer(10000) from SpatialTesting.bquinn.COUNTYBNDY_PG_OGC
UNION ALL
select Shape.STExteriorRing() from SpatialTesting.bquinn.COUNTYBNDY_PG_OGC

Takes our county boundary polygon, finds its centroid, buffers that centroid point out 10k feet, and then overlays that centroid blob on the outline of the county polygon, garnered from the poly’s exterior ring. Sounds simple enough, and the pasted code worked.  But 10 days ago I would have been very hard pressed to come up with that syntax!

The rules for geometry validation are just different between the ESRI world and the OGC world as instantiated by Microsoft in SQL Server 2008 R2.
I’ve been pounding my head against the wall over what I thought were the challenges of taking fine examples from Alastair Aitchison (a.k.a. tanoshimi on the MSDN forums) that are presented in Beginning Spatial with SQL Server 2008, and trying to make these happen in useful ways on real GIS data.  That is to say, data that were not created by hand-entry or even well-known-text (WKT) entry into SSMS, which are often  simple single features.

I was particularly concerned reading the first paragraph in Chapter 11: “All of the techniques discussed in this chapter apply only to a single item of geography or geometry data, and generally do not require any parameters.”  As I understand now, Mr. Aitchison meant “All of the techniques as discussed…” and all the various Spatio-Temporal methods like STIntersects() work perfectly well on perfectly valid spatial tables.

I thought that these methods just weren’t going to work on my GIS data,  typically large tables with lots of objects, not the scalar objects of the book examples.  But as it turns out, I had a different problem.  My geometry, as transcribed via Shapefiles and the Shape2SQL tool from SharpGIS.net, was arriving in a form that was occasionally invalid for certain features.  One invalid feature in a spatial table makes the entire SQL statement crash.  Errors were reported with reasons that did not help me find my actual problem very easily.  Bad geometry meant that I needed to clean it up by running the MakeValid() method.

After a couple of hours goofing around with T-SQL syntax, I’ve made my first baby steps toward cleaning up my stuff.  Here’s how that is happening:

(select ID, PARCEL, Prop_ID, Shape_area, Shape_len,
(CASE Shape.STIsValid() WHEN 1 THEN Shape ELSE Shape.MakeValid() END) as Shape
INTO spatial_01.dbo.Parcel2 FROM spatial_01.dbo.parcel)

As so often with code, it looks stupid-simple to see it written out in the way that actually works; a major “duh” factor.
The pay-off is that with 100% valid geometry, table vs. table spatial queries are now working.  I’ve not yet broken any speed records in query performance vs. ArcGIS 10, and yet I have finally proven to myself that I can use  Spatial SQL to do powerful geoprocessing that, in some cases, will be easier to write and maintain in SQL than in ArcGIS / Model Builder / Python.

For context, I’ve struggled through the past four days trying to figure out where my Spatial SQL syntax was messed up.  As it turned out, exactly ZERO of my uploaded spatial data tables arrived with 100% valid GEOMETRY data.  Anyway, once I made everything OGC-valid, a query like this

SELECT NAME, b.PType INTO spatial_01.dbo.School_Geology
FROM spatial_01.dbo.school2 a, spatial_01.dbo.geology2 b
WHERE a.Shape.STIntersects(b.Shape) = 1

"NAME","PType"
"TOMALES HIGH SCHOOL","Twg"
"BOLINAS-STINSON SCHOOL (STINSON)","Kfs"
"LAGUNA JOINT ELEM. SCHOOL","Qal"
"SAN GERONIMO VALLEY ELEM. SCHOOL","fsr"
"LAGUNITAS ELEM. SCHOOL","fsr"
"MARIN HORIZON SCHOOL","fsr"
"COUNTY COMMUNITY SCHOOL","Qal"
"ST RITA SCHOOL","Qal"
"ST PATRICK SCHOOL","fsr"
...

On a last note, this week I have validated the experience of using ESRI ArcSDE 9.3.1 settings to configure SDE (when running atop MS SQL Server 2008) to store its geometry in native OGC GEOMETRY binary form rather than the classic SDEBINARY form.  To do this, someone with db owner privileges can update the SDE_dbtune table with these directions to cause the GEOMETRY format to be used by default.  In principle, this should allow one to exploit the live feature classes in SDE for Spatial SQL queries—without the muss and fuss of exporting through Shapefiles and Shape2SQL (which is not intended for production-environment use, anyway), or need to code something in .NET to export a copy.

Wouldn’t it be nice to have ArcSDE running just like it always has, and then also be able to use Spatial SQL queries on the exact same feature classes that are being used throughout the enterprise?  Sounds great, in principle.  After tonight’s insight into the need to MakeValid() some perfectly functional feature classes, I do have some concerns that SDE feature classes could get broken by MakeValid().  On the other hand, it appears that one can take a Spatial SQL GEOMETRY-based table that meets SDE limitations: (1) only one geometry column, (2) all rows of the table must have the same geometry type, (3) all rows of the table must have the same Spatial Reference ID (SRID) defined—and any SDE feature class that was cleaned through MakeValid() should retain those characteristics—and then register the resulting spatial SQL table once again as an SDE feature class.  Hey, it sounds like it’s worth a try!

More working through Aitchison’s book has yielded many successful uses of OGC methods on our GIS data:

-- SELECT Shape.STGeometryType() FROM [spatial_01].[dbo].[road]
-- SELECT Shape.STGeometryType() FROM [spatial_01].[dbo].[Parcel]
-- SELECT Shape.STGeometryType() FROM [spatial_01].[dbo].[school_pt]
-- SELECT Shape.STGeometryType() FROM [spatial_01].[dbo].[geology]

-- SELECT Shape.STDimension() FROM [spatial_01].[dbo].[geology]
-- SELECT Shape.STDimension() FROM [spatial_01].[dbo].[road]
-- SELECT Shape.STDimension() FROM [spatial_01].[dbo].[school_pt]

-- SELECT Shape.InstanceOf('Surface') FROM [spatial_01].[dbo].[geology]
-- SELECT Shape.InstanceOf('Polygon') FROM [spatial_01].[dbo].[geology]
-- SELECT Shape.InstanceOf('Curve') FROM [spatial_01].[dbo].[road]
-- SELECT Shape.InstanceOf('LineString') FROM [spatial_01].[dbo].[road]
-- SELECT Shape.InstanceOf('Point') FROM [spatial_01].[dbo].[school_pt]

-- SELECT Shape.STIsSimple() FROM [spatial_01].[dbo].[road]
-- SELECT Shape.STIsSimple() FROM [spatial_01].[dbo].[BldgFoot]

-- SELECT Shape.STIsClosed() FROM [spatial_01].[dbo].[BldgFoot]

-- SELECT Shape.STIsRing() FROM [spatial_01].[dbo].[road]

-- SELECT Shape.STNumPoints() FROM [spatial_01].[dbo].[road]
-- SELECT Shape.STNumPoints() FROM [spatial_01].[dbo].[geology]
-- SELECT Shape.STNumPoints() FROM [spatial_01].[dbo].[school_pt]

-- Find Centroids and blob them out big
-- SELECT Shape.STCentroid().STBuffer(3500) FROM [spatial_01].[dbo].[City]

-- Calculate Perimeter Lengths and sort them out
-- SELECT Shape.STLength() as Shape_len FROM [spatial_01].[dbo].[City] ORDER BY Shape_len

-- Calculate City areas and sort them out
-- SELECT NAME, CAST (Shape.STArea()/1000000 AS INTEGER) as Shape_km2
--   FROM [spatial_01].[dbo].[City] ORDER BY Shape_len

-- Reading the SRID
-- SELECT Shape.STSrid FROM [spatial_01].[dbo].[City]

-- Setting the SRID
-- UPDATE [spatial_01].[dbo].[City] SET Shape.STSrid = 32610
-- SELECT Shape.STSrid FROM [spatial_01].[dbo].[City]

-- oops, that's wrong, let's set it back to 2768
-- UPDATE [spatial_01].[dbo].[City] SET Shape.STSrid = 2768
-- SELECT Shape.STSrid FROM [spatial_01].[dbo].[City]

-- counting elements
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[City]
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[school_pt]
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[geology]
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[road]
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[Parcel]
-- SELECT SUM(Shape.STNumGeometries()) FROM [spatial_01].[dbo].[BldgFoot]

-- Geometric Difference between cities and their own 3500-meter centroid blobs
-- SELECT Shape.STDifference(Shape.STCentroid().STBuffer(3500)) FROM [spatial_01].[dbo].[City]

-- Geometric Symmetric Difference between cities and their own 3500-meter centroid blobs
-- SELECT Shape.STSymDifference(Shape.STCentroid().STBuffer(3500)) FROM [spatial_01].[dbo].[City]

-- Creating 50-foot Buffer around roads
-- SELECT Shape.STBuffer(50) FROM [spatial_01].[dbo].[road]

-- Simplified Buffer around roads, tolerance 8 feet
-- SELECT Shape.BufferWithTolerance(50, 8, 'false') FROM [spatial_01].[dbo].[road]

-- Create convex hull around schools
--SELECT Shape.STConvexHull() FROM spatial_01.dbo.school_pt

-- buffer parcels
-- SELECT Shape.STBuffer(300) AS Shape INTO pcl_seed FROM parcel where Prop_ID = '001-001-01'
-- select * from pcl_seed  -- to verify the selection
-- SELECT Prop_ID, a.Shape FROM spatial_01.dbo.parcel a, spatial_01.dbo.pcl_seed b
--   WHERE a.Shape.STIntersects(b.Shape) = 1