Cacher is the code snippet organizer for pro developers

#1 Upload the shapefile into the database:
sudo apt-get update
sudo apt-get install gdal-bin
ogr2ogr -f "PostgreSQL" PG:"host=localhost port=5432 dbname=gisdata user=bitasd1m_bs password=abbas"
-t_srs EPSG:4326 $PWD/tl_2017_48_bg.shp -overwrite -progress --config PG_USE_COPY YES -lco SRID=4326 
-nlt PROMOTE_TO_MULTI -nln public.bg tl_2017_48_bg -overwrite
tl_2017_48_bg.shp : shapefile name 
tl_2017_48_bg : name of layer (#to get name of the layer... go to the folder where the shapefile is located and type: ogrinfo .  )
public.bg : bg would be name of the table in public scheme

#2 Upload csv file into the database:
1) install pgfutter:
wget -O pgfutter https://github.com/lukasmartinelli/pgfutter/releases/download/v1.1/pgfutter_linux_amd64
chmod +x pgfutter

./pgfutter --help
2) upload:
./pgfutter --dbname gisdata --username ubuntu --pass abbas --schema public --table houston_2017_08_07 csv Houston-2017-08-07.csv;


#3 add geom column to the table:  ###source:https://gis.stackexchange.com/questions/256752/st-makepointlong-lat-hint-no-function-matches-the-given-name-and-argument-ty
##you might need to modify type of lat and lon columns first:
alter table latLng alter column lat type double precision using lat::double precision;
alter table latLng alter column long type double precision using long::double precision;
alter table latLng add column geom geometry(Point,4326);
UPDATE latLng SET geom = ST_SetSRID(ST_MakePoint(lon, lat), 4326);

CREATE INDEX merge1001_gix ON merge1001 USING GIST (geom);
ALTER TABLE merge1002 ADD COLUMN pr_id SERIAL PRIMARY KEY;
create index idx_id on sp30_combined (user_id);

## above 800 meters
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;

create table sp_workers as( with m2 as (select * FROM sp30_combined sp INNER JOIN workers_agg ON (sp.user_id = workers_agg.user_id)) 
select * from m2 WHERE ST_DWithin(m2.geom::geography, m2.workers_geom::geography, 800) = True);

### Create OD matrix from two tables and calculate the distance:

1.create table m2 as (select (sc).*, h.user_id as home_id, h.geom as home_g, h.lat as home_lat, h.lon as home_lon  FROM (select * from sp30_clusters_new) sc 
INNER JOIN homeOwners_agg_g h ON (sc.user_id = h.user_id));
2.alter table m2 add column homegeom geometry(Point,4326);
3.UPDATE m2 SET homegeom = ST_SetSRID(ST_MakePoint(home_lon::float, home_lat::float), 4326);
4.alter table m2 add column dist real;
5.UPDATE m2 SET dist= ST_DistanceSphere(m2.geom, m2.homegeom);

### Geotagging:

create table home_115_g 

as select 
h.user_id,
h.ext,
h.freq,
h.points,
h.lat,
h.lon,
h.geom as geom,
b.geoid as geoid 
from home_1500115 h,
bg_texas b
where 
st_intersects(h.geom, b.wkb_geometry);


create table sp_g as select s.arrivTime,s.lat,s.leaveTime,s.lon,s.pause,s.geom as geom,b.geoid as geoid from staypoints s,bg b where st_intersects(s.geom, b.wkb_geometry);

## import csv from table in postgres
\COPY products_273 TO '/tmp/products_199.csv' DELIMITER ',' CSV HEADER;


## select ones in a specific blckgp:
SELECT * FROM home_163k_homeinhouston where geoid_1 = 482019800001

## with edgar
shp2pgsql -s EPSG4326 /home/bitasd1m_bs/env/work/places/places.shp places | psql gisdata
create table intersect2 as select geoid from block_groups where ST_Intersects(geom, (select geom from places));

alter table houston_2017_08_01 add column geom geometry(POINT, 4326);

update houston_2017_08_01 set geom = ST_SetSRID(ST_MakePoint(cast(lon as double precision), cast(lat as double precision)), 4326);



### Get number of records for each user per day
select id, date, count(*) as cnt
from houston1_15
group by id, date
order by cnt desc;

## Get number of total records per user
`SELECT id, count(id) as cnt
FROM table_name            
GROUP BY id                
ORDER BY cnt DESC;

select id, date, count(*) as cnt
from houston1_15
group by id, date
order by cnt desc;

## creating geom and plotting it on the canvas on the fly:
select st_setsrid(st_makepoint(lon::double precision, lat::double precision), 4326), "date", "hour","minute","id" 
from houston1_15 where id='714f0736deb2efd00b3f8710f62b1187cc5d844f7edf2a2a9c10c89d013ba84c';