Add new table:
Code: Select all
CREATE TABLE `nyu_roads` (
`Substance` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
`GeoDataID` INT(10) UNSIGNED NULL DEFAULT NULL,
UNIQUE INDEX `GeoDataID` (`GeoDataID`)
)
COMMENT='Road table'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;
Code: Select all
CREATE DEFINER=`root`@`localhost` TRIGGER `geo_patch_after_insert` AFTER INSERT ON `geo_patch` FOR EACH ROW BEGIN
SET @newSubstance = new.Substance;
SET @newGeoDataID = new.GeoDataID;
SET @r = (SELECT Count(*) FROM nyu_roads WHERE GeoDataId = @newGeoDataID);
if @newSubstance in (177, 180, 181) then
if @r > 0 then
UPDATE nyu_roads SET Substance = new.Substance;
else
INSERT INTO nyu_roads (Substance,GeoDataID) VALUES (@newSubstance,@newGeoDataID);
end if;
else
if @r > 0 then
DELETE FROM nyu_roads WHERE GeoDataID = new.GeoDataID;
end if;
end if;
END
Code: Select all
INSERT IGNORE INTO nyu_roads (Substance,GeoDataID) SELECT gp.Substance, gp.GeoDataID FROM geo_patch gp
INNER JOIN ( SELECT GeoDataID, MAX(Version) MaxVer FROM geo_patch GROUP BY GeoDataID ) tmp ON tmp.GeoDataID = gp.GeoDataID AND tmp.MaxVer = gp.Version
WHERE gp.Substance IN (177, 180, 181) ON DUPLICATE KEY UPDATE Substance=gp.Substance ;
Code: Select all
$rs = $this->db->query( "SELECT * FROM nyu_roads" );
Fast show roads!
Maybe switcher on 2 variants loading.