03-Spatial function in PostGIS

Reference¶

• Introduction to PostGIS
• Using SQL with Geodatabases

Connecting to the database¶¶

In [2]:

import os

import os

In [1]:

%load_ext sql

%load_ext sql

In [3]:

host = "localhost"
database = "nyc"
user = os.getenv('SQL_USER')
password = os.getenv('SQL_PASSWORD')

connection_string = f"postgresql://{user}:{password}@{host}/{database}"
%sql $connection_string

host = "localhost" database = "nyc" user = os.getenv('SQL_USER') password = os.getenv('SQL_PASSWORD') connection_string = f"postgresql://{user}:{password}@{host}/{database}" %sql $connection_string

Out[3]:

'Connected: postgres@nyc'

A quick a way to check columns from a table

In [4]:

%%sql 

SELECT * FROM nyc_neighborhoods WHERE FALSE

%%sql SELECT * FROM nyc_neighborhoods WHERE FALSE

 * postgresql://postgres:***@localhost/nyc
0 rows affected.

Out[4]:

id	geom	boroname	name

In [5]:

%%sql 

SELECT id, boroname, name from nyc_neighborhoods LIMIT 10

%%sql SELECT id, boroname, name from nyc_neighborhoods LIMIT 10

 * postgresql://postgres:***@localhost/nyc
10 rows affected.

Out[5]:

id	boroname	name
1	Brooklyn	Bensonhurst
2	Manhattan	East Village
3	Manhattan	West Village
4	The Bronx	Throggs Neck
5	The Bronx	Wakefield-Williamsbridge
6	Queens	Auburndale
7	Manhattan	Battery Park
8	Manhattan	Carnegie Hill
9	Staten Island	Mariners Harbor
10	Staten Island	Rossville

Simple SQL¶

Check PostGIS version

In [6]:

%%sql

SELECT postgis_full_version()

%%sql SELECT postgis_full_version()

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[6]:

postgis_full_version
POSTGIS="3.4.1 3.4.1" [EXTENSION] PGSQL="150" GEOS="3.12.1-CAPI-1.18.1" PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT=https://cdn.proj.org USER_WRITABLE_DIRECTORY=C:\WINDOWS\ServiceProfiles\NetworkService\AppData\Local/proj DATABASE_PATH=C:\Program Files\PostgreSQL\15\share\contrib\postgis-3.4\proj\proj.db" LIBXML="2.9.14" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)"

Hands-ON NYC Project¶

NYC Neighborhoods¶

What are the names of all the neighborhoods in New York City?

In [8]:

%%sql

SELECT name FROM nyc_neighborhoods LIMIT 10

%%sql SELECT name FROM nyc_neighborhoods LIMIT 10

 * postgresql://postgres:***@localhost/nyc
10 rows affected.

Out[8]:

name
Bensonhurst
East Village
West Village
Throggs Neck
Wakefield-Williamsbridge
Auburndale
Battery Park
Carnegie Hill
Mariners Harbor
Rossville

What are the names of all the neighborhoods in Brooklyn?

In [10]:

%%sql

SELECT name
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn' LIMIT 10

%%sql SELECT name FROM nyc_neighborhoods WHERE boroname = 'Brooklyn' LIMIT 10

 * postgresql://postgres:***@localhost/nyc
10 rows affected.

Out[10]:

name
Bensonhurst
Bay Ridge
Boerum Hill
Cobble Hill
Downtown
Sunset Park
Borough Park
East Brooklyn
Flatbush
Park Slope

What is the number of letters in the names of all the neighborhoods in Brooklyn?

In [13]:

%%sql

SELECT char_length(name) AS name_length, COUNT(*) AS count
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn'
GROUP BY char_length(name)
ORDER BY name_length;

%%sql SELECT char_length(name) AS name_length, COUNT(*) AS count FROM nyc_neighborhoods WHERE boroname = 'Brooklyn' GROUP BY char_length(name) ORDER BY name_length;

 * postgresql://postgres:***@localhost/nyc
9 rows affected.

Out[13]:

name_length	count
8	5
9	2
10	2
11	5
12	3
13	2
15	1
18	2
24	1

What is the average number of letters and standard deviation of number of letters in the names of all the neighborhoods in Brooklyn?

