Getting started with PostGIS

SELECT date_added
FROM mysteries
            

SELECT date_added
FROM mysteries
            

→ 2014-11-18T00:00:00Z

SELECT age(
    date_added,
    current_date
)
FROM mysteries
            

SELECT age(
    date_added,
    current_date
)
FROM mysteries
            

→ 1 day

SELECT ST_AsText(the_geom)
FROM mysteries
            

SELECT ST_AsText(the_geom)
FROM mysteries
            

→ POINT(-71.433105   27.362011)

SELECT ST_Distance(
    the_geom,
    ST_Point(0, 0)
)
FROM mysteries
            

SELECT ST_Distance(
    the_geom,
    ST_Point(0, 0)
)
FROM mysteries
            

→ 76.4942359652356

bikelane data: bit.ly/maptime-postgis1 & bit.ly/maptime-postgis2

PostGIS docs: postgis.net/docs/manual-2.0/reference.html

let's get the length of the bikelanes

SELECT *, ST_Length(the_geom)
FROM osm_bikelanes

SELECT *,
    ST_Length(
        ST_Transform(the_geom, 2263)
    )
FROM osm_bikelanes

SELECT *,
    ST_Length(
        ST_Transform(the_geom, 2263)
    )
FROM osm_bikelanes

(when dealing with lengths and areas, use ST_Transform)

let's get the centers of the bikelane segments

SELECT 
    ST_Centroid(the_geom)
    AS the_geom 
FROM osm_bikelanes

SELECT 
    ST_Centroid(the_geom_webmercator)
    AS the_geom_webmercator 
FROM osm_bikelanes

SELECT 
    ST_Centroid(the_geom_webmercator)
    AS the_geom_webmercator 
FROM osm_bikelanes

(if your geometries aren't showing up in CartoDB, use the_geom_webmercator)

where to the bike lanes overlap?

SELECT osm.the_geom_webmercator 
FROM 
    osm_bikelanes osm,
    dot_bikelanes dot
WHERE 
    ST_Intersects(osm.the_geom,
    dot.the_geom)

where don't they overlap?

SELECT osm.the_geom_webmercator 
FROM osm_bikelanes osm
LEFT JOIN dot_bikelanes dot 
    ON ST_Intersects(
        osm.the_geom,
        dot.the_geom
    )
WHERE dot.the_geom IS NOT NULL

weird things that aren't really useful for bikelanes

SELECT
    ST_Rotate(
        ST_Collect(
            osm.the_geom_webmercator
        ),
        -.1,
        ST_Centroid(
            ST_Collect(osm.the_geom_webmercator)
        )
    ) AS the_geom_webmercator
FROM osm_bikelanes osm

onward!

More about setting up PostGIS on your own computer: erictheise.github.io/geostack-deck/

More about PostGIS and CartoDB: academy.cartodb.com/courses/04-sql-postgis/lesson-1.html