Use spatial functions in PostGIS

A part of my PostGIS project

In this post, I am going to use some basic spatial functions in PostGIS. About the dataset used in this post, please refer to my previous posts.

Distance between points

First, I use ST_Distance that measures the distance between two points.

SELECT f1.company,f2.company,f1.city,f2.city,
ST_Transform(f1.geom,4326) AS fgeom, 
ST_Transform(f2.geom,4326) AS fgeom2, 
ST_Distance(f1.geom,f2.geom) AS distance
FROM toyproject.fortune AS f1, toyproject.fortune AS f2
WHERE ST_Distance(f1.geom,f2.geom)>0
ORDER BY ST_Distance(f1.geom,f2.geom)

The result is shown in the table below. The unit of the distance is meter. But if you select the different spatial reference system, it might not be the metric system. You can see that the distance between Paypal and CISCO is 5710m. Screen Shot 2021-03-21 at 6.26.47 PM.png

Screen Shot 2021-03-22 at 11.37.39 AM.png

Filter rows by geographical distance

Next, I filter out the companies by the distance. In this example, companies within 10 miles from the HQ of CISCO SYSTEMS, located in San Jose, CA. 12 company HQs are left after the script.

SELECT f1.company,f2.company,f1.city,f2.city,ST_Transform(f1.geom,4326) AS fgeom,
ST_Distance(f1.geom,f2.geom) AS distance
FROM toyproject.fortune AS f1 INNER JOIN toyproject.fortune AS f2
ON(ST_DWithin(f1.geom,f2.geom,16000))
WHERE f1.company='CISCO SYSTEMS'

Screen Shot 2021-03-22 at 11.44.57 AM.png

If we change the company name to WALMART, located at Bentonville, AK, no company meets this criterion.

Screen Shot 2021-03-22 at 11.49.36 AM.png

Extract overlapping geometries using ST_Intersects

Next, I spatially select the companies of which headquarters intersect the specific metropolitan areas. As a spatial data of the metropolitan areas, I use Core-Based Statistical Areas (CBSAs). The details of CBSAs are available from here. The locations of all the CBSAs are shown in the map below. Screen Shot 2021-03-27 at 6.32.38 PM.png

I imported the shapefile into my database

shp2pgsql -s 4269 -g geom_4269 -I 
"/path/to/the/shapefile/cbsa" 
toyproject.CBSAs 
| psql -h localhost -p 5432 -d postgis_in_action -U shingo

Then, write a query that selects only the companies that are located within the Houston-The Woodlands metropolitan area.

select f.*,st_transform(f.geom,4326) AS geometry,st_transform(c.geom,4326) AS cityBounds 
FROM toyproject."CBSAs" AS c
INNER JOIN toyproject.fortune AS f
ON(st_intersects(c.geom,st_transform(f.geom,4269)))
WHERE c.name='Houston-The Woodlands, TX'

The output query table returns 18 companies located within the metropolitan area.

Screen Shot 2021-03-27 at 6.45.37 PM.png

If you click the sky-blue button at the geometry column, the Geometry Viewer pops out.

Screen Shot 2021-03-27 at 6.49.12 PM.png

When I zoom in, Screen Shot 2021-03-27 at 6.49.40 PM.png You'll find that only companies in the Houston metro area are shown.

I also show another geometry column selected from the CBSAs table.

Screen Shot 2021-03-27 at 7.01.40 PM.png

No Comments Yet