PostGIS is an open-source extension to PostgreSQL that adds support for geo-referenced queries. PostGIS adds a geometry column to your table that indicates the shape and geolocation of your row.
here the geometry column is called ‘geom’
Here are three of the most common queries that I use.
- Display the geometry as text
select ST_AsEWKT(geometry_column) from table;
- Select rows from table inside given bounding box
# Here, (xmin, ymin, xmax, ymax) are the edges of your bounding box SELECT * FROM table_name WHERE geometry_column && ST_MakeEnvelope(xmin, ymin, xmax, ymax); # Example SELECT * FROM rain_gauges WHERE geom && ST_MakeEnvelope(1503535, -174488.9, 1603535.4, -154488.8);
- Change the projection of a table
# This will change the srid of the table table_name table to 2295 from whatever it was before SELECT UpdateGeometrySRID('table_name','geometry_column,2295);
For more info on projections & SRIDs : https://spatialreference.org/ref/sr-org/what-is-srid/