Add quadkey index

Index on quadkey because we will need to do some (expensive) table joins in views

\approve @ds @gislars

requested review from @gislars

assigned to @ds

@gislars This will be the view created. If you see any way to optimize let me know:

CREATE VIEW tile_best_estimate_2 AS
SELECT BE.quadkey, BE.geometry, BE.best_estimate[0] as completeness, 
BE.best_estimate[1] as built_area_size, BE.best_estimate[2] as built_up_ratio

FROM (SELECT obm_tiles.quadkey, obm_tiles.geometry, 
		CASE 
			WHEN manual.completeness = 1 THEN ARRAY[manual.completeness, osm.built_area_size, osm.built_up_ratio]
			WHEN manual.completeness = 0 THEN ARRAY[manual.completeness, ghsl.built_area_size, ghsl.built_up_ratio]
			WHEN ghsl.completeness = 1 THEN ARRAY[ghsl.completeness, osm.built_area_size, osm.built_up_ratio]
			ELSE ARRAY[ghsl.completeness,ghsl.built_area_size,ghsl.built_up_ratio]
		END AS best_estimate
	FROM obm_tiles
	LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 0) AS manual ON manual.quadkey = obm_tiles.quadkey
	LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 1) AS osm ON osm.quadkey = obm_tiles.quadkey
	LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 2) AS ghsl ON ghsl.quadkey = obm_tiles.quadkey
			   ) as BE

Better:

CREATE VIEW tile_best_estimate AS
SELECT obm_tiles.quadkey, obm_tiles.geometry, 

	CASE 
		WHEN manual.completeness IS NULL THEN ghsl.completeness
		ELSE manual.completeness
	END 
        AS completeness,

	CASE 
		WHEN manual.completeness = 1 THEN osm.built_area_size
		WHEN manual.completeness = 0 THEN ghsl.built_area_size
		WHEN ghsl.completeness = 1 THEN osm.built_area_size
		ELSE ghsl.built_area_size
	END
        AS built_area_size,

	CASE 
		WHEN manual.completeness = 1 THEN osm.built_up_ratio
		WHEN manual.completeness = 0 THEN ghsl.built_up_ratio
		WHEN ghsl.completeness = 1 THEN osm.built_up_ratio
		ELSE ghsl.built_up_ratio
	END
        AS built_up_ratio

FROM obm_tiles
LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 2) AS manual ON manual.quadkey = obm_tiles.quadkey
LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 0) AS osm ON osm.quadkey = obm_tiles.quadkey
LEFT JOIN (SELECT quadkey, completeness, built_area_size, built_up_ratio
			   FROM obm_built_area_assessments
			   WHERE source_id = 1) AS ghsl ON ghsl.quadkey = obm_tiles.quadkey

added 1 commit

• 12cd4bdb - Add quadkey index

Compare with previous version

added 1 commit

• 52aea443 - Add quadkey index

Compare with previous version

approved this merge request

added 1 commit

• 5b1fced6 - Add quadkey index

Compare with previous version

approved this merge request

merged