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 http://edpp.cz/some-map?0=0&1=0...zoom=5. That 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.

Switch Latitude And Longitude With Regular Expression

It happens that you receive a file with longitude and latitude just in the opposite order that you would like to have. It’s fairly easy to switch those without loading it into Excel or Calc and doing Ctrl + C and Ctrl + V on columns.

If you have a file with tabular data that looks like this:

 50.52, 60.15
 70.96, 80.1
-55.23, 62.03

You can use Sublime Text to switch the values:

1. Press Ctrl + H
2. Copy (\-?\d+\.?\d+),?[\t ]*(\-?\d+\.?\d+)$ to Find What input
3. Copy $2,$1 to Replace With input

Hit Replace All button and you’re done.