{ "cells": [ { "cell_type": "markdown", "id": "116fcdbc", "metadata": {}, "source": [ "# Working with Geometries\n", "\n", "**Setting up the conda env:**\n", "\n", "```\n", "conda create -n sql python\n", "conda activate sql\n", "conda install ipython-sql sqlalchemy psycopg2 notebook pandas -c conda-forge\n", "```\n", "\n", "**Sample dataset:**\n", "- [nyc_data.zip](https://github.com/giswqs/postgis/raw/master/data/nyc_data.zip) (Watch this [video](https://youtu.be/fROzLrjNDrs) to load data into PostGIS)\n", "\n", "**References**:\n", "- [Introduction to PostGIS](https://postgis.net/workshops/postgis-intro)\n", "- [Using SQL with Geodatabases](https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/sql-and-enterprise-geodatabases.htm)" ] }, { "cell_type": "markdown", "id": "b43030e6", "metadata": {}, "source": [ "## Connecting to the database" ] }, { "cell_type": "code", "execution_count": 1, "id": "30a969e8", "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "id": "4a222a24", "metadata": {}, "outputs": [], "source": [ "import os" ] }, { "cell_type": "code", "execution_count": 3, "id": "df2880de", "metadata": {}, "outputs": [], "source": [ "host = \"localhost\"\n", "database = \"nyc\"\n", "user = os.getenv('SQL_USER')\n", "password = os.getenv('SQL_PASSWORD')" ] }, { "cell_type": "code", "execution_count": 4, "id": "dc4f36b1", "metadata": {}, "outputs": [], "source": [ "connection_string = f\"postgresql://{user}:{password}@{host}/{database}\"" ] }, { "cell_type": "code", "execution_count": 5, "id": "c74c78db", "metadata": {}, "outputs": [], "source": [ "%sql $connection_string" ] }, { "cell_type": "code", "execution_count": 6, "id": "6bd2a33c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "0 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
idgeomboronamename
" ], "text/plain": [ "[]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT * FROM nyc_neighborhoods WHERE FALSE" ] }, { "cell_type": "markdown", "id": "71396af8", "metadata": {}, "source": [ "## Creating geometries" ] }, { "cell_type": "code", "execution_count": 7, "id": "5e6eef1f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "(psycopg2.errors.DuplicateTable) relation \"geometries\" already exists\n", "\n", "[SQL: CREATE TABLE geometries (name varchar, geom geometry);]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "CREATE TABLE geometries (name varchar, geom geometry);\n", "\n", "INSERT INTO geometries VALUES\n", " ('Point', 'POINT(0 0)'),\n", " ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),\n", " ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),\n", " ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),\n", " ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');\n", "\n", "SELECT name, ST_AsText(geom) FROM geometries;" ] }, { "cell_type": "markdown", "id": "9fa845b7", "metadata": {}, "source": [ "## Metadata tables" ] }, { "cell_type": "code", "execution_count": 8, "id": "78f6c6f0", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sridauth_nameauth_sridsrtextproj4text
3819EPSG3819GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408],AUTHORITY["EPSG","1024"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3819"]]+proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs
3821EPSG3821GEOGCS["TWD67",DATUM["Taiwan_Datum_1967",SPHEROID["GRS 1967 Modified",6378160,298.25,AUTHORITY["EPSG","7050"]],AUTHORITY["EPSG","1025"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3821"]]+proj=longlat +ellps=aust_SA +no_defs
3824EPSG3824GEOGCS["TWD97",DATUM["Taiwan_Datum_1997",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","1026"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3824"]]+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs
3889EPSG3889GEOGCS["IGRS",DATUM["Iraqi_Geospatial_Reference_System",SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY["EPSG","1029"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3889"]]+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs
3906EPSG3906GEOGCS["MGI 1901",DATUM["MGI_1901",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],TOWGS84[682,-203,480,0,0,0,0],AUTHORITY["EPSG","1031"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","3906"]]+proj=longlat +ellps=bessel +towgs84=682,-203,480,0,0,0,0 +no_defs
4001EPSG4001GEOGCS["Unknown datum based upon the Airy 1830 ellipsoid",DATUM["Not_specified_based_on_Airy_1830_ellipsoid",SPHEROID["Airy 1830",6377563.396,299.3249646,AUTHORITY["EPSG","7001"]],AUTHORITY["EPSG","6001"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4001"]]+proj=longlat +ellps=airy +no_defs
4002EPSG4002GEOGCS["Unknown datum based upon the Airy Modified 1849 ellipsoid",DATUM["Not_specified_based_on_Airy_Modified_1849_ellipsoid",SPHEROID["Airy Modified 1849",6377340.189,299.3249646,AUTHORITY["EPSG","7002"]],AUTHORITY["EPSG","6002"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4002"]]+proj=longlat +ellps=mod_airy +no_defs
4003EPSG4003GEOGCS["Unknown datum based upon the Australian National Spheroid",DATUM["Not_specified_based_on_Australian_National_Spheroid",SPHEROID["Australian National Spheroid",6378160,298.25,AUTHORITY["EPSG","7003"]],AUTHORITY["EPSG","6003"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4003"]]+proj=longlat +ellps=aust_SA +no_defs
4004EPSG4004GEOGCS["Unknown datum based upon the Bessel 1841 ellipsoid",DATUM["Not_specified_based_on_Bessel_1841_ellipsoid",SPHEROID["Bessel 1841",6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],AUTHORITY["EPSG","6004"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4004"]]+proj=longlat +ellps=bessel +no_defs
4005EPSG4005GEOGCS["Unknown datum based upon the Bessel Modified ellipsoid",DATUM["Not_specified_based_on_Bessel_Modified_ellipsoid",SPHEROID["Bessel Modified",6377492.018,299.1528128,AUTHORITY["EPSG","7005"]],AUTHORITY["EPSG","6005"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4005"]]+proj=longlat +a=6377492.018 +b=6356173.508712696 +no_defs
" ], "text/plain": [ "[(3819, 'EPSG', 3819, 'GEOGCS[\"HD1909\",DATUM[\"Hungarian_Datum_1909\",SPHEROID[\"Bessel 1841\",6377397.155,299.1528128,AUTHORITY[\"EPSG\",\"7004\"]],TOWGS84[595.48,121.69,515.35,4. ... (34 characters truncated) ... Y[\"EPSG\",\"1024\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"3819\"]]', '+proj=longlat +ellps=bessel +towgs84=595.48,121.69,515.35,4.115,-2.9383,0.853,-3.408 +no_defs '),\n", " (3821, 'EPSG', 3821, 'GEOGCS[\"TWD67\",DATUM[\"Taiwan_Datum_1967\",SPHEROID[\"GRS 1967 Modified\",6378160,298.25,AUTHORITY[\"EPSG\",\"7050\"]],AUTHORITY[\"EPSG\",\"1025\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"3821\"]]', '+proj=longlat +ellps=aust_SA +no_defs '),\n", " (3824, 'EPSG', 3824, 'GEOGCS[\"TWD97\",DATUM[\"Taiwan_Datum_1997\",SPHEROID[\"GRS 1980\",6378137,298.257222101,AUTHORITY[\"EPSG\",\"7019\"]],TOWGS84[0,0,0,0,0,0,0],AUTHORITY[\"EPSG\",\"1026\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"3824\"]]', '+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs '),\n", " (3889, 'EPSG', 3889, 'GEOGCS[\"IGRS\",DATUM[\"Iraqi_Geospatial_Reference_System\",SPHEROID[\"GRS 1980\",6378137,298.257222101,AUTHORITY[\"EPSG\",\"7019\"]],TOWGS84[0,0,0,0,0,0,0],AU ... (6 characters truncated) ... Y[\"EPSG\",\"1029\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"3889\"]]', '+proj=longlat +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +no_defs '),\n", " (3906, 'EPSG', 3906, 'GEOGCS[\"MGI 1901\",DATUM[\"MGI_1901\",SPHEROID[\"Bessel 1841\",6377397.155,299.1528128,AUTHORITY[\"EPSG\",\"7004\"]],TOWGS84[682,-203,480,0,0,0,0],AUTHORITY[\"EPSG\",\"1031\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"3906\"]]', '+proj=longlat +ellps=bessel +towgs84=682,-203,480,0,0,0,0 +no_defs '),\n", " (4001, 'EPSG', 4001, 'GEOGCS[\"Unknown datum based upon the Airy 1830 ellipsoid\",DATUM[\"Not_specified_based_on_Airy_1830_ellipsoid\",SPHEROID[\"Airy 1830\",6377563.396,299.324 ... (39 characters truncated) ... Y[\"EPSG\",\"6001\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4001\"]]', '+proj=longlat +ellps=airy +no_defs '),\n", " (4002, 'EPSG', 4002, 'GEOGCS[\"Unknown datum based upon the Airy Modified 1849 ellipsoid\",DATUM[\"Not_specified_based_on_Airy_Modified_1849_ellipsoid\",SPHEROID[\"Airy Modifie ... (66 characters truncated) ... Y[\"EPSG\",\"6002\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4002\"]]', '+proj=longlat +ellps=mod_airy +no_defs '),\n", " (4003, 'EPSG', 4003, 'GEOGCS[\"Unknown datum based upon the Australian National Spheroid\",DATUM[\"Not_specified_based_on_Australian_National_Spheroid\",SPHEROID[\"Australian N ... (67 characters truncated) ... Y[\"EPSG\",\"6003\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4003\"]]', '+proj=longlat +ellps=aust_SA +no_defs '),\n", " (4004, 'EPSG', 4004, 'GEOGCS[\"Unknown datum based upon the Bessel 1841 ellipsoid\",DATUM[\"Not_specified_based_on_Bessel_1841_ellipsoid\",SPHEROID[\"Bessel 1841\",6377397.155,2 ... (45 characters truncated) ... Y[\"EPSG\",\"6004\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4004\"]]', '+proj=longlat +ellps=bessel +no_defs '),\n", " (4005, 'EPSG', 4005, 'GEOGCS[\"Unknown datum based upon the Bessel Modified ellipsoid\",DATUM[\"Not_specified_based_on_Bessel_Modified_ellipsoid\",SPHEROID[\"Bessel Modified\",6 ... (57 characters truncated) ... Y[\"EPSG\",\"6005\"]],PRIMEM[\"Greenwich\",0,AUTHORITY[\"EPSG\",\"8901\"]],UNIT[\"degree\",0.0174532925199433,AUTHORITY[\"EPSG\",\"9122\"]],AUTHORITY[\"EPSG\",\"4005\"]]', '+proj=longlat +a=6377492.018 +b=6356173.508712696 +no_defs ')]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT * FROM spatial_ref_sys LIMIT 10" ] }, { "cell_type": "code", "execution_count": 9, "id": "f189ec99", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "6 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
f_table_catalogf_table_schemaf_table_namef_geometry_columncoord_dimensionsridtype
nycpublicnyc_homicidesgeom226918POINT
nycpublicnyc_census_blocksgeom226918MULTIPOLYGON
nycpublicnyc_neighborhoodsgeom226918MULTIPOLYGON
nycpublicnyc_streetsgeom226918MULTILINESTRING
nycpublicnyc_subway_stationsgeom226918POINT
nycpublicgeometriesgeom20GEOMETRY
" ], "text/plain": [ "[('nyc', 'public', 'nyc_homicides', 'geom', 2, 26918, 'POINT'),\n", " ('nyc', 'public', 'nyc_census_blocks', 'geom', 2, 26918, 'MULTIPOLYGON'),\n", " ('nyc', 'public', 'nyc_neighborhoods', 'geom', 2, 26918, 'MULTIPOLYGON'),\n", " ('nyc', 'public', 'nyc_streets', 'geom', 2, 26918, 'MULTILINESTRING'),\n", " ('nyc', 'public', 'nyc_subway_stations', 'geom', 2, 26918, 'POINT'),\n", " ('nyc', 'public', 'geometries', 'geom', 2, 0, 'GEOMETRY')]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM geometry_columns" ] }, { "cell_type": "code", "execution_count": 10, "id": "578f50c6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "5 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namest_geometrytypest_ndimsst_srid
PointST_Point20
LinestringST_LineString20
PolygonST_Polygon20
PolygonWithHoleST_Polygon20
CollectionST_GeometryCollection20
" ], "text/plain": [ "[('Point', 'ST_Point', 2, 0),\n", " ('Linestring', 'ST_LineString', 2, 0),\n", " ('Polygon', 'ST_Polygon', 2, 0),\n", " ('PolygonWithHole', 'ST_Polygon', 2, 0),\n", " ('Collection', 'ST_GeometryCollection', 2, 0)]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)\n", " FROM geometries;" ] }, { "cell_type": "markdown", "id": "5928ddc6", "metadata": {}, "source": [ "## Points\n", "\n", "![](https://postgis.net/workshops/postgis-intro/_images/points.png)\n", "\n", "A spatial point represents a single location on the Earth. This point is represented by a single coordinate (including either 2-, 3- or 4-dimensions). Points are used to represent objects when the exact details, such as shape and size, are not important at the target scale. For example, cities on a map of the world can be described as points, while a map of a single state might represent cities as polygons.\n" ] }, { "cell_type": "code", "execution_count": 11, "id": "35b8d2fb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
st_astext
POINT(0 0)
" ], "text/plain": [ "[('POINT(0 0)',)]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT ST_AsText(geom)\n", " FROM geometries\n", " WHERE name = 'Point';" ] }, { "cell_type": "markdown", "id": "6b3c202f", "metadata": {}, "source": [ "Some of the specific spatial functions for working with points are:\n", "\n", "- **ST_X(geometry)** returns the X ordinate\n", "- **ST_Y(geometry)** returns the Y ordinate\n", "\n", "So, we can read the ordinates from a point like this:" ] }, { "cell_type": "code", "execution_count": 12, "id": "559161d2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
st_xst_y
0.00.0
" ], "text/plain": [ "[(0.0, 0.0)]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT ST_X(geom), ST_Y(geom)\n", " FROM geometries\n", " WHERE name = 'Point';" ] }, { "cell_type": "code", "execution_count": 13, "id": "5df159c9", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namest_astext
Cortlandt StPOINT(583521.854408956 4507077.86259909)
Rector StPOINT(583324.48663246 4506805.37316021)
South FerryPOINT(583304.182399475 4506069.65404812)
138th StPOINT(590250.10594797 4518558.01992433)
149th StPOINT(590454.739989117 4519145.71961785)
149th StPOINT(590465.893419111 4519168.6974832)
161st StPOINT(590573.169495527 4520214.76617728)
167th StPOINT(591252.83141041 4520950.35335555)
167th StPOINT(590946.3972263 4521077.31897688)
170th StPOINT(591583.611145281 4521434.84662681)
" ], "text/plain": [ "[('Cortlandt St', 'POINT(583521.854408956 4507077.86259909)'),\n", " ('Rector St', 'POINT(583324.48663246 4506805.37316021)'),\n", " ('South Ferry', 'POINT(583304.182399475 4506069.65404812)'),\n", " ('138th St', 'POINT(590250.10594797 4518558.01992433)'),\n", " ('149th St', 'POINT(590454.739989117 4519145.71961785)'),\n", " ('149th St', 'POINT(590465.893419111 4519168.6974832)'),\n", " ('161st St', 'POINT(590573.169495527 4520214.76617728)'),\n", " ('167th St', 'POINT(591252.83141041 4520950.35335555)'),\n", " ('167th St', 'POINT(590946.3972263 4521077.31897688)'),\n", " ('170th St', 'POINT(591583.611145281 4521434.84662681)')]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT name, ST_AsText(geom)\n", " FROM nyc_subway_stations\n", " LIMIT 10;" ] }, { "cell_type": "markdown", "id": "6c65c004", "metadata": {}, "source": [ "## Linestrings\n", "\n", "![](https://postgis.net/workshops/postgis-intro/_images/lines.png)\n", "\n", "\n", "A **linestring** is a path between locations. It takes the form of an\n", "ordered series of two or more points. Roads and rivers are typically\n", "represented as linestrings. A linestring is said to be **closed** if it\n", "starts and ends on the same point. It is said to be **simple** if it\n", "does not cross or touch itself (except at its endpoints if it is\n", "closed). A linestring can be both **closed** and **simple**.\n", "\n", "The street network for New York (`nyc_streets`) was loaded earlier in\n", "the workshop. This dataset contains details such as name, and type. A\n", "single real world street may consist of many linestrings, each\n", "representing a segment of road with different attributes.\n", "\n", "The following SQL query will return the geometry associated with one\n", "linestring (in the `ST_AsText` column)." ] }, { "cell_type": "code", "execution_count": 14, "id": "8c16481d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
st_astext
LINESTRING(0 0,1 1,2 1,2 2)
" ], "text/plain": [ "[('LINESTRING(0 0,1 1,2 1,2 2)',)]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT ST_AsText(geom)\n", " FROM geometries\n", " WHERE name = 'Linestring';" ] }, { "cell_type": "markdown", "id": "8500c582", "metadata": {}, "source": [ "Some of the specific spatial functions for working with linestrings are:\n", "\n", "- `ST_Length(geometry)` returns the length of the linestring\n", "- `ST_StartPoint(geometry)` returns the first coordinate as a point\n", "- `ST_EndPoint(geometry)` returns the last coordinate as a point\n", "- `ST_NPoints(geometry)` returns the number of coordinates in the\n", " linestring\n", "\n", "So, the length of our linestring is:" ] }, { "cell_type": "code", "execution_count": 15, "id": "5c3357c1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
st_length
3.414213562373095
" ], "text/plain": [ "[(3.414213562373095,)]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql \n", "\n", "SELECT ST_Length(geom)\n", " FROM geometries\n", " WHERE name = 'Linestring';" ] }, { "cell_type": "markdown", "id": "3f0b18a6", "metadata": {}, "source": [ "## Polygons" ] }, { "cell_type": "markdown", "id": "db445b68", "metadata": {}, "source": [ "![](https://postgis.net/workshops/postgis-intro/_images/polygons.png)\n", "\n", "A polygon is a representation of an area. The outer boundary of the\n", "polygon is represented by a ring. This ring is a linestring that is both\n", "closed and simple as defined above. Holes within the polygon are also\n", "represented by rings.\n", "\n", "Polygons are used to represent objects whose size and shape are\n", "important. City limits, parks, building footprints or bodies of water\n", "are all commonly represented as polygons when the scale is sufficiently\n", "high to see their area. Roads and rivers can sometimes be represented as\n", "polygons.\n", "\n", "The following SQL query will return the geometry associated with one\n", "polygon (in the `ST_AsText` column)." ] }, { "cell_type": "code", "execution_count": 16, "id": "c5d012ac", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
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))
" ], "text/plain": [ "[('POLYGON((0 0,1 0,1 1,0 1,0 0))',),\n", " ('POLYGON((0 0,10 0,10 10,0 10,0 0),(1 1,1 2,2 2,2 1,1 1))',)]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT ST_AsText(geom)\n", " FROM geometries\n", " WHERE name LIKE 'Polygon%';" ] }, { "cell_type": "markdown", "id": "d79deb6c", "metadata": {}, "source": [ "Some of the specific spatial functions for working with polygons are:\n", "\n", "- `ST_Area(geometry)` returns the area of the polygons\n", "- `ST_NRings(geometry)` returns the number of rings (usually 1, more\n", " of there are holes)\n", "- `ST_ExteriorRing(geometry)` returns the outer ring as a linestring\n", "- `ST_InteriorRingN(geometry,n)` returns a specified interior ring as\n", " a linestring\n", "- `ST_Perimeter(geometry)` returns the length of all the rings\n", "\n", "We can calculate the area of our polygons using the area function:" ] }, { "cell_type": "code", "execution_count": 17, "id": "babf8fd1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "2 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namest_area
Polygon1.0
PolygonWithHole99.0
" ], "text/plain": [ "[('Polygon', 1.0), ('PolygonWithHole', 99.0)]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT name, ST_Area(geom)\n", " FROM geometries\n", " WHERE name LIKE 'Polygon%';" ] }, { "cell_type": "markdown", "id": "07bf45a3", "metadata": {}, "source": [ "## Collections\n", "\n", "There are four collection types, which group multiple simple geometries\n", "into sets.\n", "\n", "- **MultiPoint**, a collection of points\n", "- **MultiLineString**, a collection of linestrings\n", "- **MultiPolygon**, a collection of polygons\n", "- **GeometryCollection**, a heterogeneous collection of any geometry\n", " (including other collections)\n", "\n", "Collections are another concept that shows up in GIS software more than\n", "in generic graphics software. They are useful for directly modeling real\n", "world objects as spatial objects. For example, how to model a lot that\n", "is split by a right-of-way? As a **MultiPolygon**, with a part on either\n", "side of the right-of-way.\n", "\n", "Our example collection contains a polygon and a point:" ] }, { "cell_type": "code", "execution_count": 18, "id": "eb4518bb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
namest_astext
CollectionGEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0,1 0,1 1,0 1,0 0)))
" ], "text/plain": [ "[('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0,1 0,1 1,0 1,0 0)))')]" ] }, "execution_count": 18, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT name, ST_AsText(geom)\n", " FROM geometries\n", " WHERE name = 'Collection';" ] }, { "cell_type": "markdown", "id": "c8893b4b", "metadata": {}, "source": [ "Some of the specific spatial functions for working with collections are:\n", "\n", "- `ST_NumGeometries(geometry)` returns the number of parts in the\n", " collection\n", "- `ST_GeometryN(geometry,n)` returns the specified part\n", "- `ST_Area(geometry)` returns the total area of all polygonal parts\n", "- `ST_Length(geometry)` returns the total length of all linear parts" ] }, { "cell_type": "markdown", "id": "c4284f3b", "metadata": {}, "source": [ "## Geometry Input and Output\n", "\n", "Within the database, geometries are stored on disk in a format only used\n", "by the PostGIS program. In order for external programs to insert and\n", "retrieve useful geometries, they need to be converted into a format that\n", "other applications can understand. Fortunately, PostGIS supports\n", "emitting and consuming geometries in a large number of formats:\n", "\n", "- Well-known text ([`WKT`](https://postgis.net/workshops/postgis-intro/glossary.html#term-wkt))\n", " - `ST_GeomFromText(text, srid)` returns `geometry`\n", " - `ST_AsText(geometry)` returns `text`\n", " - `ST_AsEWKT(geometry)` returns `text`\n", "- Well-known binary (`WKB`)\n", " - `ST_GeomFromWKB(bytea)` returns `geometry`\n", " - `ST_AsBinary(geometry)` returns `bytea`\n", " - `ST_AsEWKB(geometry)` returns `bytea`\n", "- Geographic Mark-up Language (`GML`)\n", " - `ST_GeomFromGML(text)` returns `geometry`\n", " - `ST_AsGML(geometry)` returns `text`\n", "- Keyhole Mark-up Language (`KML`)\n", " - `ST_GeomFromKML(text)` returns `geometry`\n", " - `ST_AsKML(geometry)` returns `text`\n", "- `GeoJSON`\n", " - `ST_AsGeoJSON(geometry)` returns `text`\n", "- Scalable Vector Graphics (`SVG`)\n", " - `ST_AsSVG(geometry)` returns `text`\n", "\n", "In addition to the `ST_GeometryFromText` function, there are many other\n", "ways to create geometries from well-known text or similar formatted\n", "inputs:" ] }, { "cell_type": "code", "execution_count": 19, "id": "0f004d23", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
geometry
0101000020E610000000000000000000400000000000000040
" ], "text/plain": [ "[('0101000020E610000000000000000000400000000000000040',)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "-- Using ST_GeomFromText with the SRID parameter\n", "SELECT ST_GeomFromText('POINT(2 2)',4326);\n", "\n", "-- Using ST_GeomFromText without the SRID parameter\n", "SELECT ST_SetSRID(ST_GeomFromText('POINT(2 2)'),4326);\n", "\n", "-- Using a ST_Make* function\n", "SELECT ST_SetSRID(ST_MakePoint(2, 2), 4326);\n", "\n", "-- Using PostgreSQL casting syntax and ISO WKT\n", "SELECT ST_SetSRID('POINT(2 2)'::geometry, 4326);\n", "\n", "-- Using PostgreSQL casting syntax and extended WKT\n", "SELECT 'SRID=4326;POINT(2 2)'::geometry;" ] }, { "cell_type": "markdown", "id": "c889980a", "metadata": {}, "source": [ "## Casting from Text\n", "\n", "The `WKT` strings we've see so far have been of type 'text' and we have\n", "been converting them to type 'geometry' using PostGIS functions like\n", "`ST_GeomFromText()`.\n", "\n", "PostgreSQL includes a short form syntax that allows data to be converted\n", "from one type to another, the casting syntax, oldata::newtype. So for example, this SQL\n", "converts a double into a text string." ] }, { "cell_type": "code", "execution_count": 20, "id": "569ce890", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
text
0.9
" ], "text/plain": [ "[('0.9',)]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT 0.9::text;" ] }, { "cell_type": "markdown", "id": "a9fe75f5", "metadata": {}, "source": [ "Less trivially, this SQL converts a WKT string into a geometry:" ] }, { "cell_type": "code", "execution_count": 21, "id": "3510c006", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
geometry
010100000000000000000000000000000000000000
" ], "text/plain": [ "[('010100000000000000000000000000000000000000',)]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT 'POINT(0 0)'::geometry;" ] }, { "cell_type": "markdown", "id": "74680683", "metadata": {}, "source": [ "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:" ] }, { "cell_type": "code", "execution_count": 22, "id": "e868ec68", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://postgres:***@localhost/nyc\n", "1 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", "
geometry
0101000020E610000000000000000000000000000000000000
" ], "text/plain": [ "[('0101000020E610000000000000000000000000000000000000',)]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT 'SRID=4326;POINT(0 0)'::geometry;" ] }, { "cell_type": "markdown", "id": "8d580020", "metadata": {}, "source": [ "## Function List\n", "\n", "\n", "[ST\\_Area][]: Returns the area of the surface if it is a polygon or\n", "multi-polygon. For \"geometry\" type area is in SRID units. For\n", "\"geography\" area is in square meters.\n", "\n", "[ST\\_AsText][]: Returns the Well-Known Text (WKT) representation of the\n", "geometry/geography without SRID metadata.\n", "\n", "[ST\\_AsBinary][]: Returns the Well-Known Binary (WKB) representation of\n", "the geometry/geography without SRID meta data.\n", "\n", "[ST\\_EndPoint][]: Returns the last point of a LINESTRING geometry as a\n", "POINT.\n", "\n", "[ST\\_AsEWKB][]: Returns the Well-Known Binary (WKB) representation of\n", "the geometry with SRID meta data.\n", "\n", "[ST\\_AsEWKT][]: Returns the Well-Known Text (WKT) representation of the\n", "geometry with SRID meta data.\n", "\n", "[ST\\_AsGeoJSON][]: Returns the geometry as a GeoJSON element.\n", "\n", "[ST\\_AsGML][]: Returns the geometry as a GML version 2 or 3 element.\n", "\n", "[ST\\_AsKML][]: Returns the geometry as a KML element. Several variants.\n", "Default version=2, default precision=15.\n", "\n", "[ST\\_AsSVG][]: Returns a Geometry in SVG path data given a geometry or\n", "geography object.\n", "\n", "[ST\\_ExteriorRing][]: Returns a line string representing the exterior\n", "ring of the POLYGON geometry. Return NULL if the geometry is not a\n", "polygon. Will not work with MULTIPOLYGON\n", "\n", "[ST\\_GeometryN][]: Returns the 1-based Nth geometry if the geometry is a\n", "GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE or\n", "MULTIPOLYGON. Otherwise, return NULL.\n", "\n", "[ST\\_GeomFromGML][]: Takes as input GML representation of geometry and\n", "outputs a PostGIS geometry object.\n", "\n", "[ST\\_GeomFromKML][]: Takes as input KML representation of geometry and\n", "outputs a PostGIS geometry object\n", "\n", "[ST\\_GeomFromText][]: Returns a specified ST\\_Geometry value from\n", "Well-Known Text representation (WKT).\n", "\n", "[ST\\_GeomFromWKB][]: Creates a geometry instance from a Well-Known\n", "Binary geometry representation (WKB) and optional SRID.\n", "\n", "[ST\\_GeometryType][]: Returns the geometry type of the ST\\_Geometry\n", "value.\n", "\n", "[ST\\_InteriorRingN][]: Returns the Nth interior linestring ring of the\n", "polygon geometry. Return NULL if the geometry is not a polygon or the\n", "given N is out of range.\n", "\n", "[ST\\_Length][]: Returns the 2d length of the geometry if it is a\n", "linestring or multilinestring. geometry are in units of spatial\n", "reference and geogra\n", "\n", " [ST\\_Area]: http://postgis.net/docs/ST_Area.html\n", " [ST\\_AsText]: http://postgis.net/docs/ST_AsText.html\n", " [ST\\_AsBinary]: http://postgis.net/docs/ST_AsBinary.html\n", " [ST\\_EndPoint]: http://postgis.net/docs/ST_EndPoint.html\n", " [ST\\_AsEWKB]: http://postgis.net/docs/ST_AsEWKB.html\n", " [ST\\_AsEWKT]: http://postgis.net/docs/ST_AsEWKT.html\n", " [ST\\_AsGeoJSON]: http://postgis.net/docs/ST_AsGeoJSON.html\n", " [ST\\_AsGML]: http://postgis.net/docs/ST_AsGML.html\n", " [ST\\_AsKML]: http://postgis.net/docs/ST_AsKML.html\n", " [ST\\_AsSVG]: http://postgis.net/docs/ST_AsSVG.html\n", " [ST\\_ExteriorRing]: http://postgis.net/docs/ST_ExteriorRing.html\n", " [ST\\_GeometryN]: http://postgis.net/docs/ST_GeometryN.html\n", " [ST\\_GeomFromGML]: http://postgis.net/docs/ST_GeomFromGML.html\n", " [ST\\_GeomFromKML]: http://postgis.net/docs/ST_GeomFromKML.html\n", " [ST\\_GeomFromText]: http://postgis.net/docs/ST_GeomFromText.html\n", " [ST\\_GeomFromWKB]: http://postgis.net/docs/ST_GeomFromWKB.html\n", " [ST\\_GeometryType]: http://postgis.net/docs/ST_GeometryType.html\n", " [ST\\_InteriorRingN]: http://postgis.net/docs/ST_InteriorRingN.html\n", " [ST\\_Length]: http://postgis.net/docs/ST_Length.html" ] } ], "metadata": { "celltoolbar": "Tags", "hide_input": false, "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.2" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": true, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false }, "varInspector": { "cols": { "lenName": 16, "lenType": 16, "lenVar": 40 }, "kernels_config": { "python": { "delete_cmd_postfix": "", "delete_cmd_prefix": "del ", "library": "var_list.py", "varRefreshCmd": "print(var_dic_list())" }, "r": { "delete_cmd_postfix": ") ", "delete_cmd_prefix": "rm(", "library": "var_list.r", "varRefreshCmd": "cat(var_dic_list()) " } }, "types_to_exclude": [ "module", "function", "builtin_function_or_method", "instance", "_Feature" ], "window_display": false } }, "nbformat": 4, "nbformat_minor": 5 }