Paragon Corpoation PostGIS Spatial Database Engine The Open Source Geospatial Foundation UMN Mapserver Boston Geographic Information Systems   
FOSS4G International 2017, August 14th-18th 2017
   PostGreSQL Object Relational Database Management System
Home   About Boston GIS   Consulting Services  Boston GIS Blog  Postgres OnLine Journal  Planet PostGIS  PostGIS Funding

Purpose of BostonGIS

BostonGIS is a testbed for GIS and Web Mapping solutions utilizing open source, freely available and/or open gis technologies. We will be using mostly Boston, Massachusetts data to provide mapping and spatial database examples.

If you have some thoughts or comments on what you would like to see covered on this site, drop us a line on our Feed Back page.

PostGIS in Action
check out book and download the first chapter and SQL Primer for free.

Tips and Tricks for PostGIS

PostgreSQL: Up and Running 2nd Edition (Amazon)
PostgreSQL: Up and Running 2nd EditionIt covers PostgreSQL 9.2-9.4 with special focus

GIS Tutorials on Opensource and OpenGIS technologies Tutorials
GIS Article comments Article and Tutorial Comments
Boston GIS BLog Rss FeedBoston GIS blog

PDF HTML All BostonGIS tutorials packaged together in an E-Book.

Boston GIS Store


Tutorial and Tip Sites
Desktop GIS
External Data
GIS Events and Groups
GIS SDKs and Frameworks
External Resources
GIS Blogs Around Boston
External GIS Blogs
External Papers Articles
GIS Quick Guides and References
OpenStreetMap and OpenLayers Tutorials
PostGIS, pgRouting, and PostgreSQL Tutorials
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2) more ...
pgRouting: Loading OpenStreetMap with Osm2Po and route querying more ...
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.0) more ...
OSCON 2009: Tips and Tricks for Writing PostGIS Spatial Queries more ...
PGCon2009: PostGIS 1.4, PostgreSQL 8.4 Spatial Analysis Queries, Building Geometries, Open Jump more ...
PLR Part 3: PL/R and Geospatial Data Abstraction Library (GDAL) RGDAL more ...
PostGIS Nearest Neighbor: A Generic Solution - Much Faster than Previous Solution more ...
Solving the Nearest Neighbor Problem in PostGIS more ...
PLR Part 2: PL/R and PostGIS more ...
PLR Part 1: Up and Running with PL/R (PLR) in PostgreSQL: An almost Idiot's Guide more ...
Part 2 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Displaying the Maps more ... download
Part 1 - PostGIS and SharpMap in ASP.NET 2.0 using VB.NET: Compiling SharpMap with PostGIS more ...
Part 3: PostGIS Loading Data from Non-Spatial Sources more ...
Part 2: Introduction to Spatial Queries and SFSQL with PostGIS more ...
Miscellaneous Tutorials/Cheatsheets/Examples
SpatiaLite Tutorials
Boston External Map Examples
SQL Server 2008 Tutorials
Part 3: Getting Started With SQL Server 2008 Spatial: Spatial Aggregates and More more ...
Part 2: Getting Started With SQL Server 2008 Spatial: Reproject data and More Spatial Queries

Printer Friendly

Bringing in Towns As Geography (Geodetic) -- Continued

In the first part we covered bringing in Mass Towns data as Planar geometry, but were stuck because we need to transform the data to a degree based projection (in particular one listed in sys.spatial_reference_systems) to use the Geography data type, but SQL Server 2008 has no mechanism to transform that data. Now we shall demonstrate how to transform and import the data in a supported spatial reference system.

Transforming ESRI Shape from State Plane to WGS 84 long lat

As mentioned before SQL Server 2008 has no mechanism for doing spatial transformations, so what to do?

Using OGR to Transform the data

OGR/GDAL is a free Open Source GIS toolkit that is very useful for data loading and doing spatial transformations among other things. Its probably the easiest to use of all the tools.

  • Install it as described in our OGR2OGR Cheatsheet
  • Launch the FWTools Shell from Start->All Programs->FW Tools 2..->FW Tools Shell
  • CD into the directory you downloaded your data in my case cd C:\data\gisdata
  • Type the following. The below will take an ESRI shape file called TOWNSSURVEY_POLY.shp that is of Massachusetts State Plane Meters (EPSG:26986) and transforms it to WGS 84 long lat (EPSG:4326)

    ogr2ogr -f "ESRI Shapefile" -a_srs "EPSG:26986" -t_srs "EPSG:4326" towns_geodetic.shp TOWNSSURVEY_POLY.shp
  • Now launch Shape2SQL.exe repeat our import step except choose the new towns_geodetic.shp, but choose Geography instead and SRID 4326. Your screen should look: Load towns as Geography

