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.

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'

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

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.

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.

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

When I zoom in, You'll find that only companies in the Houston metro area are shown.

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