Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: ead78f21 by Tony Chemit at 2023-06-25T17:19:52+02:00 Amélioration des triggers postgis - Closes #2745 - - - - - 5 changed files: - + client/runner/src/main/assembly/dist/scripts/postgresql/drop-postgis.sql - client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_0_create-postgis-functions.sql - client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_1_create-postgis-referential.sql - client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_2_create-postgis-ps.sql - client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_3_create-postgis-ll.sql Changes: ===================================== client/runner/src/main/assembly/dist/scripts/postgresql/drop-postgis.sql ===================================== @@ -0,0 +1,17 @@ +ALTER table common.Harbour DROP COLUMN the_geom CASCADE; +ALTER TABLE ps_observation.activity DROP COLUMN the_geom CASCADE; +ALTER TABLE ps_observation.transmittingbuoy DROP COLUMN the_geom CASCADE; +ALTER TABLE ps_logbook.activity DROP COLUMN the_geom CASCADE; +ALTER TABLE ps_logbook.activity DROP COLUMN the_geom_original CASCADE; +ALTER TABLE ps_logbook.transmittingbuoy DROP COLUMN the_geom CASCADE; +ALTER TABLE ll_observation.activity DROP COLUMN the_geom CASCADE; +ALTER TABLE ll_observation.set DROP COLUMN the_geom_settingstart CASCADE; +ALTER TABLE ll_observation.set DROP COLUMN the_geom_settingend CASCADE; +ALTER TABLE ll_observation.set DROP COLUMN the_geom_haulingstart CASCADE; +ALTER TABLE ll_observation.set DROP COLUMN the_geom_haulingend CASCADE; +ALTER TABLE ll_logbook.activity DROP COLUMN the_geom CASCADE; +ALTER TABLE ll_logbook.set DROP COLUMN the_geom_settingstart CASCADE; +ALTER TABLE ll_logbook.set DROP COLUMN the_geom_settingend CASCADE; +ALTER TABLE ll_logbook.set DROP COLUMN the_geom_haulingstart CASCADE; +ALTER TABLE ll_logbook.set DROP COLUMN the_geom_haulingend CASCADE; +ALTER TABLE ll_landing.landing DROP COLUMN the_geom CASCADE; ===================================== client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_0_create-postgis-functions.sql ===================================== @@ -41,7 +41,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - latitude % and longitude %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.latitude, NEW.longitude; - NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude, NEW.latitude), 4326); + NEW.the_geom := ST_SetSRID(ST_MakePoint(NEW.longitude::double precision, NEW.latitude::double precision), 4326); RAISE NOTICE 'Computed for %.% % latitude % and longitude %, the_geom %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.latitude, NEW.longitude, NEW.the_geom; RETURN NEW; END @@ -69,7 +69,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - latitudeOriginal % and longitudeOriginal %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.latitudeOriginal, NEW.longitudeOriginal; - NEW.the_geom_original := ST_SetSRID(ST_MakePoint(NEW.longitudeOriginal, NEW.latitudeOriginal), 4326); + NEW.the_geom_original := ST_SetSRID(ST_MakePoint(NEW.longitudeOriginal::double precision, NEW.latitudeOriginal::double precision), 4326); RAISE NOTICE 'Computed for %.% % latitudeOriginal % and longitudeOriginal %, the_geom_original %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.latitudeOriginal, NEW.longitudeOriginal, NEW.the_geom_original; RETURN NEW; END @@ -98,7 +98,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - settingStartLatitude % and settingStartLongitude %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.settingStartLatitude, NEW.settingStartLongitude; - NEW.the_geom_settingStart := ST_SetSRID(ST_MakePoint(NEW.settingStartLongitude, NEW.settingStartLatitude), 4326); + NEW.the_geom_settingStart := ST_SetSRID(ST_MakePoint(NEW.settingStartLongitude::double precision, NEW.settingStartLatitude::double precision), 4326); RAISE NOTICE 'Computed for %.% % settingStartLatitude % and settingStartLongitude %, the_geom_settingStart %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.settingStartLatitude, NEW.settingStartLongitude, NEW.the_geom_settingStart; RETURN NEW; END @@ -126,7 +126,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - settingEndLatitude % and settingEndLongitude %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.settingEndLatitude, NEW.settingEndLongitude; - NEW.the_geom_settingEnd := ST_SetSRID(ST_MakePoint(NEW.settingEndLongitude, NEW.settingEndLatitude), 4326); + NEW.the_geom_settingEnd := ST_SetSRID(ST_MakePoint(NEW.settingEndLongitude::double precision, NEW.settingEndLatitude::double precision), 4326); RAISE NOTICE 'Computed for %.% % settingEndLatitude % and settingEndLongitude %, the_geom_settingEnd %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.settingEndLatitude, NEW.settingEndLongitude, NEW.the_geom_settingEnd; RETURN NEW; END @@ -154,7 +154,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - haulingStartLatitude % and haulingStartLongitude %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.haulingStartLatitude, NEW.haulingStartLongitude; - NEW.the_geom_haulingStart := ST_SetSRID(ST_MakePoint(NEW.haulingStartLongitude, NEW.haulingStartLatitude), 4326); + NEW.the_geom_haulingStart := ST_SetSRID(ST_MakePoint(NEW.haulingStartLongitude::double precision, NEW.haulingStartLatitude::double precision), 4326); RAISE NOTICE 'Computed for %.% % haulingStartLatitude % and haulingStartLongitude %, the_geom_haulingStart %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.haulingStartLatitude, NEW.haulingStartLongitude, NEW.the_geom_haulingStart; RETURN NEW; END @@ -182,7 +182,7 @@ BEGIN return NEW; END IF; RAISE NOTICE 'Will compute the_geom for %.% % - haulingEndLatitude % and haulingEndLongitude %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.haulingEndLatitude, NEW.haulingEndLongitude; - NEW.the_geom_haulingEnd := ST_SetSRID(ST_MakePoint(NEW.haulingEndLongitude, NEW.haulingEndLatitude), 4326); + NEW.the_geom_haulingEnd := ST_SetSRID(ST_MakePoint(NEW.haulingEndLongitude::double precision, NEW.haulingEndLatitude::double precision), 4326); RAISE NOTICE 'Computed for %.% % haulingEndLatitude % and haulingEndLongitude %, the_geom_haulingEnd %', TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW.topiaId, NEW.haulingEndLatitude, NEW.haulingEndLongitude, NEW.the_geom_haulingEnd; RETURN NEW; END ===================================== client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_1_create-postgis-referential.sql ===================================== @@ -19,9 +19,8 @@ -- <http://www.gnu.org/licenses/gpl-3.0.html>. -- #L% --- - -SELECT AddGeometryColumn('common', 'harbour', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE common.harbour ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_harbour_gist ON common.harbour USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_common_harbour_the_geom ON common.harbour; CREATE TRIGGER tr_sync_common_harbour_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON common.harbour FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE common.harbour SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; +UPDATE common.harbour SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision,latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; ===================================== client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_2_create-postgis-ps.sql ===================================== @@ -20,33 +20,33 @@ -- #L% --- -SELECT AddGeometryColumn('ps_observation', 'activity', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ps_observation.activity ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ps_observation_activity_gist ON ps_observation.activity USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ps_observation_activity_the_geom ON ps_observation.activity; CREATE TRIGGER tr_sync_ps_observation_activity_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ps_observation.activity FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ps_observation.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; +UPDATE ps_observation.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision,latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; -SELECT AddGeometryColumn('ps_observation', 'transmittingbuoy', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ps_observation.transmittingbuoy ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ps_observation_transmittingbuoy_gist ON ps_observation.TransmittingBuoy USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ps_observation_transmittingbuoy_the_geom ON ps_observation.TransmittingBuoy; CREATE TRIGGER tr_sync_ps_observation_transmittingbuoy_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ps_observation.TransmittingBuoy FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ps_observation.TransmittingBuoy SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; +UPDATE ps_observation.TransmittingBuoy SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision, latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; -SELECT AddGeometryColumn('ps_logbook' ,'activity', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ps_logbook.activity ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ps_logbook_activity_gist ON ps_logbook.activity USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ps_logbook_activity_the_geom ON ps_logbook.activity; CREATE TRIGGER tr_sync_ps_logbook_activity_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ps_logbook.activity FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ps_logbook.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; +UPDATE ps_logbook.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision,latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; -SELECT AddGeometryColumn('ps_logbook','activity', 'the_geom_original', 4326, 'POINT', 2); +ALTER TABLE ps_logbook.activity ADD COLUMN the_geom_original geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ps_logbook_activity_original_gist ON ps_logbook.activity USING GIST (the_geom_original); DROP TRIGGER IF EXISTS tr_sync_ps_logbook_activity_the_geom_original ON ps_logbook.activity; CREATE TRIGGER tr_sync_ps_logbook_activity_the_geom_original BEFORE INSERT OR UPDATE OF latitudeOriginal, longitudeOriginal ON ps_logbook.activity FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_original(); -UPDATE ps_logbook.activity SET the_geom_original = ST_SetSRID(ST_MakePoint(latitudeOriginal, longitudeOriginal), 4326) WHERE latitudeOriginal IS NOT NULL AND longitudeOriginal IS NOT NULL AND the_geom_original IS NULL; +UPDATE ps_logbook.activity SET the_geom_original = ST_SetSRID(ST_MakePoint(latitudeOriginal::double precision, longitudeOriginal::double precision), 4326) WHERE latitudeOriginal IS NOT NULL AND longitudeOriginal IS NOT NULL AND the_geom_original IS NULL; -SELECT AddGeometryColumn('ps_logbook', 'transmittingbuoy', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ps_logbook.transmittingbuoy ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ps_logbook_transmittingbuoy_gist ON ps_logbook.TransmittingBuoy USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ps_logbook_transmittingbuoy_the_geom ON ps_logbook.TransmittingBuoy; CREATE TRIGGER tr_sync_ps_logbook_transmittingbuoy_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ps_logbook.TransmittingBuoy FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ps_logbook.TransmittingBuoy SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; +UPDATE ps_logbook.TransmittingBuoy SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision, latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; ===================================== client/runner/src/main/assembly/dist/scripts/postgresql/extra/01_3_create-postgis-ll.sql ===================================== @@ -20,72 +20,72 @@ -- #L% --- -SELECT AddGeometryColumn('ll_observation', 'activity', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ll_observation.activity ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_observation_activity_gist ON ll_observation.activity USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ll_observation_activity_the_geom ON ll_observation.activity; CREATE TRIGGER tr_sync_ll_observation_activity_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ll_observation.activity FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ll_observation.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) AND the_geom IS NULL; +UPDATE ll_observation.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision, latitude::double precision), 4326) WHERE the_geom IS NULL; -SELECT AddGeometryColumn('ll_observation', 'set', 'the_geom_settingstart', 4326, 'POINT', 2); +ALTER TABLE ll_observation.set ADD COLUMN the_geom_settingstart geometry(Point, 4326); DROP INDEX IF EXISTS idx_ll_observation_set_gist_settingstart; CREATE INDEX IF NOT EXISTS idx_ll_observation_set_gist_settingstart ON ll_observation.set USING GIST (the_geom_settingstart); DROP TRIGGER IF EXISTS tr_sync_ll_observation_set_the_geom_settingstart ON ll_observation.set; CREATE TRIGGER tr_sync_ll_observation_set_the_geom_settingstart BEFORE INSERT OR UPDATE OF settingStartLatitude, settingStartLongitude ON ll_observation.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_settingStart(); -UPDATE ll_observation.set SET the_geom_settingstart = ST_SetSRID(ST_MakePoint(settingStartLongitude, settingStartLatitude), 4326) AND the_geom_settingstart IS NULL; +UPDATE ll_observation.set SET the_geom_settingstart = ST_SetSRID(ST_MakePoint(settingStartLongitude::double precision, settingStartLatitude::double precision), 4326) WHERE the_geom_settingstart IS NULL; -SELECT AddGeometryColumn('ll_observation', 'set', 'the_geom_settingend', 4326, 'POINT', 2); +ALTER TABLE ll_observation.set ADD COLUMN the_geom_settingend geometry(Point, 4326); DROP INDEX IF EXISTS idx_ll_observation_set_gist_settingend; CREATE INDEX IF NOT EXISTS idx_ll_observation_set_gist_settingend ON ll_observation.set USING GIST (the_geom_settingend); DROP TRIGGER IF EXISTS tr_sync_ll_observation_set_the_geom_settingend ON ll_observation.set; CREATE TRIGGER tr_sync_ll_observation_set_the_geom_settingend BEFORE INSERT OR UPDATE OF settingEndLatitude, settingEndLongitude ON ll_observation.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_settingEnd(); -UPDATE ll_observation.set SET the_geom_settingend = ST_SetSRID(ST_MakePoint(settingEndLongitude, settingEndLatitude), 4326) AND the_geom_settingend IS NULL; +UPDATE ll_observation.set SET the_geom_settingend = ST_SetSRID(ST_MakePoint(settingEndLongitude::double precision, settingEndLatitude::double precision), 4326) WHERE the_geom_settingend IS NULL; -SELECT AddGeometryColumn('ll_observation', 'set', 'the_geom_haulingstart', 4326, 'POINT', 2); +ALTER TABLE ll_observation.set ADD COLUMN the_geom_haulingstart geometry(Point, 4326); DROP INDEX IF EXISTS idx_ll_observation_set_gist_haulingstart; CREATE INDEX IF NOT EXISTS idx_ll_observation_set_gist_haulingstart ON ll_observation.set USING GIST (the_geom_haulingstart); DROP TRIGGER IF EXISTS tr_sync_ll_observation_set_the_geom_haulingstart ON ll_observation.set; CREATE TRIGGER tr_sync_ll_observation_set_the_geom_haulingstart BEFORE INSERT OR UPDATE OF haulingStartLatitude, haulingStartLongitude ON ll_observation.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_haulingStart(); -UPDATE ll_observation.set SET the_geom_haulingstart = ST_SetSRID(ST_MakePoint(haulingStartLongitude, haulingStartLatitude), 4326) AND the_geom_haulingstart IS NULL; +UPDATE ll_observation.set SET the_geom_haulingstart = ST_SetSRID(ST_MakePoint(haulingStartLongitude::double precision, haulingStartLatitude::double precision), 4326) WHERE the_geom_haulingstart IS NULL; -SELECT AddGeometryColumn('ll_observation', 'set', 'the_geom_haulingend', 4326, 'POINT', 2); +ALTER TABLE ll_observation.set ADD COLUMN the_geom_haulingend geometry(Point, 4326); DROP INDEX IF EXISTS idx_ll_observation_set_gist_haulingend; CREATE INDEX IF NOT EXISTS idx_ll_observation_set_gist_haulingend ON ll_observation.set USING GIST (the_geom_haulingend); DROP TRIGGER IF EXISTS tr_sync_ll_observation_set_the_geom_haulingend ON ll_observation.set; CREATE TRIGGER tr_sync_ll_observation_set_the_geom_haulingend BEFORE INSERT OR UPDATE OF haulingEndLatitude, haulingEndLongitude ON ll_observation.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_haulingEnd(); -UPDATE ll_observation.set SET the_geom_haulingend = ST_SetSRID(ST_MakePoint(haulingEndLongitude, haulingEndLatitude), 4326) AND the_geom_haulingend IS NULL; +UPDATE ll_observation.set SET the_geom_haulingend = ST_SetSRID(ST_MakePoint(haulingEndLongitude::double precision, haulingEndLatitude::double precision), 4326) WHERE the_geom_haulingend IS NULL; -SELECT AddGeometryColumn('ll_logbook', 'activity', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ll_logbook.activity ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_logbook_activity_gist ON ll_logbook.activity USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ll_logbook_activity_the_geom ON ll_logbook.activity; CREATE TRIGGER tr_sync_ll_logbook_activity_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ll_logbook.activity FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); UPDATE ll_logbook.activity SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; -SELECT AddGeometryColumn('ll_logbook', 'set', 'the_geom_settingstart', 4326, 'POINT', 2); +ALTER TABLE ll_logbook.set ADD COLUMN the_geom_settingstart geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_logbook_set_gist_settingstart ON ll_logbook.set USING GIST (the_geom_settingstart); DROP TRIGGER IF EXISTS tr_sync_ll_logbook_set_the_geom_settingstart ON ll_logbook.set; CREATE TRIGGER tr_sync_ll_logbook_set_the_geom_settingstart BEFORE INSERT OR UPDATE OF settingStartLatitude, settingStartLongitude ON ll_logbook.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_settingStart(); -UPDATE ll_logbook.set SET the_geom_settingstart = ST_SetSRID(ST_MakePoint(settingStartLongitude, settingStartLatitude), 4326) WHERE settingStartLongitude IS NOT NULL AND settingStartLatitude IS NOT NULL AND the_geom_settingstart IS NULL; +UPDATE ll_logbook.set SET the_geom_settingstart = ST_SetSRID(ST_MakePoint(settingStartLongitude::double precision, settingStartLatitude::double precision), 4326) WHERE settingStartLongitude IS NOT NULL AND settingStartLatitude IS NOT NULL AND the_geom_settingstart IS NULL; -SELECT AddGeometryColumn('ll_logbook', 'set', 'the_geom_settingend', 4326, 'POINT', 2); +ALTER TABLE ll_logbook.set ADD COLUMN the_geom_settingend geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_logbook_set_gist_settingend ON ll_logbook.set USING GIST (the_geom_settingend); DROP TRIGGER IF EXISTS tr_sync_ll_logbook_set_the_geom_settingend ON ll_logbook.set; CREATE TRIGGER tr_sync_ll_logbook_set_the_geom_settingend BEFORE INSERT OR UPDATE OF settingEndLatitude, settingEndLongitude ON ll_logbook.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_settingEnd(); -UPDATE ll_logbook.set SET the_geom_settingend = ST_SetSRID(ST_MakePoint(settingEndLongitude, settingEndLatitude), 4326) WHERE settingEndLongitude IS NOT NULL AND settingEndLatitude IS NOT NULL AND the_geom_settingend IS NULL; +UPDATE ll_logbook.set SET the_geom_settingend = ST_SetSRID(ST_MakePoint(settingEndLongitude::double precision, settingEndLatitude::double precision), 4326) WHERE settingEndLongitude IS NOT NULL AND settingEndLatitude IS NOT NULL AND the_geom_settingend IS NULL; -SELECT AddGeometryColumn('ll_logbook', 'set', 'the_geom_haulingstart', 4326, 'POINT', 2); +ALTER TABLE ll_logbook.set ADD COLUMN the_geom_haulingstart geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_logbook_set_gist_haulingstart ON ll_logbook.set USING GIST (the_geom_haulingstart); DROP TRIGGER IF EXISTS tr_sync_ll_logbook_set_the_geom_haulingstart ON ll_logbook.set; CREATE TRIGGER tr_sync_ll_logbook_set_the_geom_haulingstart BEFORE INSERT OR UPDATE OF haulingStartLatitude, haulingStartLongitude ON ll_logbook.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_haulingStart(); -UPDATE ll_logbook.set SET the_geom_haulingstart = ST_SetSRID(ST_MakePoint(haulingStartLongitude, haulingStartLatitude), 4326) WHERE haulingStartLongitude IS NOT NULL AND haulingStartLatitude IS NOT NULL AND the_geom_haulingstart IS NULL; +UPDATE ll_logbook.set SET the_geom_haulingstart = ST_SetSRID(ST_MakePoint(haulingStartLongitude::double precision, haulingStartLatitude::double precision), 4326) WHERE haulingStartLongitude IS NOT NULL AND haulingStartLatitude IS NOT NULL AND the_geom_haulingstart IS NULL; -SELECT AddGeometryColumn('ll_logbook', 'set', 'the_geom_haulingend', 4326, 'POINT', 2); +ALTER TABLE ll_logbook.set ADD COLUMN the_geom_haulingend geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_logbook_set_gist_haulingend ON ll_logbook.set USING GIST (the_geom_haulingend); DROP TRIGGER IF EXISTS tr_sync_ll_logbook_set_the_geom_haulingend ON ll_logbook.set; CREATE TRIGGER tr_sync_ll_logbook_set_the_geom_haulingend BEFORE INSERT OR UPDATE OF haulingEndLatitude, haulingEndLongitude ON ll_logbook.set FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_haulingEnd(); -UPDATE ll_logbook.set SET the_geom_haulingend = ST_SetSRID(ST_MakePoint(haulingEndLongitude, haulingEndLatitude), 4326) WHERE haulingEndLongitude IS NOT NULL AND haulingEndLatitude IS NOT NULL AND the_geom_haulingend IS NULL; +UPDATE ll_logbook.set SET the_geom_haulingend = ST_SetSRID(ST_MakePoint(haulingEndLongitude::double precision, haulingEndLatitude::double precision), 4326) WHERE haulingEndLongitude IS NOT NULL AND haulingEndLatitude IS NOT NULL AND the_geom_haulingend IS NULL; -SELECT AddGeometryColumn('ll_landing', 'landing', 'the_geom', 4326, 'POINT', 2); +ALTER TABLE ll_landing.landing ADD COLUMN the_geom geometry(Point, 4326); CREATE INDEX IF NOT EXISTS idx_ll_landing_landing_gist ON ll_landing.landing USING GIST (the_geom); DROP TRIGGER IF EXISTS tr_sync_ll_landing_landing_the_geom ON ll_landing.landing; CREATE TRIGGER tr_sync_ll_landing_landing_the_geom BEFORE INSERT OR UPDATE OF latitude, longitude ON ll_landing.landing FOR EACH ROW EXECUTE PROCEDURE sync_the_geom_default(); -UPDATE ll_landing.landing SET the_geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326) AND the_geom IS NULL; +UPDATE ll_landing.landing SET the_geom = ST_SetSRID(ST_MakePoint(longitude::double precision, latitude::double precision), 4326) WHERE latitude IS NOT NULL AND longitude IS NOT NULL AND the_geom IS NULL; View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/ead78f219371b12a5c63753e5d... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/ead78f219371b12a5c63753e5d... You're receiving this email because of your account on gitlab.com.