book

Pro Spatial with SQL Server 2012

by Alastair Aitchison

May 2012

Intermediate to advanced

560 pages

15h 52m

English

Apress

Read now

Unlock full access

What Is a Spatial Reference System?Modeling the EarthGeographic Coordinate SystemsProjected Coordinate SystemsPutting It All Together: Components of a Spatial Reference SystemSpatial Reference Identifiers (SRIDs)Well-Known Text of a Spatial Reference SystemContrasting a Geographic and a Projected Spatial ReferenceSummary

Geometry HierarchyInteriors, Exteriors, and BoundariesPointsLineStringsCircularStringsCompoundCurves PolygonsCurvePolygonsMultiPointsMultiLineStringsMultiPolygonsGeometryCollectionsFullGlobeEmpty GeometriesChoosing the Correct GeometrySummary
SQLCLR FoundationsThe geography DatatypeThe geometry DatatypeComparing geography and geometry DatatypesStorage RequirementsInternal Data StructureConverting Between DatatypesCreating Spatially Enabled TablesEnforcing a Common SRIDSummary
Creating Spatial Data from Well-Known TextCreating Spatial Data from Well-Known BinaryCreating Spatial Data from Geometry Markup LanguageCreating Spatial Data from Dynamically Generated WKTCreating Spatial Data Using the Builder ClassesSummary
Sources of Spatial DataImporting Tabular Spatial DataImporting Data Using OGR2OGRImporting ESRI Shapefile DataImporting MapInfo DataReprojecting Data During ImportExporting Spatial Data from SQL ServerSpatial ETL Using SQL Server Integration ServicesSummary
The Bing Maps Geocoding ServiceCreating a .NET Geocoding AssemblyCreating a Geocoding UDF Wrapper Compiling the AssemblyConfiguring the DatabaseImporting the AssemblyCreating the Geocode FunctionUsing the Geocode FunctionCreating a Geocoding TVF WrapperAsynchronous and Batch GeocodingReverse GeocodingSummary
PrecisionValidityHandling ErrorsSummary
Datum TransformationTransformation AlgorithmsTransforming to and from WGS84Projection, Unprojection, and ReprojectionPerforming Manual CalculationsCreating a Reusable Spatial Conversion LibraryRegistering the Transformation Assembly and FunctionsTransforming Spatial Data in T-SQLArchitecture and Design ConsiderationsSummary
Property and Method Syntax—A ReminderExamining the Type of Geometry Used to Represent a FeatureTesting for OGC PropertiesCounting the Number of Points in a GeometryReturning Individual Points from a GeometryDetermining the Center of a GeometryReturning Coordinate ValuesProperties Describing the Extent of a GeometryProperties Related to SurfacesProperties Related to GeometryCollectionsCalculating MetricsSetting or Retrieving the Spatial Reference Identifier (SRID)Summary
Simplifying a GeometryConverting Curves to LinesReorienting a geography PolygonDensificationBufferingCreating the Convex Hull of a GeometrySummary
Creating a Union of Two Items of Spatial DataAppending One Geometry onto AnotherSubtracting One Geometry from AnotherDetermining the Unique Parts of Two GeometriesDefining the Intersection Between Two GeometriesAggregating Columns of Spatial DataCombining Spatial Result SetsJoining Tables Using a Spatial ColumnSummary
Calculating the Distance Between Two GeometriesCalculating the Shortest Path Between Two GeometriesTesting for IntersectionIdentifying Specific Types of IntersectionDefining Custom Relationships Between GeometriesSummary
SQL Server's Spatial Histogram ProceduresCreating a Customized Distribution Analysisk-Means ClusteringSummary
Graph TheoryModeling a Street NetworkBrute-Force Routing in T-SQLA*: A More Efficient Routing Algorithm?The Traveling Salesman ProblemHarnessing the Bing Maps Routing ServiceSummary
The Importance of TrianglesTriangulationAn SQLCLR Triangulation ProcedureCreating 3D Surface ModelsCreating Alpha Shapes (Concave Hulls)Voronoi TessellationsSummary
The SSMS Spatial Results TabThematic Mapping with Google EarthCreating a Web Map Interface with Bing MapsSummary
Creating a Simple Report MapCustomizing the Map ViewportAdding an Analytic DatasetApplying a Styling RuleAdding a Bing Maps Tile LayerAssigning Actions to Map ElementsLimitations of the SSRS Map ControlSummary
The Need for a Spatial IndexHow Does a Spatial Index Work?Creating a Spatial Index in T-SQLAnalysing How the Index Is Used Creating a geography IndexDesigning Queries to Use a Spatial IndexOptimizing an IndexPerformance ComparisonSummary

Content preview from Pro Spatial with SQL Server 2012

A P P E N D I X

Appendix

This appendix contains various tables of reference information that you might find helpful when working with spatial data in SQL Server, as follows:

Methods available for the geometry and geography datatypes

Exception codes and descriptions

IsValidDetailed() response codes and descriptions

Common SRIDs