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
- 190