In [14]:

%%sql

SELECT avg(char_length(name)) AS mean, stddev(char_length(name)) AS std
FROM nyc_neighborhoods
WHERE boroname = 'Brooklyn'

%%sql SELECT avg(char_length(name)) AS mean, stddev(char_length(name)) AS std FROM nyc_neighborhoods WHERE boroname = 'Brooklyn'

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[14]:

mean	std
11.7391304347826087	3.9105613559407395

What is the average number of letters in the names of all the neighborhoods in New York City, reported by borough?

In [15]:

%%sql

SELECT boroname, avg(char_length(name)) AS mean, stddev(char_length(name)) AS std
FROM nyc_neighborhoods
GROUP BY boroname

%%sql SELECT boroname, avg(char_length(name)) AS mean, stddev(char_length(name)) AS std FROM nyc_neighborhoods GROUP BY boroname

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[15]:

boroname	mean	std
Queens	11.6666666666666667	5.0057438272815975
Brooklyn	11.7391304347826087	3.9105613559407395
Staten Island	12.2916666666666667	5.2043390480959474
The Bronx	12.0416666666666667	3.6651017740975152
Manhattan	11.8214285714285714	4.3123729948325257

NYC Census Blocks¶

In [16]:

%%sql

SELECT * FROM nyc_census_blocks WHERE FALSE

%%sql SELECT * FROM nyc_census_blocks WHERE FALSE

 * postgresql://postgres:***@localhost/nyc
0 rows affected.

Out[16]:

id	geom	blkid	popn_total	popn_white	popn_black	popn_nativ	popn_asian	popn_other	boroname

What is the population of the City of New York?

In [17]:

%%sql 

SELECT Sum(popn_total) AS population
FROM nyc_census_blocks

%%sql SELECT Sum(popn_total) AS population FROM nyc_census_blocks

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[17]:

population
8175032

What is the population of the Bronx?

In [18]:

%%sql 

SELECT SUM(popn_total) AS population
FROM nyc_census_blocks
WHERE boroname = 'The Bronx'

%%sql SELECT SUM(popn_total) AS population FROM nyc_census_blocks WHERE boroname = 'The Bronx'

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[18]:

population
1385108

For each borough, what percentage of the population is white?

In [19]:

%%sql

SELECT boroname, 100 * SUM(popn_white) / SUM(popn_total) AS white_pct
From nyc_census_blocks
GROUP BY boroname

%%sql SELECT boroname, 100 * SUM(popn_white) / SUM(popn_total) AS white_pct From nyc_census_blocks GROUP BY boroname

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[19]:

boroname	white_pct
Queens	39.7220773945910130
Brooklyn	42.8011737932686549
The Bronx	27.9037446899447552
Manhattan	57.4493039480462811
Staten Island	72.8942034860154033

Working with Geometries¶

Creating geometries¶

• Built-in type of geometries in PostGIS

  

• View is the virtual table created by query

  

In [21]:

%%sql

CREATE TABLE geometries (name varchar, geom geometry);

INSERT INTO geometries VALUES
  ('Point', 'POINT(0 0)'),
  ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
  ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
  ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
  ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

%%sql CREATE TABLE geometries (name varchar, geom geometry); INSERT INTO geometries VALUES ('Point', 'POINT(0 0)'), ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'), ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'), ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'), ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

 * postgresql://postgres:***@localhost/nyc
(psycopg2.errors.DuplicateTable) relation "geometries" already exists

[SQL: CREATE TABLE geometries (name varchar, geom geometry);]
(Background on this error at: http://sqlalche.me/e/14/f405)

• ST_AsText: convert geometry to text

In [22]:

%%sql
SELECT name, ST_AsText(geom) FROM geometries;

%%sql SELECT name, ST_AsText(geom) FROM geometries;

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[22]:

name	st_astext
Point	POINT(0 0)
Linestring	LINESTRING(0 0,1 1,2 1,2 2)
Polygon	POLYGON((0 0,1 0,1 1,0 1,0 0))
PolygonWithHole	POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1))
Collection	GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0,1 0,1 1,0 1,0 0)))

Metadata tables¶

In [24]:

%%sql 

SELECT * FROM spatial_ref_sys LIMIT 5

