Tutorial Instructions

# This tutorial has quite a bit of data and takes a sec to load so be a little patient.

PostGIS is one of the most powerful extensions for PostgreSQL and it can turn a database into a GIS (Geographic Information System).

For this tutorial, we’ve loaded a data bundle of 2020 New York City Census data. This data is also part of the PostGIS.net tutorial if you’d like to dig in deeper there.

### Finding single points:

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

### Calculating area

In square meters

``````SELECT ST_Area(geom)
FROM nyc_neighborhoods
WHERE name = 'West Village';``````

### Calculating length

``````SELECT ST_Length(geom)
FROM nyc_streets
WHERE name = 'Columbus Cir';``````

What is the length of streets in New York City, summarized by type?

``````SELECT type, Sum(ST_Length(geom)) AS length
FROM nyc_streets
GROUP BY type
ORDER BY length DESC;``````

### The SRID

When transforming coordinate data (such as with `ST_GeomFromText()`) you need a standardized way of transforming from latitude/longitude to internal representations.  PostGIS comes with a `spatial_ref_sys` spatial reference table upon installation that contains the most common spatial references, standardized across GIS offerings.  In this case we are using the id of `26918` which is a common projection for projections centered around North America.

### Extrapolating from a point

Find the point

``````SELECT name, ST_AsText(geom)
FROM nyc_subway_stations
``````name   |                 st_astext
----------+--------------------------------------------
(1 row)``````

Find the district and borough name for that point.

``````SELECT name, boroname
FROM nyc_neighborhoods
WHERE ST_Intersects(geom, ST_GeomFromText('POINT(583571 4506714)',26918));``````

### Spatial Joins

``````SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)

### Distance

From one point to another

``````SELECT ST_Distance(a.geom, b.geom)
FROM nyc_streets a, nyc_streets b
WHERE a.name = 'Columbus Cir'
AND b.name = 'Atlantic Commons';``````

Or distance to find something close. For example the streets with 10 meters of the Broad Street station (distance from a point).

``````SELECT name
FROM nyc_streets
WHERE ST_DWithin(
geom,
ST_GeomFromText('POINT(583571 4506714)',26918),
10
);``````

Want to go further and test QGIS or some of our API web tools, see our [PostGIS for Newbies blog](https://www.crunchydata.com/blog/postgis-for-newbies). 