important scripts

osm2pgrouting --addnodes --dbname gisdb --username postgres --password housepotato --file '/home/meetup-dev/Downloads/malaysia-singapore-brunei-latest.osm_01.osm'

sudo su postgres

Start server
pg_ctlcluster 11 main start

sudo systemctl start postgresql@11-main

locate pg_hba.conf

/etc/init.d/postgresql restart

osmconvert malaysia-singapore-brunei-latest.osm.pbf --out-osm -o=malaysia-singapore-brunei-latest.osm_01.osm

SELECT gid as id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM ways

SELECT * FROM pgr_astar(
    'SELECT osm_id as id, source_osm as source, target_osm as target, cost, reverse_cost, x1, y1, x2, y2 FROM ways',
    1674151284, 2517371826);

103.8245345,1.2848411 #Stephen

SELECT osm_id FROM ways_vertices_pgr
    ORDER BY the_geom <-> ST_GeometryFromText('POINT(103.87938659999999 1.3568938)',4326)
    LIMIT 1;
#Format is longtitude, then latitude

osm2pgsql -U postgres -W --create --database gisdb '/home/meetup-dev/Downloads/malaysia-singapore-brunei-latest.osm.pbf'

SELECT osm_id,name,way FROM singapore_point WHERE amenity = 'restaurant';

SELECT ST_MakePolygon(ST_GeomFromText('LINESTRING(103.747899 1.448418,
103.717000 1.455884,
103.672316 1.428042,
103.567125 1.208307,
103.645382 1.191688,
104.035741 1.280736,
104.049487 1.353505,
103.984832 1.401563,
103.948364 1.391565,
103.915053 1.425629,
103.878286 1.435270,
103.829075 1.477364,
103.747899 1.448418)'));

#The above polygon
0103000000010000000D000000836DC493DDEF5940EF004F5AB82CF73FD9CEF753E3ED59408B506C054D4BF73FF624B03907EB5940EA07759142D9F63F9EEFA7C64BE459402A7288B83955F33F55DB4DF04DE959400492B06F2711F33F16889E9449025A402CF52C08E57DF43FF0A485CB2A035A40478FDFDBF4A7F53F7903CC7C07FF5940D2890453CD6CF63F0A2DEBFEB1FC5940C02154A9D943F63FD446753A90FA5940BDAAB35A60CFF63F38A27BD635F85940F4E0EEACDDF6F63F99BB96900FF55940999A046F48A3F73F836DC493DDEF5940EF004F5AB82CF73F

SELECT ST_IsClosed(ls),
  ST_Equals(ST_StartPoint(ls), ST_EndPoint(ls)),
  ST_AsText(ST_StartPoint(ls)) = ST_AsText(ST_EndPoint(ls)) AS WKT_equal,
  ST_AsBinary(ST_StartPoint(ls)) = ST_AsBinary(ST_EndPoint(ls)) AS WKB_equal,
  ST_Distance(ST_StartPoint(ls), ST_EndPoint(ls))
FROM (
  SELECT '0103000000010000000D000000836DC493DDEF5940EF004F5AB82CF73FD9CEF753E3ED59408B506C054D4BF73FF624B03907EB5940EA07759142D9F63F9EEFA7C64BE459402A7288B83955F33F55DB4DF04DE959400492B06F2711F33F16889E9449025A402CF52C08E57DF43FF0A485CB2A035A40478FDFDBF4A7F53F7903CC7C07FF5940D2890453CD6CF63F0A2DEBFEB1FC5940C02154A9D943F63FD446753A90FA5940BDAAB35A60CFF63F38A27BD635F85940F4E0EEACDDF6F63F99BB96900FF55940999A046F48A3F73F836DC493DDEF5940EF004F5AB82CF73F'::geometry AS ls
) AS f;


#Boundary of singapore
103.747899 1.448418,
103.717000 1.455884,
103.672316 1.428042,
103.567125 1.208307,
103.645382 1.191688,
104.035741 1.280736,
104.049487 1.353505,
103.984832 1.401563,
103.948364 1.391565,
103.915053 1.425629,
103.878286 1.435270,
103.829075 1.477364,
103.717000 1.455884

#Make sure one place is within the other
ST_Within(geometry A, geometry B)

#Failed but remains for reference
SELECT osm_id,name,way FROM singapore_point WHERE amenity = 'restaurant' AND ST_Within(way, '0103000000010000000D000000836DC493DDEF5940EF004F5AB82CF73FD9CEF753E3ED59408B506C054D4BF73FF624B03907EB5940EA07759142D9F63F9EEFA7C64BE459402A7288B83955F33F55DB4DF04DE959400492B06F2711F33F16889E9449025A402CF52C08E57DF43FF0A485CB2A035A40478FDFDBF4A7F53F7903CC7C07FF5940D2890453CD6CF63F0A2DEBFEB1FC5940C02154A9D943F63FD446753A90FA5940BDAAB35A60CFF63F38A27BD635F85940F4E0EEACDDF6F63F99BB96900FF55940999A046F48A3F73F836DC493DDEF5940EF004F5AB82CF73F');

SELECT sp.osm_id,sp.name,sp.way FROM singapore_point as sp join
ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
103.717000 1.455884,
103.672316 1.428042,
103.567125 1.208307,
103.645382 1.191688,
104.035741 1.280736,
104.049487 1.353505,
103.984832 1.401563,
103.948364 1.391565,
103.915053 1.425629,
103.878286 1.435270,
103.829075 1.477364,
103.747899 1.448418)')) as sp_poly on ST_Within(sp.way, sp_poly.st_buildarea)
 WHERE amenity = 'restaurant';