%%sql SELECT * FROM spatial_ref_sys LIMIT 5

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[24]:

srid	auth_name	auth_srid	srtext	proj4text
2000	EPSG	2000	PROJCS["Anguilla 1957 / British West Indies Grid",GEOGCS["Anguilla 1957",DATUM["Anguilla_1957",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],AUTHORITY["EPSG","6600"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4600"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2000"]]	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +units=m +no_defs
2001	EPSG	2001	PROJCS["Antigua 1943 / British West Indies Grid",GEOGCS["Antigua 1943",DATUM["Antigua_1943",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],TOWGS84[-255,-15,71,0,0,0,0],AUTHORITY["EPSG","6601"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4601"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2001"]]	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=-255,-15,71,0,0,0,0 +units=m +no_defs
2002	EPSG	2002	PROJCS["Dominica 1945 / British West Indies Grid",GEOGCS["Dominica 1945",DATUM["Dominica_1945",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],TOWGS84[725,685,536,0,0,0,0],AUTHORITY["EPSG","6602"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4602"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2002"]]	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=725,685,536,0,0,0,0 +units=m +no_defs
2003	EPSG	2003	PROJCS["Grenada 1953 / British West Indies Grid",GEOGCS["Grenada 1953",DATUM["Grenada_1953",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],TOWGS84[72,213.7,93,0,0,0,0],AUTHORITY["EPSG","6603"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4603"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2003"]]	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=72,213.7,93,0,0,0,0 +units=m +no_defs
2004	EPSG	2004	PROJCS["Montserrat 1958 / British West Indies Grid",GEOGCS["Montserrat 1958",DATUM["Montserrat_1958",SPHEROID["Clarke 1880 (RGS)",6378249.145,293.465,AUTHORITY["EPSG","7012"]],TOWGS84[174,359,365,0,0,0,0],AUTHORITY["EPSG","6604"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4604"]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",0],PARAMETER["central_meridian",-62],PARAMETER["scale_factor",0.9995],PARAMETER["false_easting",400000],PARAMETER["false_northing",0],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Easting",EAST],AXIS["Northing",NORTH],AUTHORITY["EPSG","2004"]]	+proj=tmerc +lat_0=0 +lon_0=-62 +k=0.9995000000000001 +x_0=400000 +y_0=0 +ellps=clrk80 +towgs84=174,359,365,0,0,0,0 +units=m +no_defs

Check the geometry of all tables

In [25]:

%%sql

SELECT * FROM geometry_columns

%%sql SELECT * FROM geometry_columns

 * postgresql://postgres:***@localhost/nyc
6 rows affected.

Out[25]:

f_table_catalog	f_table_schema	f_table_name	f_geometry_column	coord_dimension	srid	type
nyc	public	nyc_subway_stations	geom	2	26918	POINT
nyc	public	nyc_neighborhoods	geom	2	26918	MULTIPOLYGON
nyc	public	nyc_census_blocks	geom	2	26918	MULTIPOLYGON
nyc	public	nyc_homicides	geom	2	26918	POINT
nyc	public	nyc_streets	geom	2	26918	MULTILINESTRING
nyc	public	geometries	geom	2	0	GEOMETRY

• ST_GeometryType: get the real geometry type
• ST_SRID: get the spatial reference id

In [26]:

%%sql 

SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)
  FROM geometries;

%%sql SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom) FROM geometries;

 * postgresql://postgres:***@localhost/nyc
5 rows affected.

Out[26]:

name	st_geometrytype	st_ndims	st_srid
Point	ST_Point	2	0
Linestring	ST_LineString	2	0
Polygon	ST_Polygon	2	0
PolygonWithHole	ST_Polygon	2	0
Collection	ST_GeometryCollection	2	0

Points¶

In [27]:

%%sql

SELECT ST_AsText(geom)
  FROM geometries
  WHERE name = 'Point';

%%sql SELECT ST_AsText(geom) FROM geometries WHERE name = 'Point';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[27]:

st_astext
POINT(0 0)

Some of the specific spatial functions for working with points are:

• ST_X(geometry) returns the X ordinate
• ST_Y(geometry) returns the Y ordinate

So, we can read the ordinates from a point like this:

In [28]:

%%sql

SELECT ST_X(geom), ST_Y(geom)
  FROM geometries
  WHERE name = 'Point';

%%sql SELECT ST_X(geom), ST_Y(geom) FROM geometries WHERE name = 'Point';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[28]:

st_x	st_y
0.0	0.0

In [29]:

%%sql

SELECT name, ST_AsText(geom)
  FROM nyc_subway_stations
  LIMIT 10;

%%sql SELECT name, ST_AsText(geom) FROM nyc_subway_stations LIMIT 10;

 * postgresql://postgres:***@localhost/nyc
10 rows affected.

Out[29]:

name	st_astext
Cortlandt St	POINT(583521.854408956 4507077.862599085)
Rector St	POINT(583324.4866324601 4506805.373160211)
South Ferry	POINT(583304.1823994748 4506069.654048115)
138th St	POINT(590250.10594797 4518558.019924332)
149th St	POINT(590454.7399891173 4519145.719617855)
149th St	POINT(590465.8934191109 4519168.697483203)
161st St	POINT(590573.169495527 4520214.766177284)
167th St	POINT(591252.8314104103 4520950.353355553)
167th St	POINT(590946.3972262995 4521077.318976877)
170th St	POINT(591583.6111452815 4521434.846626811)

In [30]:

%%sql

SELECT name, ST_AsText(geom) AS geometry, ST_X(geom) AS X, ST_Y(geom) AS Y
  FROM nyc_subway_stations
  LIMIT 10;

%%sql SELECT name, ST_AsText(geom) AS geometry, ST_X(geom) AS X, ST_Y(geom) AS Y FROM nyc_subway_stations LIMIT 10;

 * postgresql://postgres:***@localhost/nyc
10 rows affected.

Out[30]:

name	geometry	x	y
Cortlandt St	POINT(583521.854408956 4507077.862599085)	583521.854408956	4507077.862599085
Rector St	POINT(583324.4866324601 4506805.373160211)	583324.4866324601	4506805.373160211
South Ferry	POINT(583304.1823994748 4506069.654048115)	583304.1823994748	4506069.654048115
138th St	POINT(590250.10594797 4518558.019924332)	590250.10594797	4518558.019924332
149th St	POINT(590454.7399891173 4519145.719617855)	590454.7399891173	4519145.719617855
149th St	POINT(590465.8934191109 4519168.697483203)	590465.8934191109	4519168.697483203
161st St	POINT(590573.169495527 4520214.766177284)	590573.169495527	4520214.766177284
167th St	POINT(591252.8314104103 4520950.353355553)	591252.8314104103	4520950.353355553
167th St	POINT(590946.3972262995 4521077.318976877)	590946.3972262995	4521077.318976877
170th St	POINT(591583.6111452815 4521434.846626811)	591583.6111452815	4521434.846626811

Linestrings¶

• A linestring is a path between locations. It takes the form of an ordered series of two or more points.
• Roads and rivers are typically represented as linestrings.
• A linestring is said to be closed if it starts and ends on the same point.
• It is said to be simple if it does not cross or touch itself (except at its endpoints if it is closed).
• A linestring can be both closed and simple.

In [31]:

%%sql

SELECT ST_AsText(geom)
  FROM geometries
  WHERE name = 'Linestring';

%%sql SELECT ST_AsText(geom) FROM geometries WHERE name = 'Linestring';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[31]:

st_astext
LINESTRING(0 0,1 1,2 1,2 2)

Some of the specific spatial functions for working with linestrings are:

• ST_Length(geometry) returns the length of the linestring
• ST_StartPoint(geometry) returns the first coordinate as a point
• ST_EndPoint(geometry) returns the last coordinate as a point
• ST_NPoints(geometry) returns the number of coordinates in the linestring

So, the length of our linestring is:

In [32]:

%%sql 

SELECT ST_Length(geom)
  FROM geometries
  WHERE name = 'Linestring';

%%sql SELECT ST_Length(geom) FROM geometries WHERE name = 'Linestring';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[32]:

st_length
3.414213562373095

Polygons¶

• A polygon is a representation of an area.
• The outer boundary of the polygon is represented by a ring. This ring is a linestring that is both closed and simple as defined above.
• Holes within the polygon are also represented by rings.

In [33]:

%%sql

SELECT ST_AsText(geom)
  FROM geometries
  WHERE name LIKE 'Polygon%';

%%sql SELECT ST_AsText(geom) FROM geometries WHERE name LIKE 'Polygon%';

 * postgresql://postgres:***@localhost/nyc
2 rows affected.

Out[33]:

st_astext
POLYGON((0 0,1 0,1 1,0 1,0 0))
POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1))

Some of the specific spatial functions for working with polygons are:

• ST_Area(geometry) returns the area of the polygons
• ST_NRings(geometry) returns the number of rings (usually 1, more of there are holes)
• ST_ExteriorRing(geometry) returns the outer ring as a linestring
• ST_InteriorRingN(geometry,n) returns a specified interior ring as a linestring
• ST_Perimeter(geometry) returns the length of all the rings

We can calculate the area of our polygons using the area function:

In [34]:

%%sql

SELECT name, ST_Area(geom)
  FROM geometries
  WHERE name LIKE 'Polygon%';

%%sql SELECT name, ST_Area(geom) FROM geometries WHERE name LIKE 'Polygon%';

 * postgresql://postgres:***@localhost/nyc
2 rows affected.

Out[34]:

name	st_area
Polygon	1.0
PolygonWithHole	99.0

Collections¶

There are four collection types, which group multiple simple geometries into sets.

• MultiPoint, a collection of points
• MultiLineString, a collection of linestrings
• MultiPolygon, a collection of polygons
• GeometryCollection, a heterogeneous collection of any geometry (including other collections)

Collections are another concept that shows up in GIS software more than in generic graphics software.

• They are useful for directly modeling real world objects as spatial objects.
• For example, how to model a lot that is split by a right-of-way?
  • As a MultiPolygon, with a part on either side of the right-of-way.

In [35]:

%%sql

SELECT name, ST_AsText(geom)
  FROM geometries
  WHERE name = 'Collection';

%%sql SELECT name, ST_AsText(geom) FROM geometries WHERE name = 'Collection';

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[35]:

name	st_astext
Collection	GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0,1 0,1 1,0 1,0 0)))