Doing Queries with Geography (Geodetic)

Now we have our data imported, Launch SQLSpatial.exe as we did before and run these queries

The below fails because Geography does not support Centroid - get error STCentroid for type STGeography not found.

SELECT TOP 1 geom.STCentroid().STAsText() FROM towns_geodetic WHERE town = 'BOSTON'

So the first thing we learn from the above exercise, is that sometimes planar is still needed since while Geography can cover a larger region, it is lacking many of the functions available in the regular Geometry. Refer to SQL Server 2008 PostGIS MySQL Compare for a compare of function/method differences between SQL Server 2008 Geography and Geometry


Distance Searches

One important feature that Geography (Geodetic) has over Geometry is ability to do distances in meters using spherical coordinates and spanning large regions. In fact Isaac Kunen touches on this a little in his blog Nearest Neighbors. In fact doing distance queries and finding neighbors is probably the number one reason why most people will want to use spatial queries. With this one feature, one can answer questions such as

  • How many low income families are within x miles from this welfare office?
  • Correlation between outbreaks of cancer and location of a nuclear oil spill taking into consideration the full area of the oil spill?

Of course questions like these could be answered before, but are answered a bit more trivially with a spatially enabled database and are extremely difficult to answer if you are trying to find shortest distances between 2 objects that are not points.

Note we know the distance is in meters because the spatial_reference_systems table tells us so.

SELECT unit_of_measure from sys.spatial_reference_systems WHERE spatial_reference_id = 4326;

Most of the spatial refence systems defined in this sys table are in meters except for a few weird ones in Clarke's foot, Survey foot, and German metre.

Here we are going to run this in SQL Server 2008 Studio since we don't have any map data to view and we want to take advantage of SQL Server 2008 Studio show plan features. Keep in mind just as in all OGC compliant spatial databases, the STDistance function defines the minimum distance between 2 geometries. So if you are comparing a Polygon to a polygon then its the distance between the points on each polygon that is the closest.

Below is a slightly different query from what we used in planar and can be used equally in planar. Here we arbitrarily take the first point that defines a polygon in Boston and ask what town POLYGON/MULTIPOLYGON geometries are within 1 mile of this point and we also want to know the exact distances and results ordered by distance.

SELECT, ref.point1.STDistance(t.geom)/0.3048 As dist_ft
FROM towns_geodetic As t 
SELECT TOP 1 geom.STPointN(1) As point1
FROM towns_geodetic WHERE town = 'BOSTON') As ref
ON ref.point1.STDistance(t.geom) < 1609.344
ORDER BY ref.point1.STDistance(t.geom) ;

town	dist_ft
BOSTON	140.31019135227
BOSTON	211.728831986735
DEDHAM	2616.66222586371
DEDHAM	2616.73216967261
MILTON	3501.37051762325

Now try clicking the "Include Actual Execution Plan" (or Ctrl-M for short) View Execution Plan and hitting the Execute for the above query.

You should see something like this which will give you a rough idea of where your processing time is going.

Show plan

Shown above is a very small fragment of the plan used. From this we learn that our query is using a spatial index (this is good), but there is a warning on it, not so good. Usually when you see a little warning like that, it means your planner statistics are either non-existent or out of date. If you right click on it and view details, it will tell you more about the warning. This query is already lightning fast, so we won't worry about this minor issue. In our next part, we shall delve into larger sets of data with more sophisticated queries, where speed will be an issue and we'll want to squeeze out every inch of speed we can.

So I shall leave you with these words of wisdom as far as Query Plans go and these apply for most databases and not just spatial queries. We'll experiment with these rules of thumb in the next section.

  • Scan percentages and sniff out highest percentage costs and inspect those
  • Scan for lack of indexes in plans where you would expect indexes to be used. Note just because no index is used even when you have an index is not an absolute cause for concern. Sometimes it is faster to do a table scan than an index scan.
  • Scan for warning flags such as above

Post Comments About Part 2: Getting Started With SQL Server 2008 Spatial: Reproject data and More Spatial Queries
Part 1: Getting Started With SQL Server 2008 Spatial: An almost Idiot's Guide more ...
UMN Mapserver Tutorials
General Commentary
Locations of visitors to BostonGIS
Boston GIS      Copyright 2017      Paragon Corporation