Archive for the 'Spatial SQL' Category

Jun 25 2014

Open Simulator joins the SGeoS build — a strategy for blogging the builds

This is the first of what should be  a set of posts that detail a server build process for the San Francisco Enterprise Geographic Information Systems Program (SFGIS) Standard Geospatial Server (SGeoS).  In fact, the build work has been ongoing for several weeks and is concluding here, with OpenSim.

The motivation for including OpenSim in the platform was a desire to provide support for legacy .NET applications that may exist in various departments. In the interest of creating a Microsoft-neutral build that is framed with Open Source components, it was natural to bundle the Mono framework into the SGeoS design.  And while individual department applications are their own business and not part of the standard build, OpenSim serves as an excellent demonstration of the utility of the Mono framework as included on the server.  That , together with my perspective that immersive 3D clearly should be associated with geospatial servers, is why OpenSim is included in the Standard Geospatial Server.

OpenSim is not trivial by any means, and yet it is not such a resource hog that it would be infeasible to bundle it.  What’s more, it is an opportunity to distribute immersive 3D technology packaged with other geospatial capabilities.

Since the build descriptions are being transcribed from a build document that is approaching 80 pages on Google Docs, it seems prudent to break it up into individual modules.   And since WordPress here is configured to show older posts below newer ones—I’ll start down at the end modules and post new build descriptions for earlier modules in later days.

The original notion for SGeoS was to have modular build chapters that could provide a unit of capability.  That way, only selected modules need be configured.  After discussions with VMware engineers, I became intrigued by the notion of making a single server image that could run everything, all at once, and then disable unneeded featured in an actual deployment.  So the build document was initially structured with module-like chapters, but in fact the server builds them all—so it’s worth viewing the build document in sequence.

The modules will probably end up  numbering about 10, including packaging for production and possibly default-disabling of most items.   If one watches too closely, it might seem like I’m making a countdown to completion.  But this will end with a stub for deployment packaging, work back through an OpenSim build, and end up with imaging an install of CentOS 6.5 onto a new VM guest system.

No responses yet

Nov 03 2010

Getting OGC spatial tables back into ESRI ArcSDE on SQL Server 2008 R2

Published by under GIS in general,Spatial SQL

Sure, all the OGC-style spatial queries are great stuff, but when an organization already has very many feature classes in an ArcSDE geodatabase, there’s little appeal to making copies of reference data  for the sole sake of spatial queries.  Yet as of ArcSDE 9.3.1, when installed on MS SQL Server 2008 and 2008 R2, the option exists to bypass the classic SDE_BINARY format for geometry features, and instead use SQL Server’s native GEOMETRY format as the default format for new SDE features created in that geodatabase through ArcGIS.

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!

No responses yet

Oct 30 2010

Tied up in knots with Spatial SQL

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
Is really all it takes to return the mapped geologic unit beneath the point for every school in the county.
For 120 school points, and 3700 geology polygons, the query above returns its result in about 30 seconds, with no tuning of the spatial indices and no SQL hints provided (yet).
"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

No responses yet