Cooking

With

@rhysallister

Cooking?

SELECT * FROM ingredients i
CROSS JOIN LATERAL (
  SELECT * FROM menulist m
  WHERE dish = 'Jerk Chicken Pasta'
  ORDER BY i.g <-> m.food
  LIMIT 4)
  
SELECT * FROM ingredients i
CROSS JOIN LATERAL (
  SELECT * FROM menulist m
  WHERE dish = 'Jerk Chicken Pasta'
  ORDER BY i.g <-> m.food
  LIMIT 4) as menu
  

Text

Foreign

Data

Wrapper?

Culinary

Epicurean

Food

Cooking?

Cooking?

A
Short
Story

Theft
Of
Electricity

P_d \approx \sum M
\sum M \div P_d = 0.75
ST_d \approx \sum ST_M

Except We Had No IDEA WHERE Each customer was located

We
Mapped
Them

~550,000

2 Months

OR

thought

SO

WE

410,000

Let's
Get
Started

rhys|1|# \dn
  List of schemas
  Name   |  Owner   
---------+----------
 bb      | rhys
 culprit | rhys
 public  | postgres
 service | rhys
 work    | rhys
(5 rows)

rhys|1|# 

rhys|1|# \dt service.*
           List of relations
 Schema  |   Name   | Type  |  Owner   
---------+----------+-------+----------
 service | info     | table | rhys
 service | location | table | postgres
 service | mrdata   | table | rhys
(3 rows)

rhys|1|# 
rhys|1|# SELECT * FROM service.location limit 3;
-[ RECORD 1 ]--------+---------------------------------------------------
premises             | 327937
g                    | 0101000020780D000090C2F52828242841EC51B81ECE5E2441
matchtype            | MRRDR
matchdate            | 2018-09-12
connectedtransformer | 253884
feederid             | Port Antonio - 410
-[ RECORD 2 ]--------+---------------------------------------------------
premises             | 327959
g                    | NULL
matchtype            | MRRDR
matchdate            | 2018-09-12
connectedtransformer | 244925
feederid             | Port Antonio - 410
-[ RECORD 3 ]--------+---------------------------------------------------
premises             | 327965
g                    | 0101000020780D00003E0AD7A3282B28411F85EB51005A2441
matchtype            | MRRDR_3
matchdate            | 2015-11-03
connectedtransformer | 244925
feederid             | Port Antonio - 410

Time: 0.669 ms
rhys|1|# SELECT * FROM service.mrdata limit 3;
-[ RECORD 1 ]--------------------
id       | 57743
routeid  | 20140702662
route    | 662
serv_cat | KWH
premises | 444443
reading  | 5823.00
readtime | 2014-07-08 11:37:40-05
-[ RECORD 2 ]--------------------
id       | 57744
routeid  | 20140702662
route    | 662
serv_cat | KWH
premises | 444444
reading  | 6626.00
readtime | 2014-07-08 11:37:48-05
-[ RECORD 3 ]--------------------
id       | 57745
routeid  | 20140702662
route    | 662
serv_cat | KWH
premises | 444445
reading  | 8781.00
readtime | 2014-07-08 11:37:58-05

Time: 0.730 ms
rhys|1|# SELECT * FROM service.info limit 3;
-[ RECORD 1 ]----------+----------------------------------------
premises               | 1000000
customer_name          | Rosalie Kathena Mckenzie
reading_route          | 21105
service_address_line_1 | Cedar Valley Dist  Redwood P.O.
service_address_line_2 | NULL
service_address_city   | Redwood P.O.
commercial_district    | SPAN
-[ RECORD 2 ]----------+----------------------------------------
premises               | 1000001
customer_name          | Edmond G Peart
reading_route          | 17714
service_address_line_1 | Albion  Mandeville P.O.
service_address_line_2 | NULL
service_address_city   | Mandeville P.O.
commercial_district    | MAND
-[ RECORD 3 ]----------+----------------------------------------
premises               | 1000002
customer_name          | Sonia Elizabeth Green
reading_route          | 15658
service_address_line_1 | METER 3 4 Bridgewater  Runaway Bay P.O.
service_address_line_2 | NULL
service_address_city   | Runaway Bay P.O.
commercial_district    | STAN

Time: 0.664 ms
rhys|1|# select routeid, count(g) count_good, count(premises), 
((count(g)/count(premises)::float) * 100)::int perc, min(readtime), max(readtime),
st_area(st_envelope(st_collect(g)))::bigint from service.location
join service.mrdata  using (premises)
group by routeid
limit 15;
   routeid   | count_good | count | perc |            min             |            max             |  st_area  
