ST_Intersection not returning all intersecting geometries

Question

I have a query that will calculate the proportions of given columns based on the area of intersection. The returns mostly the polygons entirely inside the other polygon, and in some cases returns the ones that intersect. Why is it not returning polygons entirely inside AND those bisected by the boundaries of another?

SELECT
    ROW_NUMBER() OVER () AS my_new_id,
    st_intersection(a.geom, b.geom)
into newlayer
from mass_pop_censustract as a, mass_regions as b

EDITED: The query should return the bright green plus the areas in the blue hatch (right picture). Essentially, I want to calculate the proportion of the population that lives in the portion of the census tracts (yellow) that falls in my area of interest (light green, left picture.)

Answer 1

Following my comment above, here's a suggestion to get the areal proportion of those intersections directly, to then be used in further steps, instead of your initial workflow. Might help, if that is actually what you want.

Since you didn't mention the data's CRS I'll assume WGS84 (EPSG:4326) and use a cast to geography type to derive measurements based on spheroidal algebra and in metric units.
I'll also assume the census tract data has an id, and a pop column with each tracts population.

Running

WITH
  its AS (
    SELECT ct.id,
           ct.pop,
           ST_Area(ct.geom::geography) AS area,
           ST_Area(ST_Intersection(ct.geom, rg.geom)::geography) AS prop_area
    FROM <census> AS ct
    JOIN <region> AS rg
      ON ST_Intersects(ct.geom, rg.geom)
  )

SELECT id,
       prop_area / 1000 AS prop_area_sqkm,
       prop_area / area AS prop_area_pct,
       pop * (prop_area / area) AS prop_pop
FROM its;

will calculate prop_pop as population proportion for each census tract id based on percentage of the area intersecting the ROI.

If you need to join this with other data holding population numbers, the query returns the respective intersection area prop_area_sqkm (if you are working with e.g. population density), and that area's proportion prop_area_pct in percentage (for e.g. external total population data).

Does that help?

Answer 2

the two queries below should get you what you need. you need to use the st_intersects to spatially filter the records that overlap. (I believe st_intersects first checks for bounding box intersections)

drop table if exists new_layer;
create table new_layer as
SELECT
    ROW_NUMBER() OVER () AS my_new_id,
    st_intersection(a.geom, b.geom)
from mass_pop_censustract as a join mass_regions as b
on st_intersects(a.geom, b.geom)

or this variation is sometimes useful

drop table if exists new_layer;
create table new_layer as
SELECT
    ROW_NUMBER() OVER () AS my_new_id,
    st_intersection(a.geom, b.geom)
from mass_pop_censustract as a join mass_regions as b
on st_dwithin(a.geom, b.geom,0.001)