Some of the specific spatial functions for working with collections are:

• ST_NumGeometries(geometry) returns the number of parts in the collection
• ST_GeometryN(geometry,n) returns the specified part
• ST_Area(geometry) returns the total area of all polygonal parts
• ST_Length(geometry) returns the total length of all linear parts

Geometry Input and Output¶

Within the database, geometries are stored on disk in a format only used by the PostGIS program.

• In order for external programs to insert and retrieve useful geometries, they need to be converted into a format that other applications can understand.

• Fortunately, PostGIS supports emitting and consuming geometries in a large number of formats:

• Well-known text (WKT)

  • ST_GeomFromText(text, srid) returns geometry
  • ST_AsText(geometry) returns text
  • ST_AsEWKT(geometry) returns text

• Well-known binary (WKB)

  • ST_GeomFromWKB(bytea) returns geometry

  • ST_AsBinary(geometry) returns bytea

  • ST_AsEWKB(geometry) returns bytea

• Geographic Mark-up Language (GML)

  • ST_GeomFromGML(text) returns geometry

  • ST_AsGML(geometry) returns text

• Keyhole Mark-up Language (KML)

  • ST_GeomFromKML(text) returns geometry

  • ST_AsKML(geometry) returns text

• GeoJSON

  • ST_AsGeoJSON(geometry) returns text

