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.
SELECT name, ST_AsText(geom) FROM nyc_subway_stations LIMIT 10;
In square meters
SELECT ST_Area(geom) FROM nyc_neighborhoods WHERE name = 'West Village';
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;
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.
Find the point
SELECT name, ST_AsText(geom) FROM nyc_subway_stations WHERE name = 'Broad St';
name | st_astext ----------+-------------------------------------------- Broad St | POINT(583571.9059213118 4506714.341192182) (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));
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) WHERE subways.name = 'Broad St';
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).