Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE grid_cell_data (
           id SERIAL PRIMARY KEY,
           isTreatment boolean,
           isBuffer boolean,
           fourCornersRepresentativeToTreatmentAsGeoJSON text,
           fourCornersRepresentativeToBufferAsGeoJSON text,
           distanceFromCenterPointOfTreatmentToNearestEdge numeric,
           distanceFromCenterPointOfBufferToNearestEdge numeric,
           areasOfCoveragePerWindowForCellsRepresentativeToTreatment numeric,
           areasOfCoveragePerWindowForCellsRepresentativeToBuffer numeric,
           averageHeightsPerWindowRepresentativeToTreatment numeric,
           averageHeightsPerWindowRepresentativeToBuffer numeric,
           geometryOfCellRepresentativeToTreatment geometry(POINT,4326),
           geometryOfCellRepresentativeToBuffer geometry(POINT,4326));

INSERT INTO grid_cell_data (fourCornersRepresentativeToTreatmentAsGeoJSON,fourCornersRepresentativeToBufferAsGeoJSON) VALUES 
(ST_AsGeoJSON('POINT(1 2)'::geometry),ST_AsGeoJSON('POINT(1 2)'::geometry)),
(ST_AsGeoJSON('POINT(1 2)'::geometry),NULL),
(NULL,ST_AsGeoJSON('POINT(1 2)'::geometry));
3 rows affected

-- Option 1
UPDATE grid_cell_data SET
  geometryOfCellRepresentativeToTreatment = ST_GeomFromGeoJSON(fourCornersRepresentativeToTreatmentAsGeoJSON),
  geometryOfCellRepresentativeToBuffer = ST_GeomFromGeoJSON(fourCornersRepresentativeToBufferAsGeoJSON)
WHERE 
  fourCornersRepresentativeToTreatmentAsGeoJSON IS NOT NULL AND
  fourCornersRepresentativeToBufferAsGeoJSON IS NOT NULL;
1 rows affected

SELECT 
geometryOfCellRepresentativeToTreatment,fourCornersRepresentativeToTreatmentAsGeoJSON,
geometryOfCellRepresentativeToBuffer,fourCornersRepresentativeToBufferAsGeoJSON
FROM grid_cell_data;  


geometryofcellrepresentativetotreatment
fourcornersrepresentativetotreatmentasgeojson
geometryofcellrepresentativetobuffer
fourcornersrepresentativetobufferasgeojson


null
{"type":"Point","coordinates":[1,2]}
null
null

null
null
null
{"type":"Point","coordinates":[1,2]}

0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}
0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}


TRUNCATE grid_cell_data;
INSERT INTO grid_cell_data (fourCornersRepresentativeToTreatmentAsGeoJSON,fourCornersRepresentativeToBufferAsGeoJSON) VALUES 
(ST_AsGeoJSON('POINT(1 2)'::geometry),ST_AsGeoJSON('POINT(1 2)'::geometry)),
(ST_AsGeoJSON('POINT(1 2)'::geometry),NULL),
(NULL,ST_AsGeoJSON('POINT(1 2)'::geometry));
3 rows affected

-- Option 2
UPDATE grid_cell_data SET
  geometryOfCellRepresentativeToTreatment = ST_GeomFromGeoJSON(fourCornersRepresentativeToTreatmentAsGeoJSON)
WHERE 
  fourCornersRepresentativeToTreatmentAsGeoJSON IS NOT NULL;

UPDATE grid_cell_data SET
  geometryOfCellRepresentativeToBuffer = ST_GeomFromGeoJSON(fourCornersRepresentativeToBufferAsGeoJSON)
WHERE 
  fourCornersRepresentativeToBufferAsGeoJSON IS NOT NULL;
2 rows affected

2 rows affected

SELECT 
geometryOfCellRepresentativeToTreatment,fourCornersRepresentativeToTreatmentAsGeoJSON,
geometryOfCellRepresentativeToBuffer,fourCornersRepresentativeToBufferAsGeoJSON
FROM grid_cell_data;  


geometryofcellrepresentativetotreatment
fourcornersrepresentativetotreatmentasgeojson
geometryofcellrepresentativetobuffer
fourcornersrepresentativetobufferasgeojson


0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}
null
null

null
null
0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}

0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}
0101000020E6100000000000000000F03F0000000000000040
{"type":"Point","coordinates":[1,2]}

geometryofcellrepresentativetotreatment	fourcornersrepresentativetotreatmentasgeojson	geometryofcellrepresentativetobuffer	fourcornersrepresentativetobufferasgeojson
null	{"type":"Point","coordinates":[1,2]}	null	null
null	null	null	{"type":"Point","coordinates":[1,2]}
0101000020E6100000000000000000F03F0000000000000040	{"type":"Point","coordinates":[1,2]}	0101000020E6100000000000000000F03F0000000000000040	{"type":"Point","coordinates":[1,2]}