# Suitability of Graph Database Technology for the Analysis of Spatio-Temporal Data
## Appendix A Queries
### Gsql  
Listing A12: A GSQL query that returns all the review text from reviews written by friends and mutual friends for businesses within 30 km of the Las Vegas center. ``` CREATEQUERYgetFriendReviewsInArea(VERTEX<Userp,DOUBLElat,DOUBLElon)FORGRAPHMyGraph{ TYPEDEFtuple<STRINGtext,INTstars>review; SetAccum<review>@reviews; SetAccum<VERTEX>@@FiF2; INTdistKm=30; users={User. *}; PSet={p}; Grids=to_vertex_set(getNearbyGridId(distKm,lat,lon),\"Geo_Grid\"); NearbyBusinesses=SELECT b  FROM Grids:s-(Business_Geo:e)-Business:b  WHERE geoDistance(lat, lon, e.LATITUDE, e.LONGITUDE) <= distKm;  F1 =  SELECT f  FROM PSet-(Friends)-User:f  ACCUM @@F1F2 += f;  F2 =  SELECT f  FROM F1-(Friends)-User:f  ACCUM @@F1F2 += f;  @@F1F2.remove(p);  FRreviewedBusinesses =  SELECT b  FROM users:f-(Reviews)-Business:b  WHERE @@F1F2.contains(f);  NearbyFBusiness = NearbyBusinesses INTERSECT FReviewedBusinesses;  GetTheReviews =  SELECT b  FROM NearbyFBusiness:b-(reverse_Reviews:tgt)-User:u  WHERE MONTH(tgt.REVIEW_DATE) >= 11  AND MONTH(tgt.REVIEW_DATE) <= 12  AND @@F1F2.contains(u)  ACCUM @@reviews += review(tgt.TEXT, tgt.STARS);  PRINT @@reviews; } _Appendix A.4. Medical: Priority 1 Mean Response Times_

### SQL

Listing A13: A SQL query that returns the mean of the time it takes for ambulances to start and travel time to the hospital of priority 1 calls for a given hospital. SELECT avg(RES.time_to_ambulance_starts) as avg_ttas, avg(TNS.travel_time_hospital) as avg_tth  FROM priority PRI JOIN response RES ON RES.id = PRI.response_id JOIN transfer TNS ON RES.id = TNS.response_id  WHERE PRI.id = 1 AND ST_DWithin(RES.destination, ST_MakePoint(19.11, 63.67):: geography, 500);

### Groovy

Listing A14: A SQL query that returns the mean of the time it takes for ambulances to start and travel time to the hospital of priority 1 calls for a given hospital. g.V().hasLabel(\"Priority\").has(\"priority_id\", \"1\") .in(\"RESPONSE_PRIORITY\").hasLabel(\"Response\") .has(\"destination\", geoWithin(Gecomposable.circle(63.67, 19.11, 0.5))) .fold().aggregate(\"avg_ttas\").by(unfold().values(\"time_to_ambulance_starts\").mean()) .unfold().out(\"RESPONSE_TRANSFER\") .fold().aggregate(\"avg_tth\").by(unfold().values(\"travel_time_hospital\").mean()) .select(\"avg_ttas\", \"avg_tth\")

## Appendix A.4.3 Gsql

Listing A15: A SQL query that returns the mean of the time it takes for ambulances to start and travel time to the hospital of priority 1 calls for a given hospital. CREATE QUERY postSim() FOR GRAPH MyGraph {  SetAccum<STRING O@vSet;  AvgAccum @avgTth;  AvgAccum @avgTtas;  INT priority_id = 1;  FLOAT lat = 63.67;  FLOAT lon = 19.11;  FLOAT distKm = 0.5;  priorities = { Priority. * };  Grids = to_vertex_set(getNearbyGridId(distKm, lat, lon), \"Geo_Grid\");  ResponsesByGeo =  SELECT r  FROM Grids:s-(Dest_Geo:e)-Response:r  WHERE geoDistance(lat, lon, e.LATTUDE, e.LONGITUDE) <= distKm;  ResponsesByPrio =  SELECT r  FROM priorities:p-(Response_Priority)-:r  WHERE p.id == \"1\";  ResponsesByGeoAndPrio = ResponsesByPrio UNION ResponsesByGeo;  TargetRespomes =  SELECT h  FROM ResponsesByGeoAndPrio:h-(Response_Transfer)->Transfer:t  ACCUM @eavgTtas += h.TIME_TO_AMBULANCE_STARTS, @avgTth += t.TRAVEL_TIME_HOSPITAL;  PRINT @eavgTtas;  PRINT @eavgTth; }

_Appendix A.5. Medical: Second Resource to Transfer Patients_

### SQL

Listing A16: A SQL query that returns the total number of responses, grouped by priority of the call, that were the second resource and ended up transferring patients to a given hospital. SELECT count(*) as responses_by_prio FROM resource RSC JOIN response RES ON RSC.response_id = RES.id JOIN on_scene SCN ON SCN.response_id = RES.id JOIN priority PRI ON PRI.response_id = RES.id WHERE RSC.id = 2  AND ST_DWithin(RES.destination, ST_MakePoint(19.11, 63.67)::geography, 500) GROUP BY PRI.id;

### Groovy

Listing A17: A SQL query that returns the total number of responses, grouped by priority of the call, that were the second resource and ended up transferring patients to a given hospital.