Remove duplication in subdivided SQL

[chris48s]

Refs #2343 (review)

We've got a couple of places where there are several similar chunks of SQL

EveryElection/every_election/apps/organisations/models/divisions.py

Lines 176 to 216 in f8b745a

	def save(self, *args, **kwargs):
	super().save(*args, **kwargs)
	self.subdivided.all().delete()
	sql = """
	INSERT INTO organisations_divisiongeographysubdivided (geography, division_geography_id)
	SELECT st_subdivide(geography) as geography, id as division_geography_id
	FROM organisations_divisiongeography dg
	WHERE dg.id=%s;
	"""
	with connection.cursor() as cursor:
	cursor.execute(sql, [self.id])
	class DivisionGeographySubdivided(models.Model):
	geography = models.PolygonField(db_index=True, spatial_index=True)
	division_geography = models.ForeignKey(
	DivisionGeography,
	on_delete=models.CASCADE,
	related_name="subdivided",
	)
	POPULATE_SQL = """
	TRUNCATE organisations_divisiongeographysubdivided;
	INSERT INTO organisations_divisiongeographysubdivided (geography, division_geography_id)
	SELECT st_subdivide(geography) as geography, id as division_geography_id
	FROM organisations_divisiongeography;
	"""
	POPULATE_WHERE_MISSING_SQL = """
	WITH missing_subdivided_geography AS (
	SELECT odg.id
	FROM organisations_divisiongeography odg
	LEFT JOIN organisations_divisiongeographysubdivided odgs
	ON odg.id = odgs.division_geography_id
	WHERE odgs.id IS NULL
	)
	INSERT INTO organisations_divisiongeographysubdivided (geography, division_geography_id)
	SELECT st_subdivide(geography) as geography, id as division_geography_id
	FROM organisations_divisiongeography dg
	WHERE dg.id IN (SELECT id FROM missing_subdivided_geography);
	"""

EveryElection/every_election/apps/organisations/models/organisations.py

Lines 155 to 216 in f8b745a

	@transaction.atomic
	def save(self, *args, **kwargs):
	self.check_start_date()
	self.check_end_date()
	super().save(*args, **kwargs)
	self.subdivided.all().delete()
	sql = """
	INSERT INTO organisations_organisationgeographysubdivided (geography, organisation_geography_id)
	SELECT st_subdivide(geography) as geography, id as division_geography_id
	FROM organisations_organisationgeography og
	WHERE og.id=%s;
	"""
	with connection.cursor() as cursor:
	cursor.execute(sql, [self.id])
	class Meta:
	verbose_name_plural = "Organisation Geographies"
	ordering = ("-start_date",)
	get_latest_by = "start_date"
	unique_together = (
	("organisation", "start_date"),
	("organisation", "end_date"),
	)
	"""
	Note:
	This model also has an additional constraint to prevent
	overlapping start and end dates (but allows both to be NULL).
	This is defined in
	organisations/migrations/0040_end_date_constraint.py
	"""
	class OrganisationGeographySubdivided(models.Model):
	geography = models.PolygonField(db_index=True, spatial_index=True)
	organisation_geography = models.ForeignKey(
	OrganisationGeography,
	on_delete=models.CASCADE,
	related_name="subdivided",
	)
	POPULATE_SQL = """
	TRUNCATE organisations_organisationgeographysubdivided;
	INSERT INTO organisations_organisationgeographysubdivided (geography, organisation_geography_id)
	SELECT st_subdivide(geography) as geography, id as organisation_geography_id
	FROM organisations_organisationgeography;
	"""
	POPULATE_WHERE_MISSING_SQL = """
	WITH missing_subdivided_geography AS (
	SELECT og.id
	FROM organisations_organisationgeography og
	LEFT JOIN organisations_organisationgeographysubdivided ogs
	ON og.id = ogs.organisation_geography_id
	WHERE ogs.id IS NULL
	)
	INSERT INTO organisations_organisationgeographysubdivided (geography, organisation_geography_id)
	SELECT st_subdivide(geography) as geography, id as division_geography_id
	FROM organisations_organisationgeography og
	WHERE og.id IN (SELECT id FROM missing_subdivided_geography);
	"""

One option would be to try and abstract out the common part.

That said, the only place where we are using the constants at all is in
https://github.com/DemocracyClub/EveryElection/blob/master/every_election/apps/organisations/management/commands/populate_subdivided_tables.py

Now that we should be populating the subdivided geographies on save, do we need the management command any more? Lets see how #2343 beds in first..