-------------+------------+-------+------+----------------------------+----------------------------+-----------
 20140701000 |        260 |   304 |   86 | 2014-07-07 10:08:57-05     | 2014-07-07 16:48:54-05     |   2391197
 20140701003 |        331 |   376 |   88 | 2014-07-07 10:02:50.373-05 | 2014-07-07 16:11:04.573-05 |  21334163
 20140701006 |        268 |   300 |   89 | 2014-07-07 08:56:11-05     | 2014-07-07 13:26:20-05     |    353505
 20140701009 |        269 |   304 |   88 | 2014-07-07 09:56:20-05     | 2014-07-07 16:32:04-05     |   3839576
 20140701012 |        233 |   268 |   87 | 2014-07-07 09:50:48-05     | 2014-07-07 14:49:55-05     |    479893
 20140701015 |        239 |   272 |   88 | 2014-07-07 09:18:26-05     | 2014-07-07 16:15:35-05     |  24515613
 20140701018 |        272 |   301 |   90 | 2014-07-07 09:43:50-05     | 2014-07-07 16:52:19-05     |  27511803
 20140701021 |        343 |   401 |   86 | 2014-07-07 10:04:18-05     | 2014-07-07 16:45:13-05     | 107731861
 20140701024 |        281 |   313 |   90 | 2014-07-07 10:50:21-05     | 2014-07-07 15:21:06-05     |  26734482
 20140701060 |        301 |   341 |   88 | 2014-07-07 09:51:16-05     | 2014-07-07 17:18:57.74-05  |    540686
 20140701062 |        465 |   541 |   86 | 2014-07-07 17:33:57-05     | 2014-07-07 22:43:58-05     |     87427
 20140701066 |        448 |   516 |   87 | 2014-07-07 09:28:58-05     | 2014-07-07 15:45:13-05     |    427327
 20140701069 |        155 |   184 |   84 | 2014-07-07 10:30:48-05     | 2014-07-07 15:25:30-05     |   4122944
 20140701071 |        166 |   184 |   90 | 2014-07-07 11:55:32-05     | 2014-07-07 17:35:26-05     |   1961990
 20140701073 |        277 |   309 |   90 | 2014-07-07 11:27:38-05     | 2014-07-07 17:16:13-05     |  13327587
(15 rows)

Time: 1096.013 ms (00:01.096)
rhys|1|# select premises, readtime, row_number() over (order by readtime), g 
from service.location
left join service.mrdata using (premises)
where routeid = '20140701069'
limit 50;
 premises |        readtime        | row_number |                         g                          
