# Spatial SQL (2) - Calculating Geographic Properties

Posted by Sarah Battersby in Sarah Battersby's Blog on Nov 13, 2019 8:10:05 AMIn the ‘getting data into a spatial database’ post I included instructions for both PostgreSQL and SQL Server. At this point, however, I’m just going to focus on queries in PostgreSQL / PostGIS to keep things simple.

Note that much of the functionality that I discuss is also available in SQL Server, though the names of the functions, and the structure of the queries, are likely to be a bit different. Just Google it.

This post looks at a handful of common calculations that can be done on individual geographic features. You can use these in Initial SQL, Custom SQL, or RAWSQL queries in Tableau as part of your analytic process.

Helpful information about each of these queries in Tableau:

For demonstration purposes I’m going to use these datasets (attached as a zip file...and the coordinate reference system ID is in the file name when you unzip the attachment. Hopefully that will make it easier for you to get the data into your database to play with it in Tableau!):

- Fire stations (points)
- Nevada Department of Transportation districts (polygons)

## ST_Buffer

(https://postgis.net/docs/ST_Buffer.html)

(GEOMETRY or GEOGRAPHY)

What if we want to identify the area within 1km of any fire station? We can create a buffer:

ST_Buffer(geometry or geography, distance)

In Tableau as a RAWSQL calculation:

RAWSQL_SPATIAL(“ST_Buffer(%1::geography, 1000)”, [Geom]

**ST_Buffer**is the function in PostGIS**%1**is a place-holder for a field or parameter name used in the query. We add in the actual field name at the end of the RAWSQL calculation**%1::geography**is how we “cast” a geometry data type to a geography. This makes it so that the buffer is calculated with a spherical coordinate system and uses meters for the input distance. Otherwise, the calculation is done in a planar coordinate system. Since Tableau only works directly with spatial database data values that are already in a spherical coordinate system, if you are working with a geometry data type and you don’t cast it to a geography, your calculations will be done with units of decimal degrees. Just don’t do that. It’s a bad idea.**1000**is the distance to use in the buffer. ST_Buffer with geography data types use meters as the unit of measure. If you want to use something other than meters, do a conversion as part of the query (e.g., if you want to use feet, the query would look like this: ST_Buffer(%1::geography, 1000 * 3.28084)**[Geom]**is the name of the geometry field in our database as it appears in Tableau

Here's how the query looks in the calculated field editor:

In this case we’re casting our GEOMETRY type data (the geom field) into a GEOGRAPHY type so that we calculate the buffer using meters on the sphere.

Because this is a calculated field, we can swap out the hard-coded buffer distance with a parameter and have an adjustable distance buffer:

What happens if we work with GEOMETRY instead of GEOGRAPHY? Queries with GEOMETRY are calculated using planar coordinates. Recall that Tableau likes data in latitude and longitude, so the GEOMETRY calculations will be done using *decimal degrees* as the unit. Not a good choice…especially when it comes to things like area calculations (how big *is *a square decimal degree???)

Ugh. And I won’t even get into the projection issues with this. We can talk later.

## ST_Area

(https://postgis.net/docs/ST_Area.html)

(GEOMETRY or GEOGRAPHY)

What if we want the area of these (or any other) polygons?

It’s another easy query, but since this will return a numeric value instead of a geometry, we’ll use a RAWSQL_REAL() calculation:

RAWSQL_REAL (“ST_Area(%1)”, [Geom])

Since the buffer that we created is a GEOGRAPHY type (because we cast the GEOMETRY to GEOGRAPHY as part of the process), the results will be returned in square meters, so we might want to convert this back to KM to make it a nicer number to analyze…

## ST_Boundary

(https://postgis.net/docs/ST_Boundary.html)

(GEOMETRY)

What if we want the outline of a feature? ST_Boundary will return the outline. Note that it only works with GEOMETRY type, and not GEOGRAPHY. This could be a nice way to customize border color for polygons by using a dual axis map to combine it with polygon layer.

RAWSQL_SPATIAL(“ST_Boundary(%1)”, [Geom])

## ST_Centroid

(https://postgis.net/docs/ST_Centroid.html)

(GEOMETRY or GEOGRAPHY)

Maybe we only need the centroid for a polygon? That’s easy with ST_Centroid:

RAWSQL_SPATIAL(“ST_Centroid(%1)”, [Geom])

## ST_Envelope

(https://postgis.net/docs/ST_Envelope.html)

(GEOMETRY)

Maybe you need the bounding box? Here are the bounding boxes for the buffers around the fire stations:

RAWSQL_SPATIAL(“ST_Envelope(%1)”, [Geom])

## ST_Perimeter

(https://postgis.net/docs/ST_Perimeter.html)

(GEOMETRY or GEOGRAPHY)

Or maybe we want the perimeter of a polygon? Use ST_Perimeter() on a polygon. This will work with either GEOMETRY or GEOGRAPHY. Calculations on GEOGRAPHY will be done on the sphere and return values in meters, while calculations on GEOMETRY will be in the coordinate system used for the GEOMETRY...in this case the units are decimal degrees. The perimeter in decimal degrees makes no sense. Don’t do that. I only calculated it to show how silly it is.

RAWSQL_SPATIAL(“ST_Perimeter(%1)”, [Geom_Buffer])

## ST_X & ST_Y

(https://postgis.net/docs/ST_X.html and https://postgis.net/docs/ST_Y.html)

(GEOMETRY)

How about if we need the X and Y coordinates for a geographic feature? Easy:

RAWSQL_SPATIAL(“ST_X(%1)”, [Geom])

## ST_ConvexHull

(https://postgis.net/docs/ST_ConvexHull.html)

(GEOMETRY)

Minimum convex geometry to enclose all of the input geometries

Most of the other calculations covered in this post have been row-level. For a Convex or Concave Hull you often want to work with collections of geometry (e.g., the convex hull around all of the middle schools in an area) as opposed to at the row level (a convex hull around each individual point...which isn’t interesting, because the convex hull around a point is a point).

Because of this, unless we are already working with a multipoint, multipolygon, etc. we will need to use an *aggregated *RawSQL query and then define our level of detail / aggregation in the viz:

For instance, to generate the convex hull around ALL of the points (“collect” these together using ST_Collect first) we can use the aggreagted RawSQL_Spatial function like this:

RAWSQL_SPATIAL(“ST_ConvexHull(ST_Collect(%1))”, [Geom])

If we wanted to return multiple convex hulls around different groupings of points, we can just adjust the level of detail on the viz like this:

## ST_ConcaveHull

(https://postgis.net/docs/ST_ConcaveHull.html)

(GEOMETRY)

Concave geometry enclosing all of the input geometries. Can be ‘shrink wrapped’ to fit the input geometries more closely by adjusting the ‘target_percent’ input, though note that tighter fits will be much slower to compute.

RAWSQL_SPATIAL(“ST_ConcaveHull(ST_Collect(%1), 0.8)”, [Geom])

## ST_VoronoiPolygons

(https://postgis.net/docs/ST_VoronoiPolygons.html)

(GEOMETRY)

The Voronoi Polygon function returns a Geometry collection of polygons. As a RawSQL calculation, Tableau sees that returned geometry as one single feature with multiple polygons – like this, which might not be super helpful:

RAWSQL_SPATIAL(“ST_VoronoiPolygons(ST_Collect(%1))”, [Geom])

That’s okay if we just need a graphical representation of the polygon boundaries, but if we want to be able to interact with them individually we might want to either generate these as a pre-processing step (e.g., make the geometry and split out the individual polygons in a table in our database), or we can generate the polygons with custom SQL. This is a bit more complex –

- we have to group all of our points together (that’s the first part where we use ST_Collect to group all of the fire station points into a multipoint feature)
- Then we make the Voronoi polygons using that multipoint feature and use ST_Dump to then split the multipolygon with all of the Voronoi polygons back into individual polygons
- Then we make sure the polygons have appropriate attributes by joining the attributes from stations back in. We know which polygon gets which attributes by using ST_Intersects to match the polygon with one point location that the polygon was based on.

WITH points AS (

SELECT St_Collect(geom) as geom

FROM Las_vegas_fire_stations

),

Voronoi AS (

SELECT (St_Dump(St_VoronoiPolygons(points.geom))).geom::geometry(polygon, 4326) as geom_voronoi

FROM points

)

SELECT voronoi.*, stations.*

FROM voronoi

JOIN las_vegas_fire_stations as stations

ON St_Intersects(stations.geom, Voronoi.geom_voronoi)

## Comments