• Scalable Vector Graphics (SVG)

  • ST_AsSVG(geometry) returns text

In addition to the ST_GeometryFromText function, there are many other ways to create geometries from well-known text or similar formatted inputs:

In [36]:

%%sql

-- Using ST_GeomFromText with the SRID parameter
SELECT ST_GeomFromText('POINT(2 2)',4326);

%%sql -- Using ST_GeomFromText with the SRID parameter SELECT ST_GeomFromText('POINT(2 2)',4326);

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[36]:

st_geomfromtext
0101000020E610000000000000000000400000000000000040

In [37]:

%%sql
-- Using ST_GeomFromText without the SRID parameter
SELECT ST_SetSRID(ST_GeomFromText('POINT(2 2)'),4326);

%%sql -- Using ST_GeomFromText without the SRID parameter SELECT ST_SetSRID(ST_GeomFromText('POINT(2 2)'),4326);

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[37]:

st_setsrid
0101000020E610000000000000000000400000000000000040

In [38]:

%%sql
-- Using a ST_Make* function
SELECT ST_SetSRID(ST_MakePoint(2, 2), 4326);

%%sql -- Using a ST_Make* function SELECT ST_SetSRID(ST_MakePoint(2, 2), 4326);

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[38]:

st_setsrid
0101000020E610000000000000000000400000000000000040