----------+------------------------+------------+----------------------------------------------------
 780337   | 2014-07-07 10:30:48-05 |          1 | 0101000020780D0000B81E85EB746F274115AE47E1FD5F2441
 952349   | 2014-07-07 10:32:01-05 |          2 | 0101000020780D0000AE47E17A456F27418FC2F52803602441
 381366   | 2014-07-07 10:33:15-05 |          3 | 0101000020780D000067666666156F2741D7A3703D04602441
 914622   | 2014-07-07 10:33:29-05 |          4 | 0101000020780D0000000000001A6F27413333333306602441
 761846   | 2014-07-07 10:34:16-05 |          5 | NULL
 728734   | 2014-07-07 10:34:30-05 |          6 | 0101000020780D000033333333056F27418FC2F52805602441
 958267   | 2014-07-07 10:35:18-05 |          7 | 0101000020780D0000A4703D0A036F27415C8FC2F533602441
 935104   | 2014-07-07 10:35:56-05 |          8 | 0101000020780D0000EC51B81E0E6F27416766666646602441
 381365   | 2014-07-07 10:36:50-05 |          9 | 0101000020780D000052B81E85076F2741E17A14AE41602441
 676528   | 2014-07-07 10:37:04-05 |         10 | 0101000020780D00005C8FC2F5086F274115AE47E14B602441
 966295   | 2014-07-07 10:37:19-05 |         11 | 0101000020780D00009A9999990C6F2741000000003F602441
 719462   | 2014-07-07 10:37:41-05 |         12 | 0101000020780D00007B14AE47136F27410000000038602441
 819668   | 2014-07-07 10:37:55-05 |         13 | 0101000020780D0000000000000E6F27419A99999943602441
 709659   | 2014-07-07 10:38:55-05 |         14 | 0101000020780D000033333333046F27410000000047602441
 641307   | 2014-07-07 10:40:34-05 |         15 | 0101000020780D000000000000FE6D27410000000039602441
 381356   | 2014-07-07 10:41:17-05 |         16 | 0101000020780D0000F6285C8FF26D2741713D0AD717602441
 876853   | 2014-07-07 10:43:00-05 |         17 | NULL
 552583   | 2014-07-07 10:45:06-05 |         18 | 0101000020780D000033333333C36D27410000000026602441
 381350   | 2014-07-07 10:46:29-05 |         19 | 0101000020780D0000F6285C8F4C6D2741C3F5285C3B602441
 799007   | 2014-07-07 10:48:02-05 |         20 | 0101000020780D0000CDCCCCCC186D2741CDCCCCCC0F602441
 753098   | 2014-07-07 10:48:42-05 |         21 | 0101000020780D0000CDCCCCCC1B6D27413333333325602441
 582980   | 2014-07-07 10:51:08-05 |         22 | 0101000020780D000015AE47E1E26C27418FC2F52817602441
 773046   | 2014-07-07 10:52:59-05 |         23 | 0101000020780D000000000000C26C2741CDCCCCCC66602441
 820375   | 2014-07-07 10:55:12-05 |         24 | 0101000020780D000033333333A56C2741CDCCCCCC66602441
 860612   | 2014-07-07 10:55:49-05 |         25 | 0101000020780D00009A999999BB6C27410000000066602441
 769917   | 2014-07-07 10:58:13-05 |         26 | 0101000020780D0000CDCCCCCCE56C274133333333F55F2441
 834402   | 2014-07-07 10:59:46-05 |         27 | NULL
 834464   | 2014-07-07 11:00:00-05 |         28 | 0101000020780D0000CDCCCCCCCE6C27419A99999901602441
 809796   | 2014-07-07 11:00:30-05 |         29 | NULL
 959247   | 2014-07-07 11:00:47-05 |         30 | 0101000020780D000000000000C66C274133333333FC5F2441
 381329   | 2014-07-07 11:01:01-05 |         31 | 0101000020780D000000000000CA6C27410000000005602441
 786300   | 2014-07-07 11:01:16-05 |         32 | NULL
 762940   | 2014-07-07 11:02:01-05 |         33 | 0101000020780D0000E17A14AEC36C2741295C8FC2F95F2441
 684699   | 2014-07-07 11:07:27-05 |         34 | 0101000020780D0000EC51B81E4A6E2741F6285C8FC2602441
 870003   | 2014-07-07 11:08:36-05 |         35 | 0101000020780D000000000000126E274100000000E0602441
 381364   | 2014-07-07 11:08:51-05 |         36 | 0101000020780D0000676666660B6E2741CDCCCCCCD2602441
 717360   | 2014-07-07 11:09:13-05 |         37 | 0101000020780D00009A9999990D6E274167666666D3602441
 820620   | 2014-07-07 11:09:50-05 |         38 | 0101000020780D0000333333331A6E27419A999999DD602441
 717663   | 2014-07-07 11:11:17-05 |         39 | 0101000020780D0000333333331F6E2741676666661B612441
 381363   | 2014-07-07 11:12:38-05 |         40 | 0101000020780D000067666666CD6D274167666666E0612441
 877833   | 2014-07-07 11:13:50-05 |         41 | 0101000020780D0000C3F5285CAD6D274185EB51B803622441
 723526   | 2014-07-07 11:15:06-05 |         42 | 0101000020780D0000CDCCCCCC886D27410000000024622441
 381358   | 2014-07-07 11:15:46-05 |         43 | 0101000020780D000033333333726D2741676666661D622441
 680240   | 2014-07-07 11:16:23-05 |         44 | NULL
 580317   | 2014-07-07 11:17:24-05 |         45 | 0101000020780D000067666666596D2741333333330A622441
 590036   | 2014-07-07 11:20:53-05 |         46 | 0101000020780D000033333333666D2741CDCCCCCCEA612441
 775853   | 2014-07-07 11:22:00-05 |         47 | 0101000020780D0000CDCCCCCC336D27419A999999CC612441
 827435   | 2014-07-07 11:23:12-05 |         48 | NULL
 689110   | 2014-07-07 11:23:27-05 |         49 | NULL
 545772   | 2014-07-07 11:23:43-05 |         50 | 0101000020780D00009A9999993B6D2741000000008A612441
