Skip to content

Instantly share code, notes, and snippets.

@clhenrick

clhenrick/README.md

Last active March 3, 2025 23:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save clhenrick/ebc8dc779fb6f5ee6a88 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save clhenrick/ebc8dc779fb6f5ee6a88 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. clhenrick revised this gist Nov 9, 2019. 1 changed file with 37 additions and 9 deletions.
    46 changes: 37 additions & 9 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -546,15 +546,43 @@ Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexi

    ### Importing Spatial Data to PostGIS
    #### Using shp2pgsql
    1. Do:
    `shp2pgsql -I -s 4326 nyc-pediacities-hoods-v3-edit.shp noise.hoods > noise.sql`
    Or for using the geography data type do:
    `shp2pgsql -G -I nyc-pediacities-hoods-v3-edit.shp noise.nyc-pediacities-hoods-v3-edit_geographic > nyc_pediacities-hoods-v3-edit.sql`

    2. Do:
    `psql -d nyc_noise -f noise.sql`
    Or for the geography type above:
    `psql -d nyc_noise -f nyc_pediacities-hoods-v3-edit.sql `

    1. Create an SQL file with a CREATE TABLE statement:

    ```bash
    shp2pgsql -I -s 4326 nyc-pediacities-hoods-v3-edit.shp noise.hoods > noise.sql
    ```

    Or for using the geography data type do:

    ```bash
    shp2pgsql -G -I nyc-pediacities-hoods-v3-edit.shp noise.nyc-pediacities-hoods-v3-edit_geographic > nyc_pediacities-hoods-v3-edit.sql
    ```

    If you'd like to transform your data from one SRID to another, you may pass two EPSG codes separated by a colon to the `-s` flag:
    ```bash
    shp2pgsql -I -s 2236:4326 shapefile_in_2236.shp schema.table-name > table-name.sql
    ```
    2. Then run the SQL using `psql`:
    ```bash
    psql -d nyc_noise -f noise.sql
    ```
    Or for the geography type above:
    ```bash
    psql -d nyc_noise -f nyc_pediacities-hoods-v3-edit.sql
    ```
    3. Alternatively do both of the above in a single command:
    ```bash
    shp2pgsql -I -s 4326 nyc-pediacities-hoods-v3-edit.shp noise.hoods | psql -d nyc_noise
    ```
    #### Using osm2pgsql
    To import an OpenStreetMap extract in PBF format do:
  2. clhenrick revised this gist Jan 17, 2018. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -75,6 +75,10 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - to list all databases: `\l`

    - to list all the table in a database: `\dt`

    - to list extensions installed in a database: `\dx`

    - to quit psql: `\q`

    - to grant privileges to a user (requires logging in as `postgres` ):
  3. clhenrick revised this gist Jan 17, 2018. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -61,15 +61,15 @@ On Mac OS X:
    psql is the interactive unix command line tool for interacting with Postgres/PostGIS.

    ### Common Commands
    - log-in / connect to a database name by doing `psql -d db_name`
    - log-in / connect to a database name by doing `psql -d database_name`

    - for doing admin type things such as managing db users, log in as the postgres user: `psql postgres;`

    - to create a database: `CREATE DATABASE database-name;`
    - to create a database: `CREATE DATABASE database_name;`

    - to connect to a database: `\c database-name;`
    - to connect to a database: `\c database_name;`

    - to delete a database `DROP DATABASE database-name;`
    - to delete a database `DROP DATABASE database_name;`

    - to connect when starting psql use the `-d` flag like: `psql -d nyc_noise`

  4. clhenrick revised this gist Mar 15, 2017. 1 changed file with 15 additions and 2 deletions.
    17 changes: 15 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -506,9 +506,9 @@ FROM calc_duration
    WHERE calc_duration.cartodb_id = tracking_eric.cartodb_id
    ```

    **select population density by county**
    **Select population density**

    In this one we cast the geometry data type to the geography data type to get units of measure in meters.
    In this query we cast the geometry data type to the geography data type to get units of measure in meters.

    ```sql
    SELECT pop_sqkm,
    @@ -517,6 +517,19 @@ SELECT pop_sqkm,
    FROM us_counties
    ```

    **Repair Invalid Geometries**
    Sometimes when data is imported into PostGIS geometries get screwed up. If you get an
    error message like:

    ```
    ERROR: GEOSIntersects: TopologyException: side location conflict at -116.03227135270012 33.309736898054787
    ```

    You can try doing:

    ```sql
    UPDATE tablename SET geom=ST_MAKEVALID(geom) WHERE NOT ST_ISVALID(geom);
    ```

    ### Spatial Indexing
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.
  5. clhenrick revised this gist Feb 27, 2017. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -153,9 +153,10 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ```
    credit: [MatthewJ on stack-exchange](http://stackoverflow.com/questions/14471179/find-duplicate-rows-with-postgresql)

    - **Bulk Queries** are efficient when doing multiple inserts or updates of different values:
    - **Bulk Queries** are efficient when doing multiple inserts or updates of different values. For example,

    ```sql
    --- update some rows with new values
    UPDATE election_results o
    SET votes=n.votes, pro=n.pro
    FROM (VALUES (1,11,9),
    @@ -166,15 +167,18 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ```

    ```sql
    --- insert new values
    INSERT INTO election_results (county_id,voters,pro)
    VALUES (1, 11,8),
    (12,21,10),
    (78,31,27);
    ```
    The `INSERT` and `UPDATE` queries can be combined to what is often referred to as an `UPSERT` query:
    ```sql
    WITH
    -- write the new values
    -- make a temporary (as in for this query only) table of values
    n(ip,visits,clicks) AS (
    VALUES ('192.168.1.1',2,12),
    ('192.168.1.2',6,18),
    @@ -195,7 +199,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    );
    ```
    credit: [FASTER DATA UPDATES WITH CARTODB](https://carto.com/blog/faster-data-updates-with-cartodb)
    credit: [FASTER DATA UPDATES WITH CARTODB](https://carto.com/blog/faster-data-updates-with-cartodb)
    ### Importing Data
    - import data from a CSV file using the COPY command:
  6. clhenrick revised this gist Feb 27, 2017. 1 changed file with 63 additions and 41 deletions.
    104 changes: 63 additions & 41 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -101,15 +101,15 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - to insert data into a table:

    ```
    ```sql
    INSERT INTO table_name (column1, column2)
    VALUES
    (value1, value2);
    ```

    - to insert data from another table:

    ```
    ```sql
    INSERT INTO table_name (value1, value2)
    SELECT column1, column2
    FROM other_table_name
    @@ -122,7 +122,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - **list all column names from a table in alphabetical order:**

    ```
    ```sql
    select column_name
    from information_schema.columns
    where table_schema = 'public'
    @@ -142,7 +142,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - **Find duplicate rows** in a table based on values from two fields:

    ```
    ```sql
    select * from (
    SELECT id,
    ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
    @@ -155,7 +155,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - **Bulk Queries** are efficient when doing multiple inserts or updates of different values:

    ```
    ```sql
    UPDATE election_results o
    SET votes=n.votes, pro=n.pro
    FROM (VALUES (1,11,9),
    @@ -165,13 +165,14 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    WHERE o.county_id = n.county_id;
    ```

    ```
    ```sql
    INSERT INTO election_results (county_id,voters,pro)
    VALUES (1, 11,8),
    (12,21,10),
    (78,31,27);
    ```
    ```
    ```
    ```sql
    WITH
    -- write the new values
    n(ip,visits,clicks) AS (
    @@ -193,10 +194,13 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    SELECT ip FROM upsert
    );
    ```
    credit: [FASTER DATA UPDATES WITH CARTODB](https://carto.com/blog/faster-data-updates-with-cartodb)
    ### Importing Data
    - import data from a CSV file using the COPY command:
    ```
    ```sql
    COPY noise.locations (name, complaint, descript, boro, lat, lon)
    FROM '/Users/chrislhenrick/tutorials/postgresql/data/noise.csv' WITH CSV HEADER;
    ```
    @@ -209,13 +213,13 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ### Exporting Data
    - export data as a CSV with Headers using COPY:
    ```
    ```sql
    COPY dob_jobs_2014 to '/Users/chrislhenrick/development/nyc_dob_jobs/data/2014/dob_jobs_2014.csv' DELIMITER ',' CSV Header;
    ```
    - to the current workspace without saving to a file:
    ```
    ```sql
    COPY (SELECT foo FROM bar) TO STDOUT CSV HEADER;
    ```
    @@ -231,15 +235,15 @@ From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartod
    - Join two tables that share a key using an `INNER JOIN`(Postgresql's default join type):
    ```
    ```sql
    SELECT table_1.the_geom,table_1.iso_code,table_2.population
    FROM table_1, table_2
    WHERE table_1.iso_code = table_2.iso
    ```
    - To update a table's data based on that of a join:
    ```
    ```sql
    UPDATE table_1 as t1
    SET population = (
    SELECT population
    @@ -251,7 +255,7 @@ From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartod
    - aggregate data on a join (if table 2 has multiple rows for a unique identifier):
    ```
    ```sql
    SELECT
    table_1.the_geom,
    table_1.iso_code,
    @@ -262,7 +266,7 @@ From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartod
    ```
    - update the value of a column based on the aggregate join:
    ```
    ```sql
    UPDATE table_1 as t1
    SET total = (
    SELECT SUM(total)
    @@ -346,17 +350,17 @@ PostGIS is the extension for Postgres that allows for working with geometry data
    - create a new table for data from a CSV that has lat and lon columns:
    ```
    create table noise.locations
    (
    name varchar(100),
    complaint varchar(100), descript varchar(100),
    boro varchar(50),
    lat float8,
    lon float8,
    geom geometry(POINT, 4326)
    );
    ```
    ```sql
    create table noise.locations
    (
    name varchar(100),
    complaint varchar(100), descript varchar(100),
    boro varchar(50),
    lat float8,
    lon float8,
    geom geometry(POINT, 4326)
    );
    ```
    - inputing values for the geometry type after loading data from a CSV:
    `update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);`
    @@ -373,15 +377,15 @@ You may view more of these in [my intro to Visualizing Geospatial Data with Cart
    **Find all polygons from dataset A that intersect points from dataset B:**
    ```
    ```sql
    SELECT a.*
    FROM table_a_polygons a, table_b_points b
    WHERE ST_Intersects(a.the_geom, b.the_geom);
    ```

    **Find all rows in a polygon dataset that intersect a given point:**

    ```
    ```sql
    -- note: geometry for point must be in the order lon, lat (x, y)
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    @@ -395,7 +399,7 @@ ST_Intersects(

    Or using `ST_Contains`:

    ```
    ```sql
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    st_contains(
    @@ -410,7 +414,7 @@ st_contains(

    With ST_Containts():

    ```
    ```sql
    SELECT us_counties.the_geom_webmercator,us_counties.cartodb_id,
    count(quakes.the_geom)
    AS total
    @@ -421,7 +425,7 @@ GROUP BY us_counties.cartodb_id;

    To update a column from table A with the number of points from table B that intersect table A's polygons:

    ```
    ```sql
    update noise.hoods set num_complaints = (
    select count(*)
    from noise.locations
    @@ -439,14 +443,33 @@ Using [`ST_MakeEnvelope`](http://postgis.refractions.net/docs/ST_MakeEnvelope.ht
    HINT: You can use [bboxfinder.com](http://bboxfinder.com/) to easily grab coordinates
    of a bounding box for a given area.

    ```
    ```sql
    SELECT * FROM some_table
    where geom && ST_MakeEnvelope(-73.913891, 40.873781, -73.907229, 40.878251, 4326)
    ```

    **Select points from table a that _do not_ fall within any polygons in table b**
    This method makes use of spatial indexes and the indexes on `gid` for better performance

    ```sql
    SELECT
    a.gid,
    a.st_address,
    a.city,
    a.st_num,
    a.the_geom
    FROM
    points AS a LEFT JOIN
    polygons AS b ON
    ST_Intersects(a.the_geom, b.the_geom)
    WHERE b.gid IS NULL;
    ```

    credit: [Nicklas Avén](http://gis.stackexchange.com/questions/4502/postgis-select-features-that-do-not-intersect)

    **Make a line from a series of points**

    ```
    ```sql
    SELECT ST_MakeLine (the_geom ORDER BY id ASC)
    AS the_geom, route
    FROM points_table
    @@ -457,20 +480,19 @@ GROUP BY route;
    This one uses CartoDB's built-in function `CDB_LatLng` which is short hand for doing:
    `SELECT ST_Transform( ST_GeomFromText( 'Point(-73.982557 40.724435)',),4326)`

    ```
    ```sql
    SELECT * FROM table
    ORDER BY the_geom <->
    CDB_LatLng(42.5,-73) LIMIT 10;
    ```

    **Access the previous row of data and get value (time, value, number, etc) difference**

    ```
    ```sql
    WITH calc_duration AS (
    SELECT
    cartodb_id,
    extract(epoch FROM (date_time - lag(date_time,1) OVER(ORDER BY date_time))) AS
    duration_in_seconds
    extract(epoch FROM (date_time - lag(date_time, 1) OVER(ORDER BY date_time))) AS duration_in_seconds
    FROM tracking_eric
    ORDER BY date_time
    )
    @@ -484,7 +506,7 @@ WHERE calc_duration.cartodb_id = tracking_eric.cartodb_id

    In this one we cast the geometry data type to the geography data type to get units of measure in meters.

    ```
    ```sql
    SELECT pop_sqkm,
    round( pop / (ST_Area(the_geom::geography)/1000000))
    as psqkm
    @@ -499,7 +521,7 @@ Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexi
    `CREATE INDEX table_name_gix ON table_name USING GIST (geom);`
    `VACUUM ANALYZE table_name`
    `CLUSTER table_name USING table_name_gix;`
    ***Do this every time after making changes to your dataset or importing new data.**
    **Do this every time after making changes to your dataset or importing new data.**

    ### Importing Spatial Data to PostGIS
    #### Using shp2pgsql
    @@ -520,7 +542,7 @@ To import an OpenStreetMap extract in PBF format do:
    #### Using ogr2ogr
    Example importing a GeoJSON file into a database called nyc_pluto:

    ```
    ```bash
    ogr2ogr -f PostgreSQL \
    PG:"host='localhost' user='chrislhenrick' port='5432' \
    dbname='nyc_pluto' password=''" \
    @@ -558,7 +580,7 @@ If on Windows do this: `C:\> set GDAL_DATA=C:\GDAL\data`
    **To Export Data**
    Use ogr2ogr as follows to export a table (in this case a table called `dob_jobs_2014`) to a `GeoJSON` file (in this case a file called dob_jobs_2014_geocoded.geojson):

    ```
    ```bash
    ogr2ogr -f GeoJSON -t_srs EPSG:4326 dob_jobs_2014_geocoded.geojson \
    PG:"host='localhost' dbname='dob_jobs' user='chrislhenrick' password='' port='5432'" \
    -sql "SELECT bbl, house, streetname, borough, jobtype, jobstatus, existheight, proposedheight, \
  7. clhenrick revised this gist Feb 19, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -433,7 +433,7 @@ update noise.hoods set num_complaints = (
    );
    ```

    **Select data within a bounding box**
    **Select data within a bounding box**
    Using [`ST_MakeEnvelope`](http://postgis.refractions.net/docs/ST_MakeEnvelope.html)

    HINT: You can use [bboxfinder.com](http://bboxfinder.com/) to easily grab coordinates
  8. clhenrick revised this gist Feb 19, 2017. 1 changed file with 44 additions and 33 deletions.
    77 changes: 44 additions & 33 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    PostgreSQL & PostGIS Cheatsheet
    ===============================
    This is a collection of information on PostgreSQL and PostGIS for what I tend to use most often.
    This is a collection of information on PostgreSQL and PostGIS for what I tend to use most often.

    ## TOC
    - [Installing Postgres & PostGIS](#installation)
    @@ -18,7 +18,7 @@ This is a collection of information on PostgreSQL and PostGIS for what I tend to

    ## Installation
    ### Postgres
    - to install on Ubuntu do: `apt-get install postgresql`
    - to install on Ubuntu do: `apt-get install postgresql`

    - to install on Mac OS X first install [homebrew](http://brew.sh/) and then do `brew install postgresql`

    @@ -77,11 +77,11 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - to quit psql: `\q`

    - to grant privileges to a user (requires logging in as `postgres` ):
    - to grant privileges to a user (requires logging in as `postgres` ):

    `GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;`

    - to enable the hstore extension ( for key : value pairs, useful when working with OpenStreetMap data) do: `CREATE EXTENSION hstore`
    - to enable the hstore extension ( for key : value pairs, useful when working with OpenStreetMap data) do: `CREATE EXTENSION hstore`

    - to view columns of a table: `\d table_name`

    @@ -94,19 +94,19 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    - to change a column's data type:
    `alter table noise.hoods alter column noise_area type float;`

    - to compute values from two columns and assign them to another column: `update noise.hoods set noise_area = noise/(area/1000);`
    - to compute values from two columns and assign them to another column: `update noise.hoods set noise_area = noise/(area/1000);`

    - to search by wildcard use the `like` (case sensitive) or `ilike` (treats everything as lowercase) command:
    `SELECT count(*) from violations where inspection_date::text ilike '2014%';`

    - to insert data into a table:

    ```
    INSERT INTO table_name (column1, column2)
    VALUES
    (value1, value2);
    ```

    - to insert data from another table:

    ```
    @@ -133,13 +133,13 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    - **List data from a column as a single row, comma separated:**
    1. `SELECT array_to_string( array( SELECT id FROM table ), ',' )`
    2. `SELECT string_agg(id, ',') FROM table`

    - **rename an existing table:**
    `ALTER TABLE table_name RENAME TO table_name_new;`

    - **rename an existing column** of a table:
    `ALTER TABLE table_name RENAME COLUMN column_name TO column_new_name;`

    - **Find duplicate rows** in a table based on values from two fields:

    ```
    @@ -148,11 +148,11 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
    FROM Photos
    ) dups
    where
    where
    dups.Row > 1
    ```
    credit: [MatthewJ on stack-exchange](http://stackoverflow.com/questions/14471179/find-duplicate-rows-with-postgresql)
    credit: [MatthewJ on stack-exchange](http://stackoverflow.com/questions/14471179/find-duplicate-rows-with-postgresql)

    - **Bulk Queries** are efficient when doing multiple inserts or updates of different values:

    ```
    @@ -164,7 +164,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ) n(county_id,votes,pro)
    WHERE o.county_id = n.county_id;
    ```

    ```
    INSERT INTO election_results (county_id,voters,pro)
    VALUES (1, 11,8),
    @@ -212,7 +212,7 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ```
    COPY dob_jobs_2014 to '/Users/chrislhenrick/development/nyc_dob_jobs/data/2014/dob_jobs_2014.csv' DELIMITER ',' CSV Header;
    ```

    - to the current workspace without saving to a file:

    ```
    @@ -224,19 +224,19 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos
    ```
    psql -d dbname -t -A -F"," -c "select * from table_name" > output.csv
    ```


    ### Joining Tables Using a Shared Key
    From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartodb.com/tutorials/joining_data.html)

    - Join two tables that share a key using an `INNER JOIN`(Postgresql's default join type):

    ```
    SELECT table_1.the_geom,table_1.iso_code,table_2.population
    FROM table_1, table_2
    WHERE table_1.iso_code = table_2.iso
    ```

    - To update a table's data based on that of a join:

    ```
    @@ -286,7 +286,7 @@ Basically the steps are:
    `initdb /usr/local/var/postgres9.4 -E utf8`

    3. Run the pg_upgrade command:

    ```
    pg_upgrade \
    -d /usr/local/var/postgres \
    @@ -296,7 +296,7 @@ Basically the steps are:
    -v
    ```
    4. Change kernel settings if necessary:

    ```
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216
    @@ -308,17 +308,17 @@ Basically the steps are:
    kern.sysv.shmmax=16777216
    ```
    - re-run the pg_upgrade command in step 3

    5. Move the new data directory into place:

    ```
    cd /usr/local/var
    mv postgres postgres9.2.4
    mv postgres9.3 postgres
    ```
    6. Start the new version of PostgreSQL:
    6. Start the new version of PostgreSQL:
    `launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist`
    - check to make sure it worked:
    - check to make sure it worked:

    ```
    psql postgres -c "select version()"
    @@ -338,7 +338,7 @@ PostGIS is the extension for Postgres that allows for working with geometry data

    ### Common Commands

    - to enable PostGIS in a Postgres database do: `CREATE EXTENSION postgis;`
    - to enable PostGIS in a Postgres database do: `CREATE EXTENSION postgis;`

    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    @@ -357,24 +357,24 @@ PostGIS is the extension for Postgres that allows for working with geometry data
    geom geometry(POINT, 4326)
    );
    ```

    - inputing values for the geometry type after loading data from a CSV:
    `update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);`

    - adding a geometry column in a non-spatial table:
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`

    - calculating area in EPSG 4326:
    `alter table noise.hoods set area = (select ST_Area(geom::geography));`


    ### Common Spatial Queries
    You may view more of these in [my intro to Visualizing Geospatial Data with CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).

    **Find all polygons from dataset A that intersect points from dataset B:**

    ```
    SELECT a.*
    SELECT a.*
    FROM table_a_polygons a, table_b_points b
    WHERE ST_Intersects(a.the_geom, b.the_geom);
    ```
    @@ -384,7 +384,7 @@ WHERE ST_Intersects(a.the_geom, b.the_geom);
    ```
    -- note: geometry for point must be in the order lon, lat (x, y)
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    where
    ST_Intersects(
    ST_GeomFromText(
    'Point(-73.982557 40.724435)', 4326
    @@ -397,7 +397,7 @@ Or using `ST_Contains`:

    ```
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    where
    st_contains(
    nyc_tenants_rights_service_areas.the_geom,
    ST_GeomFromText(
    @@ -433,6 +433,17 @@ update noise.hoods set num_complaints = (
    );
    ```

    **Select data within a bounding box**
    Using [`ST_MakeEnvelope`](http://postgis.refractions.net/docs/ST_MakeEnvelope.html)

    HINT: You can use [bboxfinder.com](http://bboxfinder.com/) to easily grab coordinates
    of a bounding box for a given area.

    ```
    SELECT * FROM some_table
    where geom && ST_MakeEnvelope(-73.913891, 40.873781, -73.907229, 40.878251, 4326)
    ```

    **Make a line from a series of points**

    ```
    @@ -482,7 +493,7 @@ SELECT pop_sqkm,


    ### Spatial Indexing
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.

    - Basically the steps are:
    `CREATE INDEX table_name_gix ON table_name USING GIST (geom);`
    @@ -564,4 +575,4 @@ to do...

    ### Python

    ### Node JS
    ### Node JS
  9. clhenrick revised this gist Mar 28, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -530,7 +530,7 @@ pgsql2shp -f <path to output shapefile> -h <hostname> -u <username> -P <password
    A sample export of a shapefile called `my_data` from a database called `my_db` looks like this:

    ```
    pgsql2shp -f my_data -h localhost -u clhenrick -P 'mypassword' my_db "SELECT * FROM my_db "
    pgsql2shp -f my_data -h localhost -u clhenrick -P 'mypassword' my_db "SELECT * FROM my_data "
    ```

    #### Using ogr2ogr
  10. clhenrick revised this gist Jun 13, 2015. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -85,6 +85,9 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - to view columns of a table: `\d table_name`

    - to list all columns in a table (helpful when you have a lot of columns!):
    `select column_name from information_schema.columns where table_name = 'my_table' order by column_name asc;`

    - to rename a column:
    `alter table noise.hoods rename column noise_sqkm to complaints_sqkm;`

  11. clhenrick revised this gist May 3, 2015. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -368,6 +368,14 @@ PostGIS is the extension for Postgres that allows for working with geometry data
    ### Common Spatial Queries
    You may view more of these in [my intro to Visualizing Geospatial Data with CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).

    **Find all polygons from dataset A that intersect points from dataset B:**

    ```
    SELECT a.*
    FROM table_a_polygons a, table_b_points b
    WHERE ST_Intersects(a.the_geom, b.the_geom);
    ```

    **Find all rows in a polygon dataset that intersect a given point:**

    ```
  12. clhenrick revised this gist May 3, 2015. 1 changed file with 62 additions and 37 deletions.
    99 changes: 62 additions & 37 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -67,48 +67,56 @@ psql is the interactive unix command line tool for interacting with Postgres/Pos

    - to create a database: `CREATE DATABASE database-name;`

    - to delete a database `DROP DATABASE database-name;`

    - to connect to a database: `\c database-name;`

    - to delete a database `DROP DATABASE database-name;`

    - to connect when starting psql use the `-d` flag like: `psql -d nyc_noise`

    - to list all databases: `\l`

    - to quit psql: `\q`

    - to grant privileges to a user: `GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;`
    - to grant privileges to a user (requires logging in as `postgres` ):

    `GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;`

    - to enable the hstore extension ( for key : value pairs) do: `CREATE EXTENSION hstore`
    - to enable the hstore extension ( for key : value pairs, useful when working with OpenStreetMap data) do: `CREATE EXTENSION hstore`

    - to view columns of a table: `\d table_name`

    - to rename a column: `alter table noise.hoods rename column noise_sqkm to complaints_sqkm;`
    - to rename a column:
    `alter table noise.hoods rename column noise_sqkm to complaints_sqkm;`

    - to change a column's data type: `alter table noise.hoods alter column noise_area type float;`
    - to change a column's data type:
    `alter table noise.hoods alter column noise_area type float;`

    - to compute values from two columns and assign them to another column: `update noise.hoods set noise_area = noise/(area/1000);`

    - to search by wildcard use the `like` (case sensitive) or `ilike` (treats everything as lowercase) command:
    `SELECT count(*) from violations where inspection_date::text ilike '2014%';`


    - create a new table for data from a CSV that has lat and lon columns:

    - to insert data into a table:

    ```
    INSERT INTO table_name (column1, column2)
    VALUES
    (value1, value2);
    ```
    -- p.s. this here is a comment! it will be ignored.

    create table noise.locations
    (
    name varchar(100),
    complaint varchar(100), descript varchar(100),
    boro varchar(50),
    lat float8,
    lon float8,
    geom geometry(POINT, 4326)
    );
    - to insert data from another table:

    ```
    INSERT INTO table_name (value1, value2)
    SELECT column1, column2
    FROM other_table_name
    ```


    - to remove rows using a where clause:
    `DELETE FROM table_name WHERE some_column = some_value`


    - **list all column names from a table in alphabetical order:**

    ```
    @@ -333,28 +341,29 @@ PostGIS is the extension for Postgres that allows for working with geometry data

    - to support OSM tags do: `CREATE EXTENSION hstore;`

    - to update a column from table A with the number of points from table B that intersect table A's polygons:

    ```
    update noise.hoods set num_complaints = (
    select count(*)
    from noise.locations
    where
    ST_Intersects(
    noise.locations.geom,
    noise.hoods.geom
    )
    );
    ```

    - calculating area in EPSG 4326:
    `alter table noise.hoods set area = (select ST_Area(geom::geography));`
    - create a new table for data from a CSV that has lat and lon columns:

    ```
    create table noise.locations
    (
    name varchar(100),
    complaint varchar(100), descript varchar(100),
    boro varchar(50),
    lat float8,
    lon float8,
    geom geometry(POINT, 4326)
    );
    ```

    - inputing values for the geometry type after loading data from a CSV:
    `update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);`

    - adding a geometry column in a non-spatial table:
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`

    - calculating area in EPSG 4326:
    `alter table noise.hoods set area = (select ST_Area(geom::geography));`


    ### Common Spatial Queries
    You may view more of these in [my intro to Visualizing Geospatial Data with CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).
    @@ -373,7 +382,7 @@ ST_Intersects(
    );
    ```

    - Or using `ST_Contains`:
    Or using `ST_Contains`:

    ```
    SELECT * FROM nyc_tenants_rights_service_areas
    @@ -388,6 +397,8 @@ st_contains(

    **Counting points inside a polygon:**

    With ST_Containts():

    ```
    SELECT us_counties.the_geom_webmercator,us_counties.cartodb_id,
    count(quakes.the_geom)
    @@ -397,6 +408,20 @@ ON st_contains(us_counties.the_geom,quakes.the_geom)
    GROUP BY us_counties.cartodb_id;
    ```

    To update a column from table A with the number of points from table B that intersect table A's polygons:

    ```
    update noise.hoods set num_complaints = (
    select count(*)
    from noise.locations
    where
    ST_Intersects(
    noise.locations.geom,
    noise.hoods.geom
    )
    );
    ```

    **Make a line from a series of points**

    ```
  13. clhenrick revised this gist Apr 28, 2015. 1 changed file with 13 additions and 0 deletions.
    13 changes: 13 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -26,6 +26,19 @@ This is a collection of information on PostgreSQL and PostGIS for what I tend to

    Note that for OS X and Ubuntu you may need to run the above commands as a super user / using `sudo`.

    #### Set Up
    On Ubuntu you typically need to log in as the Postgres user and do some admin things:

    - log in as postgres: `sudo -i -u postgres`
    - create a new user: `createuser --interactive`
    - type the name of the new user (no spaces!), typically the same name as your linux user that isn't root. You can add a new linux user by doing `adduser username`.
    - typically you want the user to have super-user privileges, so type `y` when asked.
    - create a new database that has the same name as the new user: `createdb username`

    For Mac OS X you can skip the above if you install with homebrew.

    For Windows....


    #### Starting the Postgres Database
    On Mac OS X:
  14. clhenrick revised this gist Apr 28, 2015. 1 changed file with 45 additions and 5 deletions.
    50 changes: 45 additions & 5 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    PostgreSQL & PostGIS Cheatsheet
    ===============================
    The most common commands so I don't forget!
    This is a collection of information on PostgreSQL and PostGIS for what I tend to use most often.

    ## TOC
    - [Installing Postgres & PostGIS](#installation)
    @@ -14,26 +14,43 @@ The most common commands so I don't forget!
    - [Spatial Indexing](#spatial-indexing)
    - [Importing spatial data into PostGIS](#importing-spatial-data-to-postgis)
    - [Exporting spatial data from PostGIS](#exporting-spatial-data-from-postgis)
    - [Other Methods of Interacting With Postgres/PostGIS](#other-methods-of-interacting-with-postgres/postgis)

    ## Installation
    ### Postgres
    - to install on Ubuntu do: `sudo apt-get install -y postgresql postgresql-contrib`
    - to install on Ubuntu do: `apt-get install postgresql`

    - to install on Mac OS X first install [homebrew](http://brew.sh/) and then do `brew install postgresql`

    - to install on Windows...

    Note that for OS X and Ubuntu you may need to run the above commands as a super user / using `sudo`.


    #### Starting the Postgres Database
    On Mac OS X:

    - to start the Postgres server do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop

    - to have Postgres start everytime you boot your Mac do: `ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents` then to check that it's working after booting do: `ps ax | grep sql`

    ### PostGIS
    - On Mac OS X the easiest method is via homebrew: `brew install postgis` (if you don't have postgres installed yet it will install this first).
    - On Ubuntu do `apt-get install postgis`

    - On Mac OS X the easiest method is via homebrew: `brew install postgis`
    (note that if you don't have Postgres or GDAL installed already it will automatically install these first).

    - to install on Windows...

    ## psql
    psql is the unix command line tool for interacting with postgres.
    psql is the interactive unix command line tool for interacting with Postgres/PostGIS.

    ### Common Commands
    - for doing admin type things log in as postgres: `psql postgres;`
    - log-in / connect to a database name by doing `psql -d db_name`

    - for doing admin type things such as managing db users, log in as the postgres user: `psql postgres;`

    - to create a database: `CREATE DATABASE database-name;`

    @@ -452,6 +469,21 @@ bk_map_pluto_4326.json -nln bk_pluto


    ### Exporting Spatial Data from PostGIS
    The two main tools used to export spatial data with more complex geometries from Postgres/PostGIS than points are `pgsql2shp` and `ogr2ogr`.

    #### Using pgsql2shp
    `pgsql2shp` is a tool that comes installed with PostGIS that allows for exporting data from a PostGIS database to a shapefile format. To use it you need to specify a file path to the output shapefile (just stating the basename with no extension will output in the current working directory), a host name (usually this is `localhost`), a user name, a password for the user, a database name, and an SQL query.

    ```
    pgsql2shp -f <path to output shapefile> -h <hostname> -u <username> -P <password> databasename "<query>"
    ```

    A sample export of a shapefile called `my_data` from a database called `my_db` looks like this:

    ```
    pgsql2shp -f my_data -h localhost -u clhenrick -P 'mypassword' my_db "SELECT * FROM my_db "
    ```

    #### Using ogr2ogr
    **Note:** You may need to set the `GDAL_DATA` path if you git this error:

    @@ -476,3 +508,11 @@ FROM dob_jobs_2014 WHERE geom IS NOT NULL"
    ```

    - **note:** you must select the column containing the geometry (usually `geom` or `wkb_geometry`) for your exported layer to have geometry data.

    ## Other Methods of Interacting With Postgres/PostGIS
    to do...
    ### PGAdmin

    ### Python

    ### Node JS
  15. clhenrick revised this gist Apr 27, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -188,7 +188,7 @@ psql is the unix command line tool for interacting with postgres.
    ### Joining Tables Using a Shared Key
    From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartodb.com/tutorials/joining_data.html)

    - Join two tables that share a key:
    - Join two tables that share a key using an `INNER JOIN`(Postgresql's default join type):

    ```
    SELECT table_1.the_geom,table_1.iso_code,table_2.population
  16. clhenrick revised this gist Apr 27, 2015. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -169,7 +169,13 @@ psql is the unix command line tool for interacting with postgres.
    - export data as a CSV with Headers using COPY:

    ```
    copy dob_jobs_2014 to '/Users/chrislhenrick/development/nyc_dob_jobs/data/2014/dob_jobs_2014.csv' delimiter ',' CSV Header;
    COPY dob_jobs_2014 to '/Users/chrislhenrick/development/nyc_dob_jobs/data/2014/dob_jobs_2014.csv' DELIMITER ',' CSV Header;
    ```

    - to the current workspace without saving to a file:

    ```
    COPY (SELECT foo FROM bar) TO STDOUT CSV HEADER;
    ```

    - from the command line w/o connecting to postgres:
  17. clhenrick revised this gist Apr 27, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -321,7 +321,7 @@ PostGIS is the extension for Postgres that allows for working with geometry data
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`

    ### Common Spatial Queries
    You may view more of these in [my intro to CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).
    You may view more of these in [my intro to Visualizing Geospatial Data with CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).

    **Find all rows in a polygon dataset that intersect a given point:**

  18. clhenrick revised this gist Apr 27, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -9,7 +9,7 @@ The most common commands so I don't forget!
    - [Exporting Data from Postgres](#exporting-data)
    - [Joining Tables](#joining-tables-using-a-shared-key)
    - [Upgrading Postgres](#upgrading-postgres)
    - [PostGIS common commands](#postgis)
    - [PostGIS common commands](#postgis-1)
    - [Common PostGIS spatial queries](#common-spatial-queries)
    - [Spatial Indexing](#spatial-indexing)
    - [Importing spatial data into PostGIS](#importing-spatial-data-to-postgis)
  19. clhenrick revised this gist Apr 20, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -13,7 +13,7 @@ The most common commands so I don't forget!
    - [Common PostGIS spatial queries](#common-spatial-queries)
    - [Spatial Indexing](#spatial-indexing)
    - [Importing spatial data into PostGIS](#importing-spatial-data-to-postgis)
    - [Exporting spatial data from PostGIS](#importing-spatial-data-from-postgis)
    - [Exporting spatial data from PostGIS](#exporting-spatial-data-from-postgis)

    ## Installation
    ### Postgres
  20. clhenrick revised this gist Apr 20, 2015. 1 changed file with 75 additions and 11 deletions.
    86 changes: 75 additions & 11 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -8,6 +8,12 @@ The most common commands so I don't forget!
    - [Importing Data into Postgres](#importing-data)
    - [Exporting Data from Postgres](#exporting-data)
    - [Joining Tables](#joining-tables-using-a-shared-key)
    - [Upgrading Postgres](#upgrading-postgres)
    - [PostGIS common commands](#postgis)
    - [Common PostGIS spatial queries](#common-spatial-queries)
    - [Spatial Indexing](#spatial-indexing)
    - [Importing spatial data into PostGIS](#importing-spatial-data-to-postgis)
    - [Exporting spatial data from PostGIS](#importing-spatial-data-from-postgis)

    ## Installation
    ### Postgres
    @@ -315,24 +321,23 @@ PostGIS is the extension for Postgres that allows for working with geometry data
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`

    ### Common Spatial Queries
    Find all rows in a polygon dataset that intersect a point:
    You may view more of these in [my intro to CartoDB](https://github.com/clhenrick/cartodb-tutorial/tree/master/sql).

    **Find all rows in a polygon dataset that intersect a given point:**

    ```
    -- note: geometry for point must be lon lat
    -- note: geometry for point must be in the order lon, lat (x, y)
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    ST_Intersects(
    ST_Transform(
    ST_GeomFromText(
    'Point(-73.982557 40.724435)', 4326
    ),
    4326
    ST_GeomFromText(
    'Point(-73.982557 40.724435)', 4326
    ),
    nyc_tenants_rights_service_areas.the_geom
    nyc_tenants_rights_service_areas.the_geom
    );
    ```

    Or using `ST_Contains`:
    - Or using `ST_Contains`:

    ```
    SELECT * FROM nyc_tenants_rights_service_areas
    @@ -345,6 +350,65 @@ st_contains(
    );
    ```

    **Counting points inside a polygon:**

    ```
    SELECT us_counties.the_geom_webmercator,us_counties.cartodb_id,
    count(quakes.the_geom)
    AS total
    FROM us_counties JOIN quakes
    ON st_contains(us_counties.the_geom,quakes.the_geom)
    GROUP BY us_counties.cartodb_id;
    ```

    **Make a line from a series of points**

    ```
    SELECT ST_MakeLine (the_geom ORDER BY id ASC)
    AS the_geom, route
    FROM points_table
    GROUP BY route;
    ```

    **Order points in a table by distance to a given lat lon**
    This one uses CartoDB's built-in function `CDB_LatLng` which is short hand for doing:
    `SELECT ST_Transform( ST_GeomFromText( 'Point(-73.982557 40.724435)',),4326)`

    ```
    SELECT * FROM table
    ORDER BY the_geom <->
    CDB_LatLng(42.5,-73) LIMIT 10;
    ```

    **Access the previous row of data and get value (time, value, number, etc) difference**

    ```
    WITH calc_duration AS (
    SELECT
    cartodb_id,
    extract(epoch FROM (date_time - lag(date_time,1) OVER(ORDER BY date_time))) AS
    duration_in_seconds
    FROM tracking_eric
    ORDER BY date_time
    )
    UPDATE tracking_eric
    SET duration_in_seconds = calc_duration.duration_in_seconds
    FROM calc_duration
    WHERE calc_duration.cartodb_id = tracking_eric.cartodb_id
    ```

    **select population density by county**

    In this one we cast the geometry data type to the geography data type to get units of measure in meters.

    ```
    SELECT pop_sqkm,
    round( pop / (ST_Area(the_geom::geography)/1000000))
    as psqkm
    FROM us_counties
    ```


    ### Spatial Indexing
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.

    @@ -354,7 +418,7 @@ Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexi
    `CLUSTER table_name USING table_name_gix;`
    ***Do this every time after making changes to your dataset or importing new data.**

    ### Importing data to PostGIS
    ### Importing Spatial Data to PostGIS
    #### Using shp2pgsql
    1. Do:
    `shp2pgsql -I -s 4326 nyc-pediacities-hoods-v3-edit.shp noise.hoods > noise.sql`
    @@ -381,7 +445,7 @@ bk_map_pluto_4326.json -nln bk_pluto
    ```


    ### Exporting data from PostGIS
    ### Exporting Spatial Data from PostGIS
    #### Using ogr2ogr
    **Note:** You may need to set the `GDAL_DATA` path if you git this error:

  21. clhenrick revised this gist Apr 20, 2015. 1 changed file with 17 additions and 2 deletions.
    19 changes: 17 additions & 2 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,21 +2,34 @@ PostgreSQL & PostGIS Cheatsheet
    ===============================
    The most common commands so I don't forget!

    ## Postgres
    ## TOC
    - [Installing Postgres & PostGIS](#installation)
    - [Using Postgres on the command line: PSQL](#psql)
    - [Importing Data into Postgres](#importing-data)
    - [Exporting Data from Postgres](#exporting-data)
    - [Joining Tables](#joining-tables-using-a-shared-key)

    ## Installation
    ### Postgres
    - to install on Ubuntu do: `sudo apt-get install -y postgresql postgresql-contrib`
    - to start the Postgres server do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop

    - to have Postgres start everytime you boot your Mac do: `ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents` then to check that it's working after booting do: `ps ax | grep sql`

    ### PostGIS
    - On Mac OS X the easiest method is via homebrew: `brew install postgis` (if you don't have postgres installed yet it will install this first).



    ## psql
    psql is the unix command line tool for interacting with postgres.

    ### Common Commands
    - for doing admin type things log in as postgres: `psql postgres;`

    - to create a databse: `CREATE DATABASE database-name;`
    - to create a database: `CREATE DATABASE database-name;`

    - to delete a database `DROP DATABASE database-name;`

    @@ -47,6 +60,8 @@ psql is the unix command line tool for interacting with postgres.
    - create a new table for data from a CSV that has lat and lon columns:

    ```
    -- p.s. this here is a comment! it will be ignored.
    create table noise.locations
    (
    name varchar(100),
  22. clhenrick revised this gist Apr 20, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -16,7 +16,7 @@ psql is the unix command line tool for interacting with postgres.
    ### Common Commands
    - for doing admin type things log in as postgres: `psql postgres;`

    - to create a databse: `CREATE database-name;`
    - to create a databse: `CREATE DATABASE database-name;`

    - to delete a database `DROP DATABASE database-name;`

  23. clhenrick revised this gist Apr 20, 2015. 1 changed file with 269 additions and 26 deletions.
    295 changes: 269 additions & 26 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,7 @@ PostgreSQL & PostGIS Cheatsheet
    The most common commands so I don't forget!

    ## Postgres
    - to install on Ubuntu do: `sudo apt-get install -y postgresql postgresql-contrib`
    - to start the Postgres server do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop
    @@ -39,6 +40,10 @@ psql is the unix command line tool for interacting with postgres.

    - to compute values from two columns and assign them to another column: `update noise.hoods set noise_area = noise/(area/1000);`

    - to search by wildcard use the `like` (case sensitive) or `ilike` (treats everything as lowercase) command:
    `SELECT count(*) from violations where inspection_date::text ilike '2014%';`


    - create a new table for data from a CSV that has lat and lon columns:

    ```
    @@ -53,13 +58,7 @@ psql is the unix command line tool for interacting with postgres.
    );
    ```

    - importing data from a CSV file:

    ```
    COPY noise.locations (name, complaint, descript, boro, lat, lon)
    FROM '/Users/chrislhenrick/tutorials/postgresql/data/noise.csv' WITH CSV HEADER;
    ```
    - list all columns from a table in a db in alphabetical order:
    - **list all column names from a table in alphabetical order:**

    ```
    select column_name
    @@ -69,43 +68,276 @@ psql is the unix command line tool for interacting with postgres.
    order by column_name;
    ```

    - **List data from a column as a single row, comma separated:**
    1. `SELECT array_to_string( array( SELECT id FROM table ), ',' )`
    2. `SELECT string_agg(id, ',') FROM table`

    - **rename an existing table:**
    `ALTER TABLE table_name RENAME TO table_name_new;`

    - **rename an existing column** of a table:
    `ALTER TABLE table_name RENAME COLUMN column_name TO column_new_name;`

    - **Find duplicate rows** in a table based on values from two fields:

    ```
    select * from (
    SELECT id,
    ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
    FROM Photos
    ) dups
    where
    dups.Row > 1
    ```
    credit: [MatthewJ on stack-exchange](http://stackoverflow.com/questions/14471179/find-duplicate-rows-with-postgresql)

    - **Bulk Queries** are efficient when doing multiple inserts or updates of different values:

    ```
    UPDATE election_results o
    SET votes=n.votes, pro=n.pro
    FROM (VALUES (1,11,9),
    (2,44,28),
    (3,25,4)
    ) n(county_id,votes,pro)
    WHERE o.county_id = n.county_id;
    ```

    ```
    INSERT INTO election_results (county_id,voters,pro)
    VALUES (1, 11,8),
    (12,21,10),
    (78,31,27);
    ```
    ```
    WITH
    -- write the new values
    n(ip,visits,clicks) AS (
    VALUES ('192.168.1.1',2,12),
    ('192.168.1.2',6,18),
    ('192.168.1.3',3,4)
    ),
    -- update existing rows
    upsert AS (
    UPDATE page_views o
    SET visits=n.visits, clicks=n.clicks
    FROM n WHERE o.ip = n.ip
    RETURNING o.ip
    )
    -- insert missing rows
    INSERT INTO page_views (ip,visits,clicks)
    SELECT n.ip, n.visits, n.clicks FROM n
    WHERE n.ip NOT IN (
    SELECT ip FROM upsert
    );
    ```
    ### Importing Data
    - import data from a CSV file using the COPY command:

    ```
    COPY noise.locations (name, complaint, descript, boro, lat, lon)
    FROM '/Users/chrislhenrick/tutorials/postgresql/data/noise.csv' WITH CSV HEADER;
    ```
    - import a CSV file "AS IS" using csvkit's `csvsql` (requires python, pip, csvkit, psycopg2):

    ```
    csvsql --db postgresql:///nyc_pluto --insert 2012_DHCR_Bldg.csv
    ```

    ### Exporting Data
    - export data as a CSV with Headers using COPY:

    ```
    copy dob_jobs_2014 to '/Users/chrislhenrick/development/nyc_dob_jobs/data/2014/dob_jobs_2014.csv' delimiter ',' CSV Header;
    ```

    - from the command line w/o connecting to postgres:

    ```
    psql -d dbname -t -A -F"," -c "select * from table_name" > output.csv
    ```


    ### Joining Tables Using a Shared Key
    From CartoDB's tutorial [Join data from two tables using SQL](http://docs.cartodb.com/tutorials/joining_data.html)

    - Join two tables that share a key:

    ```
    SELECT table_1.the_geom,table_1.iso_code,table_2.population
    FROM table_1, table_2
    WHERE table_1.iso_code = table_2.iso
    ```

    - To update a table's data based on that of a join:

    ```
    UPDATE table_1 as t1
    SET population = (
    SELECT population
    FROM table_2
    WHERE iso = t1.iso_code
    LIMIT 1
    )
    ```

    - aggregate data on a join (if table 2 has multiple rows for a unique identifier):

    ```
    SELECT
    table_1.the_geom,
    table_1.iso_code,
    SUM(table_2.total) as total
    FROM table_1, table_2
    WHERE table_1.iso_code = table_2.iso
    GROUP BY table_1.iso_code, table_2.iso
    ```
    - update the value of a column based on the aggregate join:

    ```
    UPDATE table_1 as t1
    SET total = (
    SELECT SUM(total)
    FROM table_2
    WHERE iso = t1.iso_code
    GROUP BY iso
    )
    ```

    ### Upgrading Postgres
    [This Tutorial](http://blog.55minutes.com/2013/09/postgresql-93-brew-upgrade/) was very helpful for upgrading on Mac OS X via homebrew.

    **_WARNING:_** **Back up your data before doing this incase you screw up like I did!**

    Basically the steps are:

    1. Shut down Postgresql:
    `launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist`

    2. Create a new Postgresql9.x data directory:
    `initdb /usr/local/var/postgres9.4 -E utf8`

    3. Run the pg_upgrade command:

    ```
    pg_upgrade \
    -d /usr/local/var/postgres \
    -D /usr/local/var/postgres9.4 \
    -b /usr/local/Cellar/postgresql/9.3.5_1/bin/ \
    -B /usr/local/Cellar/postgresql/9.4.0/bin/ \
    -v
    ```
    4. Change kernel settings if necessary:

    ```
    sudo sysctl -w kern.sysv.shmall=65536
    sudo sysctl -w kern.sysv.shmmax=16777216
    ```
    - I also ran sudo vi /etc/sysctl.conf and entered the same values:

    ```
    kern.sysv.shmall=65536
    kern.sysv.shmmax=16777216
    ```
    - re-run the pg_upgrade command in step 3

    5. Move the new data directory into place:

    ```
    cd /usr/local/var
    mv postgres postgres9.2.4
    mv postgres9.3 postgres
    ```
    6. Start the new version of PostgreSQL:
    `launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist`
    - check to make sure it worked:

    ```
    psql postgres -c "select version()"
    psql -l
    ```

    7. Cleanup:
    - `vacuumdb --all --analyze-only`
    - `analyze_new_cluster.sh`*
    - `delete_old_cluster.sh`*
    - `brew cleanup postgresql`
    (* scripts were generated in same the directory where `pg_upgrade` was ran)


    ## PostGIS
    PostGIS is the extension for Postgres that allows for working with geometry data types and doing GIS operations in Postgres.

    ### Common Commands

    - to enable PostGIS on a regular Postgres table do: `CREATE EXTENSION postgis;`
    - to enable PostGIS in a Postgres database do: `CREATE EXTENSION postgis;`

    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    - to support OSM tags do: `CREATE EXTENSION hstore;`

    - to update a column from table A with the number of points from table B that intersect table A's polygons:

    ```
    update noise.hoods set num_complaints = (
    select count(*)
    from noise.locations
    where
    ST_Intersects(
    noise.locations.geom,
    noise.hoods.geom
    )
    );
    ```
    ```
    update noise.hoods set num_complaints = (
    select count(*)
    from noise.locations
    where
    ST_Intersects(
    noise.locations.geom,
    noise.hoods.geom
    )
    );
    ```

    - calculating area in EPSG 4326:
    `alter table noise.hoods set area = (select ST_Area(geom::geography));`

    - inputing values for the geometry type after loading data from a CSV:
    `update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);`

    - adding a geometry column in a non-spatial table:
    `select addgeometryColumn('table_name', 'geom', 4326, 'POINT', 2);`

    ### Common Spatial Queries
    Find all rows in a polygon dataset that intersect a point:

    ```
    -- note: geometry for point must be lon lat
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    ST_Intersects(
    ST_Transform(
    ST_GeomFromText(
    'Point(-73.982557 40.724435)', 4326
    ),
    4326
    ),
    nyc_tenants_rights_service_areas.the_geom
    );
    ```

    Or using `ST_Contains`:

    ```
    SELECT * FROM nyc_tenants_rights_service_areas
    where
    st_contains(
    nyc_tenants_rights_service_areas.the_geom,
    ST_GeomFromText(
    'Point(-73.917104 40.694827)', 4326
    )
    );
    ```

    ### Spatial Indexing
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.

    - Basically the steps are:
    `CREATE INDEX table_name_gix ON table_name USING GIST (geom);`
    `VACUUM ANALYZE table_name`
    `CLUSTER table_name USING table_name_gix;`
    **Do this after making changes to your dataset or importing new data.**
    ***Do this every time after making changes to your dataset or importing new data.**

    ### Importing data to PostGIS
    #### Using shp2pgsql
    @@ -125,7 +357,13 @@ To import an OpenStreetMap extract in PBF format do:

    #### Using ogr2ogr
    Example importing a GeoJSON file into a database called nyc_pluto:
    `ogr2ogr -f PostgreSQL PG:"host='localhost' user='chrislhenrick' port='5432' dbname='nyc_pluto' password=''" bk_map_pluto_4326.json -nln bk_pluto`

    ```
    ogr2ogr -f PostgreSQL \
    PG:"host='localhost' user='chrislhenrick' port='5432' \
    dbname='nyc_pluto' password=''" \
    bk_map_pluto_4326.json -nln bk_pluto
    ```


    ### Exporting data from PostGIS
    @@ -141,10 +379,15 @@ If on Linux / Mac OS do this: `export GDAL_DATA=/usr/local/share/gdal`
    If on Windows do this: `C:\> set GDAL_DATA=C:\GDAL\data`

    **To Export Data**
    Use ogr2ogr as follows to export a table (in this case a table called `noise.hoods`) to a GeoJSON file (in this case a file called nyc_hoods_noise.geojson):
    Use ogr2ogr as follows to export a table (in this case a table called `dob_jobs_2014`) to a `GeoJSON` file (in this case a file called dob_jobs_2014_geocoded.geojson):

    ```
    ogr2ogr -f GeoJSON -t_srs EPSG:4326
    nyc_hoods_noise.geojson PG:"dbname='name_of_database' user='user_name' password='your_password'"
    -sql "SELECT neighborho, borough, no_complaints, area_sqm, complaints_sqkm, geom FROM noise.hoods"
    ```
    ogr2ogr -f GeoJSON -t_srs EPSG:4326 dob_jobs_2014_geocoded.geojson \
    PG:"host='localhost' dbname='dob_jobs' user='chrislhenrick' password='' port='5432'" \
    -sql "SELECT bbl, house, streetname, borough, jobtype, jobstatus, existheight, proposedheight, \
    existoccupancy, proposedoccupany, horizontalenlrgmt, verticalenlrgmt, ownerbusinessname, \
    ownerhousestreet, ownercitystatezip, ownerphone, jobdescription, geom \
    FROM dob_jobs_2014 WHERE geom IS NOT NULL"
    ```

    - **note:** you must select the column containing the geometry (usually `geom` or `wkb_geometry`) for your exported layer to have geometry data.
  24. clhenrick revised this gist Nov 18, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -104,7 +104,7 @@ Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexi
    - Basically the steps are:
    `CREATE INDEX table_name_gix ON table_name USING GIST (geom);`
    `VACUUM ANALYZE table_name`
    `CLUSTER table_name`
    `CLUSTER table_name USING table_name_gix;`
    **Do this after making changes to your dataset or importing new data.**

    ### Importing data to PostGIS
  25. clhenrick revised this gist Nov 18, 2014. 1 changed file with 40 additions and 5 deletions.
    45 changes: 40 additions & 5 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,33 @@
    PostgreSQL & PostGIS Cheatsheet
    ===============================
    ### Postgres
    The most common commands so I don't forget!

    ## Postgres
    - to start the Postgres server do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop

    - to have Postgres start everytime you boot your Mac do: `ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents` then to check that it's working after booting do: `ps ax | grep sql`

    ### psql
    ## psql
    psql is the unix command line tool for interacting with postgres.

    ### Common Commands
    - for doing admin type things log in as postgres: `psql postgres;`

    - to create a databse: `CREATE database-name;`

    - to connect to a database: `\connect database-name;`
    - to delete a database `DROP DATABASE database-name;`

    - to connect to a database: `\c database-name;`

    - to connect when starting psql use the `-d` flag like: `psql -d nyc_noise`

    - to quit psql do: `\q`
    - to list all databases: `\l`

    - to quit psql: `\q`

    - to grant privileges to a user: `GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;`

    - to enable the hstore extension ( for key : value pairs) do: `CREATE EXTENSION hstore`

    @@ -48,11 +59,22 @@ psql is the unix command line tool for interacting with postgres.
    COPY noise.locations (name, complaint, descript, boro, lat, lon)
    FROM '/Users/chrislhenrick/tutorials/postgresql/data/noise.csv' WITH CSV HEADER;
    ```
    - list all columns from a table in a db in alphabetical order:

    ```
    select column_name
    from information_schema.columns
    where table_schema = 'public'
    and table_name = 'bk_pluto'
    order by column_name;
    ```

    ## PostGIS
    PostGIS is the extension for Postgres that allows for working with geometry data types and doing GIS operations in Postgres.

    - to enable PostGIS do: `CREATE EXTENSION postgis;`
    ### Common Commands

    - to enable PostGIS on a regular Postgres table do: `CREATE EXTENSION postgis;`

    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    @@ -76,6 +98,15 @@ update noise.hoods set num_complaints = (
    - inputing values for the geometry type after loading data from a CSV:
    `update noise.locations set the_geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);`

    ### Spatial Indexing
    Makes queries hella fast. [OSGeo](http://revenant.ca/www/postgis/workshop/indexing.html) has a good tutorial.

    - Basically the steps are:
    `CREATE INDEX table_name_gix ON table_name USING GIST (geom);`
    `VACUUM ANALYZE table_name`
    `CLUSTER table_name`
    **Do this after making changes to your dataset or importing new data.**

    ### Importing data to PostGIS
    #### Using shp2pgsql
    1. Do:
    @@ -92,6 +123,10 @@ Or for the geography type above:
    To import an OpenStreetMap extract in PBF format do:
    `osm2pgsql -H localhost --hstore-all -d nyc_from_osm ~/Downloads/newyorkcity.osm.pbf`

    #### Using ogr2ogr
    Example importing a GeoJSON file into a database called nyc_pluto:
    `ogr2ogr -f PostgreSQL PG:"host='localhost' user='chrislhenrick' port='5432' dbname='nyc_pluto' password=''" bk_map_pluto_4326.json -nln bk_pluto`


    ### Exporting data from PostGIS
    #### Using ogr2ogr
  26. clhenrick revised this gist Oct 7, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -89,7 +89,7 @@ Or for the geography type above:
    `psql -d nyc_noise -f nyc_pediacities-hoods-v3-edit.sql `

    #### Using osm2pgsql
    To import an OpenStreetMap export in PBF format do:
    To import an OpenStreetMap extract in PBF format do:
    `osm2pgsql -H localhost --hstore-all -d nyc_from_osm ~/Downloads/newyorkcity.osm.pbf`


  27. clhenrick revised this gist Oct 7, 2014. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -88,6 +88,10 @@ Or for using the geography data type do:
    Or for the geography type above:
    `psql -d nyc_noise -f nyc_pediacities-hoods-v3-edit.sql `

    #### Using osm2pgsql
    To import an OpenStreetMap export in PBF format do:
    `osm2pgsql -H localhost --hstore-all -d nyc_from_osm ~/Downloads/newyorkcity.osm.pbf`


    ### Exporting data from PostGIS
    #### Using ogr2ogr
  28. clhenrick revised this gist Sep 29, 2014. 1 changed file with 9 additions and 6 deletions.
    15 changes: 9 additions & 6 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,13 +1,15 @@
    PostgreSQL & PostGIS Cheatsheet
    ===============================
    ### Postgres
    - to start the database do: `postgres -D /usr/local/var/postgres`
    - to start the Postgres server do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop

    - to have Postgres start everytime you boot your Mac do: `ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents` then to check that it's working after booting do: `ps ax | grep sql`

    ### PSQL
    ### psql
    psql is the unix command line tool for interacting with postgres.

    - to create a databse: `CREATE database-name;`

    - to connect to a database: `\connect database-name;`
    @@ -16,10 +18,6 @@ PostgreSQL & PostGIS Cheatsheet

    - to quit psql do: `\q`

    - to enable PostGIS do: `CREATE EXTENSION postgis;`

    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    - to enable the hstore extension ( for key : value pairs) do: `CREATE EXTENSION hstore`

    - to view columns of a table: `\d table_name`
    @@ -52,6 +50,11 @@ PostgreSQL & PostGIS Cheatsheet
    ```

    ## PostGIS
    PostGIS is the extension for Postgres that allows for working with geometry data types and doing GIS operations in Postgres.

    - to enable PostGIS do: `CREATE EXTENSION postgis;`

    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    - to update a column from table A with the number of points from table B that intersect table A's polygons:

  29. clhenrick revised this gist Sep 29, 2014. 1 changed file with 17 additions and 4 deletions.
    21 changes: 17 additions & 4 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,22 +1,35 @@
    PostgreSQL & PostGIS Cheatsheet
    ===============================
    ### Postgres
    - to start the database do: `postgres -D /usr/local/var/postgres`

    - or do `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start` to start and `pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop` to stop

    - to have Postgres start everytime you boot your Mac do: `ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents` then to check that it's working after booting do: `ps ax | grep sql`

    ### PSQL
    - to create a databse: `CREATE database-name;`

    - to connect to a database: `\connect database-name;`

    - to connect when starting psql use the `-d` flag: `psql -d nyc_noise`
    - to connect when starting psql use the `-d` flag like: `psql -d nyc_noise`

    - to quit psql do: `\q`

    - to enable PostGIS do: `CREATE EXTENSION postgis;`

    - to view columns of a table: `d\ table_name`
    - to enable PostGIS topology do: `CREATE EXTENSION postgis_topology;`

    - to enable the hstore extension ( for key : value pairs) do: `CREATE EXTENSION hstore`

    - to view columns of a table: `\d table_name`

    - to rename a column: `alter table noise.hoods rename column noise_sqkm to complaints_sqkm;`

    - to change a column's data type: `alter table noise.hoods alter column noise_area type float;`

    - to compute values from two columns and assign them to another column: `update noise.hoods set noise_area = noise/(area/1000);`


    - create a new table for data from a CSV that has lat and lon columns:

    ```
    @@ -40,7 +53,7 @@ PostgreSQL & PostGIS Cheatsheet

    ## PostGIS

    - to update a column from table a with the number of points from table b that intersect that table a's polygons:
    - to update a column from table A with the number of points from table B that intersect table A's polygons:

    ```
    update noise.hoods set num_complaints = (
  30. clhenrick revised this gist Sep 27, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    Postgresql & PostGIS Cheatsheet
    PostgreSQL & PostGIS Cheatsheet
    ===============================

    ### PSQL