Hello,
I have a SpatiaLite junction table, created from three other tables, that updates with a trigger, but am struggling with how to properly implement the model for this table as well as a model for a table that uses fields within the junction table. Can someone please sanity check my thinking and possibly provide recommendations for how to build these models?
I create my junction table with:
conn = sqlite3.connect(db)
conn.enable_load_extension(True)
conn.execute("SELECT load_extension('mod_spatialite')")
c = conn.cursor()
c.execute('''
CREATE TABLE etl AS
SELECT 'EE' AS table_name,
e.aoi_id_id AS aoi_id,
e.catalog_id AS id,
e.vendor_id AS vendor_id,
e.entity_id AS entity_id,
e.vendor as vendor,
e.pixel_size_x AS pixel_size_x,
e.pixel_size_y AS pixel_size_y,
e.acquisition_date AS date,
Date(e.publish_date) AS publish_date,
AsText(e.bounds) as geometry
FROM whale_ee e
UNION
SELECT 'GEGD' AS table_name,
g.id AS aoi_id,
g.legacy_id AS id,
NULL AS vendor_id,
NULL AS entity_id,
g.company_name as vendor,
g.per_pixel_x AS pixel_size_x,
g.per_pixel_y AS pixel_size_y,
Date(g.acquisition_date) AS date,
NULL AS publish_date,
AsText(g.geometry) AS geometry
FROM whale_gegd g
LEFT JOIN whale_ee e ON g.legacy_id = e.catalog_id
WHERE
e.catalog_id IS NULL
UNION
SELECT 'MGP' AS table_name,
m.id AS aoi_id,
m.id AS id,
NULL AS vendor_id,
NULL AS entity_id,
m.platform AS platform,
m.gsd AS pixel_size_x,
m.gsd AS pixel_size_y,
Date(datetime) AS date,
NULL AS publish_date,
AsText(m.bbox) AS geometry
FROM whale_mgp m
LEFT JOIN whale_ee e ON m.id = e.catalog_id
LEFT JOIN whale_gegd g ON m.id = g.legacy_id
WHERE
e.catalog_id IS NULL AND g.legacy_id IS NULL;
''')
conn.commit()
conn.close()
I have validated that the table looks and functions as expected by querying it and plotting results on a map. I have created a model that looks like:
class ExtractTransformLoad(gis_models.Model):
ee = gis_models.ForeignKey(EarthExplorer, on_delete=gis_models.CASCADE)
gegd = gis_models.ForeignKey(GEOINTDiscovery, on_delete=gis_models.CASCADE)
mgp = gis_models.ForeignKey(MaxarGeospatialPlatform, on_delete=gis_models.CASCADE)
class Meta:
db_table = 'etl'
managed = False
I am using ForeignKey after reading online that this can be used to reference an entire table opposed to just a field, but feel like I’m misunderstanding something here since I thought Foreign Keys were supposed to be column, or attribute, based. Am I misunderstanding this concept?
Secondly, I have built a table from the junction table using the aoi_id, id, vendor_id, and entity_id in this second table, but I have no idea how to reference this relationship within the model since the junction table uses tables as ForeignKeys. Has anyone attempted this before and do you have documentation on how you implimented your model?