Metropolitan Statistical Areas (MSAs) are critical geographic designations used by the U.S. Census Bureau to define urban regions for economic and demographic analysis. If you’re working with location-based data in PostgreSQL, PostGIS provides powerful tools for storing, querying, and analyzing MSA boundaries and associated data. In this guide, I’ll show you how to effectively work with MSA data using PostgreSQL and PostGIS.

Understanding MSA Data

Before diving into the technical implementation, it’s important to understand what MSA data represents. The U.S. Office of Management and Budget (OMB) defines MSAs as geographic areas containing a substantial population nucleus and adjacent communities that have a high degree of economic and social integration.

MSAs are used for:

• Economic analysis and reporting
• Market research and business intelligence
• Demographic studies
• Real estate and location analytics
• Government policy and planning

Typically, MSA boundaries are defined by polygon geometries that include multiple counties or county equivalents. The U.S. Census Bureau provides this data through their TIGER/Line shapefiles, which we can import into PostgreSQL.

Setting Up PostGIS

First, ensure you have PostGIS installed in your PostgreSQL database. PostGIS is an extension that adds support for geographic objects and spatial queries.

CREATE EXTENSION IF NOT EXISTS postgis;

To verify your installation:

SELECT PostGIS_Version();

This should return the version information for your PostGIS installation. PostGIS 3.0 or later is recommended for optimal performance and feature support.

Creating the MSA Table Structure

Let’s create a table to store MSA data with appropriate columns for the geometry and metadata:

CREATE TABLE msa_boundaries (
id SERIAL PRIMARY KEY,
cbsa_code VARCHAR(5) NOT NULL,
msa_name VARCHAR(255) NOT NULL,
state_codes VARCHAR(100),
population INTEGER,
land_area_sqmi NUMERIC(12,2),
geom GEOMETRY(MULTIPOLYGON, 4326),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key points about this structure:

• cbsa_code: The Core Based Statistical Area code, a unique identifier assigned by OMB
• msa_name: The full name of the MSA (e.g., “Houston-The Woodlands-Sugar Land, TX”)
• state_codes: Comma-separated state abbreviations for multi-state MSAs
• geom: The spatial column using MULTIPOLYGON geometry type with SRID 4326 (WGS 84 coordinate system)

Creating Spatial Indexes

Spatial indexes are crucial for performance when working with geographic data:

CREATE INDEX idx_msa_geom ON msa_boundaries USING GIST (geom);
CREATE INDEX idx_msa_cbsa_code ON msa_boundaries (cbsa_code);
CREATE INDEX idx_msa_name ON msa_boundaries (msa_name);

The GIST (Generalized Search Tree) index dramatically improves the performance of spatial queries like intersections, containment checks, and distance calculations.

Importing MSA Shapefiles

The Census Bureau provides MSA boundaries as shapefiles. You can import these using the shp2pgsql command-line tool that comes with PostGIS:

shp2pgsql -I -s 4326 -W UTF-8 tl_2024_us_cbsa.shp msa_boundaries | psql -d your_database

The flags explained:

• -I: Create a spatial index
• -s 4326: Set the SRID to 4326 (WGS 84)
• -W UTF-8: Specify character encoding

Alternatively, you can use QGIS, ogr2ogr, or other GIS tools to import the data.

Basic Spatial Queries with MSA Data

Now that we have MSA data loaded, let’s explore some common queries.

Finding which MSA contains a specific point:

SELECT
msa_name,
cbsa_code,
population
FROM msa_boundaries
WHERE ST_Contains(
geom,
ST_SetSRID(ST_MakePoint(-95.3698, 29.7604), 4326)
);

This query checks if the coordinates for Houston, Texas fall within any MSA boundary.

Finding MSAs within a certain distance of a point:

SELECT
msa_name,
ST_Distance(
geom::geography,
ST_SetSRID(ST_MakePoint(-95.3698, 29.7604), 4326)::geography
) / 1609.34 AS distance_miles
FROM msa_boundaries
WHERE ST_DWithin(
geom::geography,
ST_SetSRID(ST_MakePoint(-95.3698, 29.7604), 4326)::geography,
160934 — 100 miles in meters
)
ORDER BY distance_miles;

Note the use of ::geography casting for accurate distance calculations on Earth’s surface.

Calculating MSA characteristics:

SELECT
msa_name,
population,
land_area_sqmi,
ROUND(population::numeric / land_area_sqmi, 2) AS population_density,
ST_Area(geom::geography) / 2589988.11 AS calculated_area_sqmi,
ST_Perimeter(geom::geography) / 1609.34 AS perimeter_miles
FROM msa_boundaries
WHERE population > 1000000
ORDER BY population DESC;

Joining MSA Data with Business Locations

A common use case is associating business locations or other point data with their corresponding MSAs:

CREATE TABLE business_locations (
id SERIAL PRIMARY KEY,
business_name VARCHAR(255),
address VARCHAR(255),
city VARCHAR(100),
state VARCHAR(2),
latitude NUMERIC(10, 7),
longitude NUMERIC(10, 7),
geom GEOMETRY(POINT, 4326)
);

Create a spatial index:

CREATE INDEX idx_business_geom ON business_locations USING GIST (geom);

Populate the geometry column from latitude/longitude:

UPDATE business_locations
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

Join businesses with their MSAs:

SELECT
b.business_name,
b.city,
b.state,
m.msa_name,
m.cbsa_code,
m.population AS msa_population
FROM business_locations b
LEFT JOIN msa_boundaries m ON ST_Contains(m.geom, b.geom);

This query identifies which MSA each business belongs to, or returns NULL for businesses outside any MSA.

Aggregating Data by MSA

You can aggregate business data by MSA to gain insights:

SELECT
m.msa_name,
m.population,
COUNT(b.id) AS business_count,
ROUND(COUNT(b.id)::numeric * 100000 / m.population, 2) AS businesses_per_100k
FROM msa_boundaries m
LEFT JOIN business_locations b ON ST_Contains(m.geom, b.geom)
WHERE m.population > 500000
GROUP BY m.msa_name, m.population
ORDER BY businesses_per_100k DESC;

Advanced Spatial Analysis

Finding overlapping or adjacent MSAs:

SELECT
a.msa_name AS msa_1,
b.msa_name AS msa_2,
ST_Touches(a.geom, b.geom) AS are_adjacent,
ST_Overlaps(a.geom, b.geom) AS overlap
FROM msa_boundaries a
CROSS JOIN msa_boundaries b
WHERE a.id < b.id
AND (ST_Touches(a.geom, b.geom) OR ST_Overlaps(a.geom, b.geom));

Calculating the centroid of an MSA:

SELECT
msa_name,
ST_Y(ST_Centroid(geom::geography)::geometry) AS centroid_latitude,
ST_X(ST_Centroid(geom::geography)::geometry) AS centroid_longitude
FROM msa_boundaries
WHERE msa_name LIKE ‘%Houston%’;

Creating buffers around MSA boundaries:

SELECT
msa_name,
ST_Buffer(geom::geography, 16093.4)::geometry AS buffer_10_miles
FROM msa_boundaries
WHERE msa_name = ‘Houston-The Woodlands-Sugar Land, TX’;

Performance Optimization Tips

Working with large geographic datasets requires attention to performance:

1. Use spatial indexes: Always create GIST indexes on geometry columns
2. Cast to geography for distance: Use ::geography casting when measuring real-world distances, but note it’s slower than geometry calculations
3. Simplify geometries when appropriate:

SELECT
msa_name,
ST_Simplify(geom, 0.01) AS simplified_geom
FROM msa_boundaries;

4. Use bounding box queries first:

SELECT msa_name
FROM msa_boundaries
WHERE geom && ST_MakeEnvelope(-96, 29, -95, 30, 4326)
AND ST_Contains(geom, ST_SetSRID(ST_MakePoint(-95.3698, 29.7604), 4326));

The && operator checks bounding box intersection first (very fast), then the more expensive ST_Contains only on candidates.

5. Vacuum and analyze regularly:

VACUUM ANALYZE msa_boundaries;

Integrating with Application Development

When building applications with MSA data, consider these patterns:

Creating a function to get MSA by coordinates:

CREATE OR REPLACE FUNCTION get_msa_by_coordinates(
lon NUMERIC,
lat NUMERIC
)
RETURNS TABLE (
msa_name VARCHAR,
cbsa_code VARCHAR,
population INTEGER
) AS $$
BEGIN
RETURN QUERY
SELECT
m.msa_name,
m.cbsa_code,
m.population
FROM msa_boundaries m
WHERE ST_Contains(
m.geom,
ST_SetSRID(ST_MakePoint(lon, lat), 4326)
)
LIMIT 1;
END;
$$ LANGUAGE plpgsql;

Usage:

SELECT * FROM get_msa_by_coordinates(-95.3698, 29.7604);

Exporting MSA Data

You can export MSA data to various formats for use in other applications:

As GeoJSON:

SELECT
jsonb_build_object(
‘type’, ‘FeatureCollection’,
‘features’, jsonb_agg(
jsonb_build_object(
‘type’, ‘Feature’,
‘properties’, jsonb_build_object(
‘name’, msa_name,
‘cbsa_code’, cbsa_code,
‘population’, population
),
‘geometry’, ST_AsGeoJSON(geom)::jsonb
)
)
)
FROM msa_boundaries
WHERE state_codes LIKE ‘%TX%’;

Real-World Use Cases

MSA data combined with PostGIS enables powerful applications:

1. Market Analysis: Identify target markets by analyzing demographic data within MSA boundaries
2. Store Location Planning: Find optimal locations for new retail stores based on MSA population and competition density
3. Service Area Mapping: Determine which MSAs your business serves and identify expansion opportunities
4. Economic Research: Analyze economic indicators across metropolitan regions
5. Real Estate Analytics: Assess property values and trends within specific MSAs

Maintaining Current Data

MSA boundaries are periodically updated by the OMB. Create a process to refresh your data:

1. Download the latest TIGER/Line shapefiles from the Census Bureau
2. Import into a temporary table
3. Compare with existing data
4. Update or replace as needed

Consider creating a metadata table to track data versions:

CREATE TABLE msa_metadata (
id SERIAL PRIMARY KEY,
version VARCHAR(50),
source_url TEXT,
import_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
record_count INTEGER
);

Conclusion

PostGIS provides robust capabilities for working with MSA data in PostgreSQL. By leveraging spatial indexes, proper geometry types, and PostGIS functions, you can build powerful location-based applications that analyze metropolitan areas efficiently.

The combination of MSA boundaries with business locations, demographic data, and other spatial information enables sophisticated geographic analysis that would be difficult or impossible with traditional relational database queries alone.

Whether you’re building market analysis tools, location-based services, or economic research platforms, PostGIS and PostgreSQL provide the foundation for working with MSA data at scale. Start with the basics, experiment with spatial queries, and you’ll discover increasingly powerful ways to extract insights from geographic data.