Skip to content

9.2 Spatial SQL

Geometry constructors

Geometry may be create by st_geometryfromtext() from WellKnowText (WKT) representation (see also st_astext() mentioned in the first part of this lesson). Examples:

select st_geometryfromtext('POINT(15 50)');
select st_geometryfromtext('LINESTRING(15 50, 16 51)');
select st_length(st_geometryfromtext('LINESTRING(15 50, 16 51)'));
select st_geometryfromtext('POLYGON((15 50, 16 50, 16 51, 15 51, 15 50))');
select st_area(st_geometryfromtext('POLYGON((15 50, 16 50, 16 51, 15 51, 15 50))'));
select st_geometryfromtext('POLYGON((15 50, 16 50, 16 51, 15 51, 15 50),
 (15.3 50.3, 15.6 50.3, 15.45 50.6, 15.3 50.3))');
select st_area(st_geometryfromtext('POLYGON((15 50, 16 50, 16 51, 15 51, 15 50),
 (15.3 50.3, 15.6 50.3, 15.45 50.6, 15.3 50.3))'));

Point geometry may be also created by st_point():

select st_point(15, 50);

Spatial relationships

Spatial predicates allows to describe various relationships between features. Take two geometries, a and b. Spatial predicates are defined as follows:

  • st_equals() Equals

Their interiors intersect and no part of the interior or boundary of one geometry intersects the exterior of the other. 

select st_equals(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
);
* st_disjoint() Disjoint

They have no point in common. 

select st_disjoint(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((2 0, 3 0, 3 1, 2 1, 2 0))')
);
* st_touches() Touches

They have at least one point in common but their interiors do not intersect. 

select st_touches(
st_geomfromtext('LINESTRING(0 0, 2 0)'),
st_geomfromtext('LINESTRING(1 0, 1 1)')
);
* st_intersects() Intersects Crosses

If the two geometries have at least one point in common. 

select st_intersects(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('POLYGON((0.5 0, 1.5 0, 1.5 1, 1.5 1, 0.5 0))')
);
* st_within() Within

If a lies in the interior of the b 

select st_within(
st_geomfromtext('LINESTRING(0.5 0.5, 1 1)'),
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
);
* st_contains() Contains

No points of b lie in the exterior of a and at least one point of the interior of b lies in the interior of a. 

select st_contains(
st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
st_geomfromtext('LINESTRING(0.5 0.5, 0.9 0.9)')
);
* st_crosses() Crosses Intersects

If they have some but not all interior points in common. 

select st_crosses(
st_geomfromtext('LINESTRING(0 0, 2 2)'),
st_geomfromtext('LINESTRING(2 0, 0 2)')
);

-- linestrings may cross only single point
select st_crosses(
st_geomfromtext('LINESTRING(0 0, 2 2)'),
st_geomfromtext('LINESTRING(0 0, 1 1, 2 0)')
);
* st_overlaps() Overlaps

They have some (but not all) points in common and have the same dimension and the intersection of the interiors of the two geometries has the same dimension as the geometries themselves.

select st_overlaps(
 st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
 st_geomfromtext('POLYGON((0.5 0, 1.5 0, 1.5 1, 0.5 1, 0.5 0))')
);

-- dimension mismatch
select st_overlaps(
 st_geomfromtext('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
 st_geomfromtext('LINESTRING(0.5 0.5, 1.5 0,5)')
);

Examples

Select water bodies overlapping Prague NUTS region:

with prague as 
(select st_transform(geom, 4326) as geom from nuts where CNTR_CODE = 'CZ' and name_latn = 'Praha')
select w.* from water_a as w
join prague as p
on st_intersects(w.geom, p.geom);

Note

You may be requested to initialize spatial metadata select InitSpatialMetadata(); since reprojection is perfomed in the statement above.

Select cinemas located in Prague:

with cinema as
(select * from pois where fclass = 'cinema')
select c.* from nuts as n
join cinema as c
on st_within(c.geom, st_transform(n.geom, 4326))
where CNTR_CODE = 'CZ' and name_latn = 'Praha';

Select all buildings where a cinema is located:

with cinema as
(select * from pois where fclass = 'cinema')
select * from buildings_a as b
join cinema as c
on st_contains(b.geom, c.geom) limit 10;

Spatial filters

Let's find pubs or restaurants around 15° meridian:

select * from pois where fclass in ('pub', 'restaurant') and st_x(geom) > 14.9 and st_x(geom) < 15.1

Let's find nearest cinema based on our current location (50.1042994N, 14.3886592E):

with cinema as
(select st_transform(geom, 3035) as geom, name from pois where fclass = 'cinema'),
aoi as
(select st_buffer(st_transform(setsrid(st_point(14.3886592, 50.1042994), 4326), 3035), 3000) as geom)
select c.* from cinema as c
join aoi
on st_within(c.geom, aoi.geom);

Spatial overlay

Select natural areas which are in Prague

1) First, find natural areas in Prague 

select l.fid, st_intersection(nuts.geom, st_transform(l.geom, 3035)) intersected_geom
from landuse_a as l, nuts
where fclass = 'nature_reserve'
and nuts.nuts_id = 'CZ010'
and st_intersects(nuts.geom, st_transform(l.geom, 3035)) = True;

2) If we try to display them, QGIS will get stuck. So, we first need to create table with spatial index. We will create the table 'natural_areas' using DB manager interface.

Create table using DB manager interface

3) Fill the table. 

insert into natural_areas
select l.fid,
        st_intersection(nuts.geom, st_transform(l.geom, 3035)) intersected_geom,
        l.name
from landuse_a as l, nuts
where fclass = 'nature_reserve'
and nuts.nuts_id = 'CZ010'
and st_intersects(nuts.geom, st_transform(l.geom, 3035)) = True;

4) We are interested only in natural areas where the name is filled. 

select * from natural_areas where name is not null;

5) We are now able to display the resulting geometry in QGIS.