 Developers
 Tutorials
 Analyze states and territories
Analyze states and territories

Join the conversation on Facebook

Join the conversation on Twitter

Subscribe to the YouTube Channel

Join the conversation on LinkedIn

View our projects on GitHub

Share via email
Natural Earth website is one of the most popular resources of open geodata.
One of these files – Admin 1
(States, provinces) administrative boundaries in scale 1:50m
– has been imported into "NATURAL_EARTH"."ne_50m_admin_1_states"
table, but only for the USA and Canada.
Again, geographical shapes of states and provinces are stored in the column "SHAPE"
with SRID = 1000004326
, i.e. planar projection.
Let’s take Florida as an example. What is a total length of the state’s borderline?
select "SHAPE".ST_SRID(4326).ST_Perimeter() as "Borderline"
from "NATURAL_EARTH"."ne_50m_admin_1_states"
where "iso_3166_2"='USFL';
What just happened?
 You used spatial method
ST_Perimeter()
to calculate the length of the perimeter of a multisurface. Before doing that you converted a shape from a flat plane (as it is stored using SRID
1000004326
) to a shape on the Round Earth model4326
to produce the most precise result. The result
3875857,373022288
is the length in meters, asmeter
is the default linear unit of measure defined forSRID
=4326
.
Let’s check if you can calculate the perimeter in miles. The table "PUBLIC"."ST_UNITS_OF_MEASURE"
stores definitions of spatial units of measures, and is a part of the geospatial content in SAP HANA.
select * from "PUBLIC"."ST_UNITS_OF_MEASURE"
where unit_name like '%mile%';
Let’s recalculate the borderline in US survey miles, but rounded.
select round("SHAPE".ST_SRID(4326).ST_Perimeter('US survey mile')) as "Borderline"
from "NATURAL_EARTH"."ne_50m_admin_1_states"
where "iso_3166_2"='USFL'
To calculate the area of the geography, you need to convert it to the planar projection. Unfortunately, every projection of Round Earth or its fragments on a flat surface is introducing some distortion.
select round("SHAPE".ST_Transform(3857).ST_Area('US survey mile')) as "Area"
from "NATURAL_EARTH"."ne_50m_admin_1_states" a
where "iso_3166_2"='USFL';
What just happened?
 SRS
3857
is the spatial reference system used by online web maps, like OpenStreetMap, Google Maps, Bing Maps etc. It is a projection for the whole globe. And because it is based on the so called Mercator projection, distances are significantly increasing the further from Equator the geography is located.
You need to use a projection with the least distortion for the area you are processing. Local projections are being used for higher precision, like SRS 3513
for Florida.
select round("SHAPE".ST_Transform(3513).ST_Area('US survey mile')) as "Area"
from "NATURAL_EARTH"."ne_50m_admin_1_states" a
where "iso_3166_2"='USFL';
Unfortunately, it is still not precise measurement, as the details of shapes loaded from the original file are rather rough. You can see it on the visualizations comparing complete state …
… to a zoomed area.
Let’s calculate a geometry that is a coastline of Florida. This example shows the use of a spatial set operations and aggregations.
with "FL_Boarder" as
(select "SHAPE".ST_Boundary() as "SHAPE"
from "NATURAL_EARTH"."ne_50m_admin_1_states" a
where "iso_3166_2"='USFL'),
"FL_LandBoarder" as
(select ST_unionAggr(b."SHAPE".ST_Intersection(c."SHAPE")) as "SHAPE"
from "NATURAL_EARTH"."ne_50m_admin_1_states" b
join "NATURAL_EARTH"."ne_50m_admin_1_states" c on 1=1
where 'USFL' <> c."iso_3166_2"
and b."iso_3166_2" = 'USFL')
select m."SHAPE".ST_Difference(n."SHAPE").st_asWKT()
from
"FL_Boarder" m,
"FL_LandBoarder" n;
The result is a collection of string lines …
… which visualized looks like this.
What just happened?
 You used
with
clause inSELECT
statement to specify an output of subqueries to be stored in two temporary result sets:"FL_Boarder"
and"FL_LandBoarder"
. For
"FL_Boarder"
you used aST_Boundary()
spatial method to return the boundary of the geometry representing the state of Florida. For
"FL_LandBoarder"
you used a spatial aggregationST_unionAggr()
to calculate the spatial union of all of the borders of Florida with other states on land. Same as in exercise with country borders you usedST_Intersection()
to calculate line strings representing those land boarders. At the end you used a spatial set method
ST_Difference()
to calculate a geometry that represents difference of two geometries above. The spatial difference between a total boundary of Florida and its borders on land gave you the coastline  exactly what we needed to calculate in this step.
In tables, you only have shapes of states and provinces. Can we calculate the Canada–United States border based on this? Sure, with the help of spatial aggregates and set operations again!
select ST_UnionAggr(us.shape.ST_Intersection(ca.shape)) as "Border"
from "NATURAL_EARTH"."ne_50m_admin_1_states" us
join "NATURAL_EARTH"."ne_50m_admin_1_states" ca
on 1=1
where us."iso_a2" in ('US') and ca."iso_a2" in ('CA');
And visualized.
What just happened?
 The query is similar to a subquery from previous step, it is just now you selected only states of the USA for one side of a join and only provinces of Canada for the other side.
 The spatial union aggregation of the intersections of these geometries in the join gave you the total land boarder between two countries.