#St transform syntax
select (ST_Transform(way,4326)) as correct,amenity from singapore_point where amenity='restaurant'

#All osm_polygons that are restaurants
select osm_id, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_polygon
where ST_Within(ST_Transform(way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
103.717000 1.455884,
103.672316 1.428042,
103.567125 1.208307,
103.645382 1.191688,
104.035741 1.280736,
104.049487 1.353505,
103.984832 1.401563,
103.948364 1.391565,
103.915053 1.425629,
103.878286 1.435270,
103.829075 1.477364,
103.747899 1.448418)')),4326)) and amenity='restaurant'

#***********************All osm_points that are restaurantish ************************************
select * from (select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from
	(select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_polygon union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_point union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_roads union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_line) as food_places
	where ST_Within(ST_Transform(way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
	103.717000 1.455884,
	103.672316 1.428042,
	103.567125 1.208307,
	103.645382 1.191688,
	104.035741 1.280736,
	104.049487 1.353505,
	103.984832 1.401563,
	103.948364 1.391565,
	103.915053 1.425629,
	103.878286 1.435270,
	103.829075 1.477364,
	103.747899 1.448418)')),4326)) and amenity in ('restaurant','fast_food','food_court')) as result



select osm_id,amenity,ST_Transform(way,4326) from planet_osm_polygon where amenity='restaurant' limit 10000

select ST_Transform(way,4326) from planet_osm_polygon limit 10000








#Full function


SELECT * FROM pgr_dijkstra(
	/*The graph edge table*/
    'SELECT osm_id as id, source_osm as source, target_osm as target, cost, reverse_cost, x1, y1, x2, y2 FROM ways',
	/* The starting point */
    (SELECT osm_id FROM ways_vertices_pgr
    ORDER BY the_geom <-> ST_GeometryFromText('POINT(103.87938659999999 1.3568938)',4326)
    LIMIT 1)
	,
	/* All restaurants in Singapore */
	(select osm_id from
	(select osm_id, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_polygon union
	select osm_id, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_point union
	select osm_id, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_roads union
	select osm_id, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_line) as food_places
	where ST_Within(ST_Transform(way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
	103.717000 1.455884,
	103.672316 1.428042,
	103.567125 1.208307,
	103.645382 1.191688,
	104.035741 1.280736,
	104.049487 1.353505,
	103.984832 1.401563,
	103.948364 1.391565,
	103.915053 1.425629,
	103.878286 1.435270,
	103.829075 1.477364,
	103.747899 1.448418)')),4326)) and amenity in ('restaurant','fast_food','food_court') and osm_id = '3759860811')
	,
    FALSE
);


#-----------------------Create SG restaurants and add closest neighbour------------------
create table singapore_restaurants as select * from (select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from
	(select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_polygon union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_point union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_roads union
	select osm_id, name, amenity,  ST_Centroid(ST_Transform(way,4326)) as way from planet_osm_line) as food_places
	where ST_Within(ST_Transform(way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
	103.717000 1.455884,
	103.672316 1.428042,
	103.567125 1.208307,
	103.645382 1.191688,
	104.035741 1.280736,
	104.049487 1.353505,
	103.984832 1.401563,
	103.948364 1.391565,
	103.915053 1.425629,
	103.878286 1.435270,
	103.829075 1.477364,
	103.747899 1.448418)')),4326)) and amenity in ('restaurant','fast_food','food_court')) as result

ALTER TABLE singapore_restaurants
ADD COLUMN nearest_road_neighbour geometry;

ALTER TABLE singapore_restaurants
ADD COLUMN nearest_road_neighbour_osm_id bigint;

UPDATE singapore_restaurants tA1 SET nearest_road_neighbour =
(SELECT near_point.the_geom
FROM singapore_restaurants tA2,
LATERAL (SELECT the_geom
         FROM (select * from osm_nodes) tableB
         ORDER BY tableB.the_geom <-> tA2.way
         LIMIT 1) near_point
WHERE tA2.osm_id = tA1.osm_id limit 1);

#Create singapore_road_points v2
create table singapore_road_points as
	(select osm_id, osm_target_id, osm_name,  geom_way as way from osm_2po_4pgr
	where ST_Within(ST_Transform(geom_way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.747899 1.448418,
	103.717000 1.455884,
	103.672316 1.428042,
	103.567125 1.208307,
	103.645382 1.191688,
	104.035741 1.280736,
	104.049487 1.353505,
	103.984832 1.401563,
	103.948364 1.391565,
	103.915053 1.425629,
	103.878286 1.435270,
	103.829075 1.477364,
	103.747899 1.448418)')),4326)))

UPDATE singapore_restaurants tA1 SET nearest_road_neighbour =
(SELECT near_point.road_point
FROM singapore_restaurants tA2,
LATERAL (
	select way as road_point from (select * from singapore_road_points) tableB
	order by ta2.way <-> tableB.way
	limit 1
         ) near_point
WHERE tA2.osm_id = tA1.osm_id limit 1);

UPDATE singapore_restaurants tA1 SET nearest_road_neighbour_osm_id =
(SELECT near_point.osm_target_id
FROM singapore_restaurants tA2,
LATERAL (
	select way as road_point, osm_target_id from (select * from singapore_road_points) tableB
	order by ta2.way <-> tableB.way
	limit 1
         ) near_point
WHERE tA2.osm_id = tA1.osm_id limit 1);

#UPDATE singapore_restaurants tA1 SET nearest_road_neighbour =
#(SELECT near_point.geom_way
#FROM singapore_restaurants tA2,
#LATERAL (SELECT geom_way
#         FROM (select * from osm_2po_4pgr) tableB
#         ORDER BY tableB.geom_way <-> tA2.way
#         LIMIT 1) near_point
#WHERE tA2.osm_id = tA1.osm_id limit 1);

#UPDATE singapore_restaurants tA1 SET nearest_road_neighbour_osm_id =
#(SELECT near_point.osm_id
#FROM singapore_restaurants tA2,
#LATERAL (SELECT geom_way, osm_id
#         FROM (select * from osm_2po_4pgr) tableB
#         ORDER BY tableB.geom_way <-> tA2.way
#         LIMIT 1) near_point
#WHERE tA2.osm_id = tA1.osm_id limit 1);

#-------------------------------------test if pgrouting loaded properly---------------------
select ST_Transform(the_geom,4326) as ways from ways
	where ST_Within(ST_Transform(the_geom,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.850960 1.379987,
	103.851443 1.357377,
	103.880304 1.355543,
	103.879789 1.374592,
	103.850960 1.379987)')),4326))

#Testing from osm line
select ST_Transform(way,4326) as ways from planet_osm_line
	where ST_Within(ST_Transform(way,4326), ST_SetSRID(ST_BuildArea(ST_GeomFromText('LINESTRING(103.850960 1.379987,
	103.851443 1.357377,
	103.880304 1.355543,
	103.879789 1.374592,
	103.850960 1.379987)')),4326))


#Read in the file with osmosis instead, because osm2pgsql seems to suck
bin/osmosis --read-pbf /home/meetup-dev/Downloads/malaysia-singapore-brunei-latest.osm.pbf --log-progress --write-pgsql database=gisdb

#Try again with pg2sql
sudo osm2pgsql -c -d gisdb -H 127.0.0.1 -U postgres --input-reader osm '/home/meetup-dev/Downloads/malaysia-singapore-brunei-latest.osm_01.osm' -W

#Some testing stuff in sgoon gardens
4488193227 and 394634458
#-------------------Entering the routing data-----------------------
PSQL="psql"
PGPORT=5432
PGHOST=localhost
PGPASSWORD=housepotato
cd hh
${PSQL} -U postgres -d mydb -q -f "hh_2po_4pgr.sql"
pause

#-----------------Creating the db---------------
https://osm2po.de/

cd '/home/meetup-dev/Downloads/osm2po-4.7.7'
java -Xmx4G -jar osm2po-core-4.7.7-signed.jar tilesize=x '/home/meetup-dev/Downloads/malaysia-singapore-brunei-latest.osm.pbf'

sudo su postgres
psql gisdb
\i osm_2po_4pgr.sql

ALTER TABLE osm_2po_4pgr
ADD COLUMN road_start geometry;
UPDATE osm_2po_4pgr ta1 set road_start = ST_PointN(geom_way,1)


#------------------Finding the best location-----------------------

#Step 1: Get the closest node the the guy
SELECT osm_source_id as osm_id FROM osm_2po_4pgr
    ORDER BY geom_way <-> ST_GeometryFromText('POINT(103.87938659999999 1.3568938)',4326)
    LIMIT 1
(242936757,1830483603,4593402970) (joel, stephen, philip)

#Step 2: make array and calculate

select
	start_vid as start_user,
	agg_cost as cost_for_user,
	total_cost,
	name,
	st_x(st_centroid(way)) as longtitude,
	st_y(st_centroid(way)) as latitude
from
		(SELECT
			 start_vid,
			 end_vid,
			 agg_cost,
			 sum(agg_cost) over (partition by end_vid) as total_cost
		FROM pgr_dijkstra(
		'SELECT osm_id as id, osm_source_id as source, osm_target_id as target, cost, reverse_cost FROM osm_2po_4pgr',
		ARRAY[242936757,1830483603,4593402970]::bigint[],
		ARRAY(select nearest_road_neighbour_osm_id from singapore_restaurants))
		where edge = -1)
	as results
 join singapore_restaurants
 	on results.end_vid = singapore_restaurants.nearest_road_neighbour_osm_id
 	order by total_cost

#Step 2(Updated to have routes as well)
  WITH results AS
(
       SELECT results.*,
              osm_2po_4pgr.geom_way,
              osm_2po_4pgr.x1,
              osm_2po_4pgr.y1
       FROM   (
                     SELECT public_result.*,public_edges_2.transport_type,public_edges_2.type_id as transport_type_id
                     FROM   (
                                   SELECT *,
                                          singapore_restaurants_2.cost AS price
                                   FROM   (
                                                 SELECT *
                                                        --public
                                                 FROM   pgr_dijkstra( 'select id,source,target,x1,y1,x2,y2,cost,reverse_cost from public_edges_2', array[425482381]::bigint[], array
                                                        (
                                                               SELECT nearest_road_neighbour_osm_id
                                                               FROM   singapore_restaurants_2))) AS results
                                   JOIN   singapore_restaurants_2
                                   ON     results.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id) public_result
                     JOIN   public_edges_2
                     ON     public_result.edge = public_edges_2.id
                     UNION
                     SELECT *,singapore_restaurants_2.cost AS price, 'driving' as transport_type, 'nil' as transport_type_id

                     FROM   (
                                   SELECT *
                                          --driving
                                   FROM   pgr_dijkstra( 'SELECT  osm_id as id,osm_source_id as source, osm_target_id as target, cost, reverse_cost,x1,y1,x2,y2 FROM osm_2po_4pgr', array[4488019045]::bigint[], array
                                          (
                                                 SELECT nearest_road_neighbour_osm_id
                                                 FROM   singapore_restaurants_2))) AS results
                     JOIN   singapore_restaurants_2
                     ON     results.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id
                     UNION
                     SELECT *,singapore_restaurants_2.cost AS price, 'walking' as transport_type, 'nil' as transport_type_id
                            --walking

                     FROM   (
                                   SELECT *
                                   FROM   pgr_dijkstra( 'SELECT  osm_id as id,osm_source_id as source, osm_target_id as target, cost*kmh/5 as cost, reverse_cost*kmh/5 as reverse_cost,x1,y1,x2,y2,closest_outing_node,closest_restaurant_node  FROM osm_2po_4pgr where clazz!=11', array[4488019045]::bigint[], array
                                          (
                                                 SELECT nearest_road_neighbour_osm_id
                                                 FROM   singapore_restaurants_2))) AS results
                     JOIN   singapore_restaurants_2
                     ON     results.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id ) AS results
       JOIN   osm_2po_4pgr
       ON     results.node = osm_2po_4pgr.osm_source_id ), best_places AS
(
         SELECT   *
         FROM     (
                         SELECT place_id,
                                sum(agg_cost) OVER (partition BY end_vid) AS total_cost,
                                max(agg_cost) OVER (partition BY end_vid) AS max_travel_time,
                                min(agg_cost) OVER (partition BY end_vid) AS min_travel_time
                         FROM   (
                                       SELECT *
                                       FROM   results
                                       WHERE  price != 'None') results
                         WHERE  edge = -1
-- --                    AND    cast (price AS integer) < 3
-- --                    AND    cast (price AS integer) > 0
                         AND    rating > 4 ) results
         WHERE    max_travel_time   - min_travel_time < 100
         ORDER BY total_cost limit 5*cardinality(array[425482381, 4488019045, 243213958]::bigint[]) )
SELECT    path_seq,
          results.start_vid AS start_user,
          agg_cost          AS cost_for_user,
          total_cost,
          NAME,
          results.end_vid,
          geom_way  AS location,
          x1        AS longtitude,
          y1        AS latitude,
          st_x(way) AS restaurant_x,
          st_y(way) AS restaurant_y,
          results.price,
          results.rating,
          results.place_id,
          transport_type,
          transport_type_id
FROM       results
INNER JOIN best_places
ON         results.place_id = best_places.place_id
#------------------------------------Adding transport type--------------------------
SELECT * FROM
(SELECT *,
		singapore_restaurants_2.cost AS price
 FROM   (
			   SELECT *
	 --public
			   FROM   pgr_dijkstra( 'select id,source,target,x1,y1,x2,y2,cost,reverse_cost from public_edges_2', array[425482381]::bigint[], array
					  (
							 SELECT nearest_road_neighbour_osm_id
							 FROM   singapore_restaurants_2))) AS results
 JOIN   singapore_restaurants_2
 ON     results.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id) public_result
 JOIN public_edges_2
 ON public_result.edge = public_edges_2.id
--  AND public_result.end_vid = public_edges_2.target
#------------------------------------Adding transport type--------------------------


#------------------------------------cost and ratings requirements----------------------------
select * from singapore_restaurants_2 where cost = '3' or cost ='2' or cost = '1' or cost = '4' and rating > 3

#------------------------------------Speed tests for additional columns----------------------------
select * from small_singapore_restaurants restaurants, osm_2po_4pgr
where restaurants.way = '0101000020E6100000EC2B14763AF25940BB8A53D1A6B2F43F'
order by ST_Distance(restaurants.way, osm_2po_4pgr.geom_way)
limit 1
2.6s

select * from small_singapore_restaurants restaurants, osm_2po_4pgr
where restaurants.way = '0101000020E6100000EC2B14763AF25940BB8A53D1A6B2F43F'
order by restaurants.way <-> osm_2po_4pgr.geom_way
limit 1

1.1s

select * from small_singapore_restaurants restaurants, osm_2po_4pgr
where restaurants.way = '0101000020E6100000EC2B14763AF25940BB8A53D1A6B2F43F'
order by restaurants.way <#> osm_2po_4pgr.geom_way
limit 1
863ms

select * from small_singapore_restaurants restaurants, singapore_road_points
where restaurants.way = '0101000020E6100000EC2B14763AF25940BB8A53D1A6B2F43F'
order by restaurants.way <-> singapore_road_points.way
limit 1
62ms

-------------------------------- Using the google maps csv-----------------------
See python file upload_csv.py

ALTER TABLE singapore_restaurants_2
ADD COLUMN way geometry(Point, 4326);


UPDATE singapore_restaurants_2 ta1 SET way = ST_SetSRID(ST_MakePoint(long, lat), 4326);


ALTER TABLE singapore_restaurants_2
ADD COLUMN nearest_road_neighbour geometry;

ALTER TABLE singapore_restaurants_2
ADD COLUMN nearest_road_neighbour_osm_id bigint;

UPDATE singapore_restaurants_2 tA1 SET nearest_road_neighbour =
(SELECT near_point.the_geom
FROM singapore_restaurants_2 tA2,
LATERAL (SELECT the_geom
         FROM (select * from osm_nodes) tableB
         ORDER BY tableB.the_geom <-> tA2.way
         LIMIT 1) near_point
WHERE tA2.place_id = tA1.place_id limit 1);


UPDATE singapore_restaurants_2 tA1 SET nearest_road_neighbour_osm_id =
(SELECT near_point.osm_target_id
FROM singapore_restaurants_2 tA2,
LATERAL (
	select way as road_point, osm_target_id from (select * from singapore_road_points) tableB
	order by ta2.way <-> tableB.way
	limit 1
         ) near_point
WHERE tA2.place_id = tA1.place_id limit 1);

#-----------------------For bus routes--------------------------------
UPDATE bus_routes_locations tA1 SET osm_source_id =
(SELECT near_point.osm_target_id
FROM bus_routes_locations tA2,
LATERAL (
	select way as road_point, osm_target_id from (select * from singapore_road_points) tableB
	order by ta2.way1 <-> tableB.way
	limit 1
         ) near_point
WHERE tA2.index = tA1.index limit 1);

UPDATE bus_routes_locations SET reverse_cost = 1000000 where 1=1

------------------------------------ Running Server Backup -------------------------------------
pg_dump -U postgres -Fc -n public -t osm_2po_4pgr -t singapore_restaurants_2 --verbose -t outing_data -t public_edges gisdb  > C:\Users\Philip\Desktop\backup.bak

#Testing
pg_dump -U postgres --data-only -Fc -n public gisdb > C:\Users\Philip\Desktop\backup.bak

C:\Program Files\PostgreSQL\11\bin\pg_dump.exe --file "D:\\Documents\\dwqdwdwqsad" --host "localhost" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --data-only "gisdb"

PGUSER=postgres PGPASSWORD=housepotato heroku pg:pull DATABASE_URL mylocaldb -a meetup-mouse
-----------------------------------Google Maps linking-----------------------------------
https://www.google.com/maps/dir/?api=1&destination=47.5951518,-122.3316393

-----------------------------------------Combining the mrt, bus and walking maps------------------
-- For updating bus routes
CREATE TABLE bus_routes_locations_2 AS
SELECT * FROM bus_routes_locations;

update bus_routes_locations_2
set osm_source_id = osm_source_id + 1000000000000,
osm_target_id = osm_target_id + 1000000000000;

-- For updating mrt routes
CREATE TABLE mrt_map_edges_2 AS
SELECT * FROM mrt_map_edges;

update mrt_map_edges_2
set osm_source_id = osm_source_id + 2000000000000,
osm_target_id = osm_target_id + 2000000000000;

--Make sure to run python script

-- #Create the table
CREATE TABLE public_edges_2 (id SERIAL, source bigint, target bigint, cost double precision, reverse_cost double precision, x1 double precision, y1 double precision,x2 double precision,y2 double precision, transport_type varchar(255),transport_type_id varchar(255), PRIMARY KEY(id));

-- #For walking routes
insert into public_edges_2 (source,target,cost,reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id)
SELECT  osm_source_id as source, osm_target_id as target, cost*kmh/6 as cost, reverse_cost*kmh/5 as reverse_cost,x1,y1,x2,y2,'walking' as transport_type,'nil' as transport_type_id  FROM osm_2po_4pgr where clazz!=11;


-- #For bus routes
insert into public_edges_2 (source,target,cost,reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id)
SELECT osm_source_id as source, osm_target_id as target, cost, reverse_cost,x1,y1,x2,y2,'bus' as transport_type,bus_no as transport_type_id FROM bus_routes_locations_2;

-- #For mrt routes
insert into public_edges_2 (source,target,cost,reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id)
SELECT osm_source_id as source, osm_target_id as target, "Time" as cost, "Time" as reverse_cost, "Longitude_Start" as x1,"Latitude_Start" as y1,"Longitude_End" as x2,"Latitude_End" as y2,'mrt' as transport_type, "REF_STNSTART" as transport_type_id FROM mrt_map_edges_2;

--Add in the connecting waiting times
insert into public_edges_2 (source,target,cost,reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id)
SELECT osm_source_id as source, osm_target_id as target, cost, reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id FROM mrt_routes_waiting_times;

insert into public_edges_2 (source,target,cost,reverse_cost,x1,y1,x2,y2,transport_type,transport_type_id)
SELECT osm_source_id as source, osm_target_id as target, cost, reverse_cost,x1,y1,x2,y2,transport_type, transport_type_id FROM bus_routes_waiting_times;


-----------------------------FOR UPDATING OSM2PO4PGR------------------------
create table osm_2po_4pgr_2 as select * from osm_2po_4pgr;

insert into osm_2po_4pgr_2(x1,y1,osm_source_id,osm_target_id,osm_id,cost,reverse_cost)
select x1,y1,source,target,cost,reverse_cost,
source + 10000000000000 from public_edges_2

# --------------------------RUNNING THE DOCKER POSTGRES------------------------------
sudo docker run --name postgres -p 5432:5432 -v $HOME/Desktop/Meetup-Data/postgresdatadir:/var/lib/postgresql/data -d pgrouting/pgrouting:v2.6.3-postgresql_10

--------------temp test script--------------------
WITH results AS
(
       SELECT results.*,

              osm_2po_4pgr_2.x1,
              osm_2po_4pgr_2.y1
       FROM   (
                     SELECT *,singapore_restaurants_2.cost AS price, 'driving' as transport_type, 'nil' as transport_type_id
                     FROM   (
                                   SELECT *
                                                 --driving
                                   FROM   pgr_dijkstra( 'SELECT  id,osm_source_id as source, osm_target_id as target, cost, reverse_cost,x1,y1,x2,y2 FROM osm_2po_4pgr_2 where geom_way is not null ',
													   ARRAY[5113422345]::bigint[],
													   ARRAY[5258642515]::bigint[])) AS results
                     JOIN   singapore_restaurants_2
                     ON     results.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id

           )

        AS results
       JOIN   osm_2po_4pgr_2
       ON     results.edge = osm_2po_4pgr_2.id or results.edge = -1), best_places AS
(
         SELECT   *
         FROM    (
                         SELECT place_id,
                                path_seq,
                                sum(agg_cost) OVER (partition BY end_vid) AS total_cost,
                                max(agg_cost) OVER (partition BY end_vid) AS max_travel_time,
                                min(agg_cost) OVER (partition BY end_vid) AS min_travel_time,
                                max(path_seq) OVER (partition BY end_vid,start_vid) AS max_path_seq
                         FROM   (
                                       SELECT *
                                       FROM   results
                                       WHERE  price != 'None') results
                         WHERE  cast (price AS integer) <= 5
                         AND    cast (price AS integer) >= 0
                         AND    rating > 1.0 ) results
         WHERE    max_travel_time   - min_travel_time < 0.25
         AND path_seq = max_path_seq
         ORDER BY total_cost limit 20*1 )
SELECT    results.path_seq,
          results.start_vid AS start_user,
          agg_cost          AS cost_for_user,
          total_cost,
          NAME,
          results.end_vid,
          x1        AS longtitude,
          y1        AS latitude,
          st_x(way) AS restaurant_x,
          st_y(way) AS restaurant_y,
          results.price,
          results.rating,
          results.place_id,
          transport_type,
          transport_type_id,
          node,
          start_vid,
          end_vid
FROM       results
INNER JOIN best_places
ON                 results.place_id = best_places.place_id

#Making edge primary key id column
alter table osm_2po_4pgr_2 drop column id

ALTER TABLE osm_2po_4pgr_2 ADD COLUMN id SERIAL PRIMARY KEY;

#Probably working walking query
WITH dijkstra_result as (
SELECT *
		 --driving
FROM   pgr_dijkstra( 'SELECT  id,source,target, cost, reverse_cost,x1,y1,x2,y2 FROM all_osm_edges where transport_type = ''walking''',
			   ARRAY[5113422345]::bigint[],
			   ARRAY[5258642515]::bigint[])
), parsed_dijkstra_result as (
SELECT *,singapore_restaurants_2.cost as price
 FROM   dijkstra_result
 JOIN   singapore_restaurants_2
 ON     dijkstra_result.end_vid = singapore_restaurants_2.nearest_road_neighbour_osm_id
), edge_matched_dijkstra_result as (
	SELECT results.*,
				all_osm_edges.transport_type,
	all_osm_edges.transport_type_id,
              all_osm_edges.x1,
              all_osm_edges.y1
       FROM   parsed_dijkstra_result AS results
       JOIN   all_osm_edges
       ON     results.edge = all_osm_edges.id
), edge_matched_dijkstra_result_with_last_edge as (
select *,'end' as transport_type, Null as transport_type_id,lat as x1,long as y1 from parsed_dijkstra_result where edge = -1
	union
select * from edge_matched_dijkstra_result
order by path_seq
-- 	select * from parsed_dijkstra_result
-- select * from dijkstra_result
), best_places AS
(
         SELECT   *
         FROM    (
                         SELECT place_id,
                                path_seq,
                                max(agg_cost) OVER (partition BY end_vid) AS total_cost,
                                max(path_seq) OVER (partition BY end_vid,start_vid) AS max_path_seq
                         FROM   (
                                       SELECT *
                                       FROM   edge_matched_dijkstra_result_with_last_edge
                                       WHERE  price != 'None' and edge = -1) results
                         WHERE  cast (price AS integer) <= 5
                         AND    cast (price AS integer) >= 0
                         AND    rating > 1.0) results
         ORDER BY total_cost limit 20*1 )
SELECT    results.path_seq,
          results.start_vid AS start_user,
          agg_cost          AS cost_for_user,
          total_cost,
          NAME,
          results.end_vid,
          x1        AS longtitude,
          y1        AS latitude,
          st_x(way) AS restaurant_x,
          st_y(way) AS restaurant_y,
          results.price,
          results.rating,
          results.place_id,
          transport_type,
          transport_type_id,
          node,
          start_vid,
          end_vid
FROM      edge_matched_dijkstra_result_with_last_edge as results
INNER JOIN best_places
ON                 results.place_id = best_places.place_id



----------------Create the new lat long table---------------------
create table all_osm_edges (
id SERIAL primary key,
osm_id bigint,
x1 double precision,
y1 double precision,
x2 double precision,
y2 double precision,
transport_type varchar(255),
transport_type_id varchar(255),
source bigint,
target bigint,
cost double precision,
reverse_cost double precision
);
--insert osm_2po_4pgr
insert into all_osm_edges (osm_id,x1,y1,x2,y2,transport_type,transport_type_id,source,target,cost,reverse_cost)
select osm_id,x1,y1,x2,y2,'driving' as transport_type,Null,osm_source_id,osm_target_id,cost,reverse_cost from osm_2po_4pgr;
--insert public_edges_2
insert into all_osm_edges (x1,y1,x2,y2,transport_type,transport_type_id,source,target,cost,reverse_cost)
select x1,y1,x2,y2,transport_type,transport_type_id,source,target,cost,reverse_cost from public_edges_2


----------------Create a new table for the new food places blog----------
create table food_blog_places (
id SERIAL,
name varchar,
writeup TEXT,
postal_code int,
address TEXT,
lat double precision,
long double precision,
operating_hours TEXT,
pictures_url TEXT[],
cost_per_pax TEXT,
rating FLOAT,
max_price FLOAT,
min_price FLOAT,
food_place_link TEXT
);