(50 rows)

Time: 57.472 ms
rhys|1|# create table work.route_lines as
select '20140701069' route, min(readtime), max(readtime), 
max(readtime) - min(readtime) duration, st_makeline(g) g from (
  select premises, readtime, row_number() over (order by readtime), g 
  from service.location
  left join service.mrdata using (premises)
  where routeid = '20140701069'
  order by readtime) as foo;
SELECT 1
Time: 98.279 ms
select 
  routeid, 
  premises,
  st_lineinterpolatepoint(
    rl.g, /* the line geometry for point interpolation */ 
    /* date_part with epoch converts tstz to unix seconds */
    date_part('epoch',readtime - min)/date_part('epoch',duration)
  )
from service.location l
left join service.mrdata using (premises)
join work.route_lines rl on rl.route = routeid
where routeid = '20140701069' and l.g is null
order by readtime
rhys|1|# select routeid, premises, st_lineinterpolatepoint(
 rl.g, 
 date_part('epoch',readtime - min)/date_part('epoch',duration)
)
from service.location l
left join service.mrdata using (premises)
join work.route_lines rl on rl.route = routeid
where routeid = '20140701069' and l.g is null
order by readtime;
   routeid   | premises |              st_lineinterpolatepoint               
-------------+----------+----------------------------------------------------
 20140701069 | 761846   | 0101000020780D000058966B2C0A6F27419A46059647602441
 20140701069 | 876853   | 0101000020780D00008D4EC1EB376D2741F02C9AFD29602441
 20140701069 | 834402   | 0101000020780D0000A4266CBC1E6E274112464FAA15612441
 20140701069 | 809796   | 0101000020780D0000D854E522106E27413B5A96AD3F612441
 20140701069 | 786300   | 0101000020780D000035AACD14FE6D2741AF4E08296B612441
 20140701069 | 680240   | 0101000020780D0000EF30837CFF6C2741AED89F9692602441
 20140701069 | 827435   | 0101000020780D000036B1791A9C6C2741D897C70DBD612441
 20140701069 | 689110   | 0101000020780D000071A808119F6C274179C6442BC7612441
 20140701069 | 381347   | 0101000020780D0000A2C544B5136B27417A37DBCBDC622441
 20140701069 | 605449   | 0101000020780D000029B27B5C426A2741A0DF502EBE622441
 20140701069 | 821028   | 0101000020780D0000DE9A7EABD469274109057B1024622441
 20140701069 | 714388   | 0101000020780D0000A6D43C3A88682741001AA0580C602441
 20140701069 | 381333   | 0101000020780D0000BD0346ADB9652741993D7AED43622441
 20140701069 | 381327   | 0101000020780D00009C3F42D9C4642741C47F952C0F632441
 20140701069 | 381267   | 0101000020780D0000AF848A8915682741E3B2947EBA622441
 20140701069 | 805209   | 0101000020780D0000E6F5F10755682741342B26FD9D622441
 20140701069 | 381264   | 0101000020780D00000FEF1D36C3682741EE6CF5856C622441
 20140701069 | 742304   | 0101000020780D000074633A6115692741D03539A247622441
 20140701069 | 381281   | 0101000020780D0000A3A345E60D6A27414186AEADDB612441
 20140701069 | 969204   | 0101000020780D00005DD51F3D89682741392D370C8C622441
 20140701069 | 381290   | 0101000020780D00007DC0029464672741B68E75DA10632441
 20140701069 | 853185   | 0101000020780D000097C262446466274111B8FD2985632441
 20140701069 | 850942   | 0101000020780D0000295308081E6327410D64D8A065672441
 20140701069 | 381306   | 0101000020780D0000D22B65F2966227418B4EE1587D672441
 20140701069 | 976559   | 0101000020780D0000F059FBE7EE602741C7E8FE88026A2441
 20140701069 | 381316   | 0101000020780D0000D0C3D107B0602741ABF4575E4F6A2441
 20140701069 | 381318   | 0101000020780D000033653F735F6027414D09279ACB6A2441
 20140701069 | 381323   | 0101000020780D0000B654E805CE5F2741C261573F7C6C2441
 20140701069 | 783624   | 0101000020780D000019AD8F07DA5F274123BFE702316D2441
(29 rows)

Time: 60.376 ms

Questions?

Cooking with PostGIS

By rhysallister

Cooking with PostGIS

  • 81