Cacher is the code snippet organizer for pro developers

## selecting some fileds from a shapefile and save it to another shapefile:
ogr2ogr -f "ESRI Shapefile" -select "OBJECTID,ParcelID,Label_Curr,Label_Land" filter_select.shp clipedlanduse.shp

ogrinfo -q city_of_austin_parks.shp -sql "SELECT * FROM city_of_austin_parks" -fid 1

## getting distinct values for a field:
ogrinfo -q land_use4326.shp -sql "SELECT DISTINCT Label_Curr FROM land_use4326"

## see the columns:
ogrinfo -q cb_2017_48_tract_500k.shp -sql "SELECT * FROM cb_2017_48_tract_500k" -fid 1


ogr2ogr new_layer.shp city_of_austin_parks.shp -sql "SELECT PARK_LABEL AS NAME, PARK_ADDRE as ADDRESS FROM city_of_austin_parks
WHERE PARK_STATU = 'Developed'"

##Reprojection(from 4269 to 4326):
ogr2ogr -f "ESRI Shapefile" blkgp.shp tl_2017_48_bg.shp -t_srs EPSG:4326 -s_srs EPSG:4269

##The re-projection can also be invoked as part of a format conversion also:
ogr2ogr -f "MapInfo File" original.tab wgs84.shp -s_srs EPSG:27700 -t_srs EPSG:4326

## Also a good source:
http://www.sarasafavi.com/intro-to-ogr-part-i-exploring-data.html
https://live.osgeo.org/en/quickstart/gdal_quickstart.html

## convert a table to a shapefile using gdal
ogr2ogr -f "ESRI Shapefile" mydata.shp PG:"host=myhost user=myloginname dbname=mydbname password=mypassword" "mytable"


## select the points outside 800 meters radius of a point:
with m2 as (select * FROM most_comm_1002 m  
INNER JOIN home1002 ON (m.freq_n_id = home1002.home_id)) select count(*) from m2
WHERE ST_DWithin(m2.geom::geography, m2.home_geom::geography, 800) = False;

### Get number of 
SELECT DISTINCT LEFT(arrivtime, 10), COUNT(LEFT(arrivtime, 10)), user_id FROM sp30_subset GROUP BY (LEFT(arrivtime, 10),user_id)
ORDER BY user_id, left(arrivtime, 10);

### calculating jump lengths
SELECT ST_Distance_Sphere(geom, lag(geom) OVER (ORDER BY user_id,arrivtime)), arrivtime, user_id FROM sp30_subset;