PostgreSQL casting syntax value::[Target Type]

In [39]:

%%sql
-- Using PostgreSQL casting syntax and ISO WKT
SELECT ST_SetSRID('POINT(2 2)'::geometry, 4326);

%%sql -- Using PostgreSQL casting syntax and ISO WKT SELECT ST_SetSRID('POINT(2 2)'::geometry, 4326);

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[39]:

st_setsrid
0101000020E610000000000000000000400000000000000040

In [40]:

%%sql
-- Using PostgreSQL casting syntax and extended WKT
SELECT 'SRID=4326;POINT(2 2)'::geometry;

%%sql -- Using PostgreSQL casting syntax and extended WKT SELECT 'SRID=4326;POINT(2 2)'::geometry;

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[40]:

geometry
0101000020E610000000000000000000400000000000000040

Type Casting in SQL¶

• The WKT strings we’ve see so far have been of type ‘text’ and we have been converting them to type ‘geometry’ using PostGIS functions like ST_GeomFromText().

• PostgreSQL includes a short form syntax that allows data to be converted from one type to another, the casting syntax, oldata::newtype. So for example, this SQL converts a double into a text string.

In [41]:

%%sql

SELECT 0.9::text;

%%sql SELECT 0.9::text;

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[41]:

text
0.9

Less trivially, this SQL converts a WKT string into a geometry:

In [42]:

%%sql

SELECT 'POINT(0 0)'::geometry;

%%sql SELECT 'POINT(0 0)'::geometry;

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[42]:

geometry
010100000000000000000000000000000000000000

One thing to note about using casting to create geometries: unless you specify the SRID, you will get a geometry with an unknown SRID. You can specify the SRID using the “extended” well-known text form, which includes an SRID block at the front:

In [43]:

%%sql

SELECT 'SRID=4326;POINT(0 0)'::geometry;

%%sql SELECT 'SRID=4326;POINT(0 0)'::geometry;

 * postgresql://postgres:***@localhost/nyc
1 rows affected.

Out[43]:

geometry
0101000020E610000000000000000000000000000000000000

Function List¶

• ST_Area: Returns the area of the surface if it is a polygon or multi-polygon. For “geometry” type area is in SRID units. For “geography” area is in square meters.

• ST_AsText: Returns the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.

• ST_AsBinary: Returns the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.

• ST_EndPoint: Returns the last point of a LINESTRING geometry as a POINT.

• ST_AsEWKB: Returns the Well-Known Binary (WKB) representation of the geometry with SRID meta data.

• ST_AsEWKT: Returns the Well-Known Text (WKT) representation of the geometry with SRID meta data.

• ST_AsGeoJSON: Returns the geometry as a GeoJSON element.

• ST_AsGML: Returns the geometry as a GML version 2 or 3 element.

• ST_AsKML: Returns the geometry as a KML element. Several variants. Default version=2, default precision=15.

• ST_AsSVG: Returns a Geometry in SVG path data given a geometry or geography object.

• ST_ExteriorRing: Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON

• ST_GeometryN: Returns the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE or MULTIPOLYGON. Otherwise, return NULL.

• ST_GeomFromGML: Takes as input GML representation of geometry and outputs a PostGIS geometry object.

• ST_GeomFromKML: Takes as input KML representation of geometry and outputs a PostGIS geometry object

• ST_GeomFromText: Returns a specified ST_Geometry value from Well-Known Text representation (WKT).

• ST_GeomFromWKB: Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID.

• ST_GeometryType: Returns the geometry type of the ST_Geometry value.

• ST_InteriorRingN: Returns the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.

• ST_Length: Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geogra