Skip to content

Instantly share code, notes, and snippets.

@zed

zed/postgis_timezone_db.markdown

Forked from robcowie/postgis_timezone_db.markdown
Created May 13, 2013 08:33
Show Gist options
  • Save zed/5566939 to your computer and use it in GitHub Desktop.
Save zed/5566939 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. zed revised this gist May 13, 2013. 1 changed file with 35 additions and 21 deletions.
    56 changes: 35 additions & 21 deletions postgis_timezone_db.markdown
    View file
    Original file line number Diff line number Diff line change
    @@ -1,30 +1,44 @@
    # Create a Postgis timezone DB

    See https://gist.github.com/3449216
    Ubuntu's analog of [robcowie / postgis_timezone_db.markdown](https://gist.github.com/robcowie/2896200)

    ## Create a timezone db from shapefiles
    ## Install postgis

    ```
    $ curl -O http://efele.net/maps/tz/world/tz_world.zip
    $ open tz_world.zip
    $ cd world
    $ createdb -T template_postgis timezones
    $ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql timezones
    ```
    sudo apt-get install python-software-properties
    sudo apt-add-repository ppa:ubuntugis/ppa
    sudo apt-get update
    sudo apt-get install postgresql-9.1-postgis

    ## Querying
    See [How to Get Started with PostGIS 2.0 on Ubuntu 12.04 (precise)](http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204)

    ```
    SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(-3.009444, 16.775833), 4326), geom); ## Timbuktu
    SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(2.3470, 48.8742), 4326), geom); ## Paris
    ```
    ## Create a db user with the same name as the current system user

    SRID 4326 is the grid system used in the shapefiles. It is possible to convert it so we don't need to convert on query.
    XXX# sudo -u postgres createuser --superuser $(whoami)

    ## See
    ## Create a GIS-enabled db

    * http://linfiniti.com/2010/03/batch-importing-shapefiles-into-postgis/
    * http://postgis.refractions.net/documentation/manual-1.4/ch02.html#id2754935
    * http://www.gisnotes.com/wordpress/2011/11/homebrew-postgresql9-0-4-postgis-1-5-3/
    * http://efele.net/maps/tz/world/
    * http://blog.shupp.org/2012/08/12/local-timezone-lookups-by-coordinates-with-postgis/
    createdb timezones
    psql -d timezones -c 'create extension postgis'
    (optional) # psql -d timezones -c 'create extension postgis_topology'

    ## Import timezone data from the tz_world shapefile

    curl -O http://efele.net/maps/tz/world/tz_world.zip
    unzip tz_world.zip
    cd world/

    shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql timezones

    See http://efele.net/maps/tz/world/

    ## Get a timezone from (longitude, latitude)

    psql timezones -c '
    SELECT tzid FROM timezone
    WHERE ST_Within(ST_SetSRID(ST_Point(2.3470, 48.8742), 4326), geom);
    ' ## Paris

    psql timezones -c '
    SELECT tzid FROM timezone
    WHERE ST_Within(ST_SetSRID(ST_Point(-81.61027, 41.50489), 4326), geom);
    ' ## Cleveland
  2. @robcowie robcowie revised this gist Sep 24, 2012. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgis_timezone_db.markdown
    View file
    Original file line number Diff line number Diff line change
    @@ -26,4 +26,5 @@ SRID 4326 is the grid system used in the shapefiles. It is possible to convert i
    * http://linfiniti.com/2010/03/batch-importing-shapefiles-into-postgis/
    * http://postgis.refractions.net/documentation/manual-1.4/ch02.html#id2754935
    * http://www.gisnotes.com/wordpress/2011/11/homebrew-postgresql9-0-4-postgis-1-5-3/
    * http://efele.net/maps/tz/world/
    * http://efele.net/maps/tz/world/
    * http://blog.shupp.org/2012/08/12/local-timezone-lookups-by-coordinates-with-postgis/
  3. @robcowie robcowie revised this gist Aug 24, 2012. 1 changed file with 1 addition and 13 deletions.
    14 changes: 1 addition & 13 deletions postgis_timezone_db.markdown
    View file
    Original file line number Diff line number Diff line change
    @@ -1,18 +1,6 @@
    # Create a Postgis timezone DB

    ## Create postgis database template

    ```
    $ createdb -E utf8 template_postgis
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql
    ```

    ## Create a new spatially-aware db

    ```
    $ createdb -T template_postgis <mydb>
    ```
    See https://gist.github.com/3449216

    ## Create a timezone db from shapefiles

  4. @robcowie robcowie created this gist Jun 8, 2012.
    41 changes: 41 additions & 0 deletions postgis_timezone_db.markdown
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,41 @@
    # Create a Postgis timezone DB

    ## Create postgis database template

    ```
    $ createdb -E utf8 template_postgis
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql
    ```

    ## Create a new spatially-aware db

    ```
    $ createdb -T template_postgis <mydb>
    ```

    ## Create a timezone db from shapefiles

    ```
    $ curl -O http://efele.net/maps/tz/world/tz_world.zip
    $ open tz_world.zip
    $ cd world
    $ createdb -T template_postgis timezones
    $ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql timezones
    ```

    ## Querying

    ```
    SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(-3.009444, 16.775833), 4326), geom); ## Timbuktu
    SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(2.3470, 48.8742), 4326), geom); ## Paris
    ```

    SRID 4326 is the grid system used in the shapefiles. It is possible to convert it so we don't need to convert on query.

    ## See

    * http://linfiniti.com/2010/03/batch-importing-shapefiles-into-postgis/
    * http://postgis.refractions.net/documentation/manual-1.4/ch02.html#id2754935
    * http://www.gisnotes.com/wordpress/2011/11/homebrew-postgresql9-0-4-postgis-1-5-3/
    * http://efele.net/maps/tz/world/