4

When working with a spatial database, it is often convenient to make a sanity check for a location of a point by viewing it on Google Maps (or any other online mapping service).

Copying the lat/lon is tedious - it requires me to manually copy-paste data from the console to the browser, and since st_asText order (lon, lat) is the opposite of Google (lat, lon) I have to do it twice and add a comma in between. Impractical for fast checking a dozen or so points.

How do I generate a valid Google Maps or a Bing map link from a PostGIS geometry?

5

PostGIS lat-lon

This problem agonized me for a while, so I wrote PostGIS lat-lon. It converts any geometry to a lat-lon pair, a TSV tuple or a Google / Bing Link.

In OSX console, links are clickable, so no copy-paste is required.

Usage Example:

SELECT latlon(geom),
       latlon_parens(geom),
       latlon_tab(geom),
       latlon_google(geom),
       latlon_osm(geom),
       latlon_bing(geom)
FROM (SELECT st_geomFromText('POINT(2.294609 48.85835)', 4326) as geom) sq;


-[ RECORD 1 ]-+---------------------------------------------------------------------------------------------------------
latlon        | 48.8583500, 2.2946090
latlon_parens | (48.8583500, 2.2946090)
latlon_tab    | 48.8583500      2.2946090
latlon_google | https://maps.google.com/maps?q=48.8583500,2.2946090&ll=48.8583500,2.2946090&z=17
latlon_osm    | https://www.openstreetmap.org/?mlat=48.8583500&mlon=2.2946090#map=18/48.8583500/2.2946090
latlon_bing   | https://www.bing.com/maps/?v=2&cp=48.8583500~2.2946090&lvl=18&style=r&sp=point.48.8583500_2.2946090_geom

Please Contribute! If you have any time-saving PostGIS functions, I will be happy to accept any pull requests.

  • Personally, I just use a spatial view (cast to EPSG:3857 or EPSG:4326) in QGIS with the OpenLayers plugin to overlay an OpenStreetMaps or Google satellite imagery basemap. That's an easier way to sanity-check polygons and lines. – Richard Law Mar 7 '16 at 18:45
  • True, and it's a very useful tool indeed. But sometimes a link is handy. For instance, SELECT name, latlon_google(geom) FROM places will give me a quick link to see where a certain place is by its name. In QGis I will have to start filtering. – Adam Matan Mar 7 '16 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.