Using PostgreSQL To Update Outdated Map Links
We’ve rolled out completely new map GUI at edpp.cz built on top of OpenLayers 3. It looks great and has lots of functions both for BFU and power users. The only pitfall that came with moving away from OpenLayers 2 were remarkable differences in zoom levels between the old map and the new one.
Each of our maps is defined by our admins (center, zoom level, layers) at the map creation. Lots of links calling different views of map are created as well. They take form of
zoom=<Number> started causing troubles immediately after the map switch. No way my workmates would update them one by one as there were ~4,500 of them. Sounds like a task for little bit of regular expressions and some SQL updates.
UPDATE table SET column = regexp_replace(column, 'zoom=\d', 'zoom=' || subquery.zoom, 'g') FROM ( SELECT regexp_replace( substring(column from 'zoom=\d'), 'zoom=(\d)', '\1', 'g')::integer + 2 AS zoom, guid FROM table) AS subquery WHERE column ~ 'zoom=\d' AND table.guid = subquery.guid
That’s what I’ve come up with. It basically extracts the zoom level from the link, adds number two to its value and writes it back to the string.