Cooking with PostGIS
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 295 | |
| Author | ||
| Contributors | ||
| License | CC Attribution 3.0 Germany: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/43496 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
| |
| Keywords |
FOSS4G Bucharest 201988 / 295
15
20
28
32
37
38
39
40
41
42
43
44
46
48
52
54
57
69
72
75
83
85
87
88
101
103
105
106
108
111
114
119
122
123
126
129
130
131
132
137
139
140
141
142
143
144
147
148
149
155
157
159
163
166
170
171
179
189
191
192
193
194
195
196
197
202
207
212
213
214
215
216
231
235
251
252
263
287
00:00
Electronic mailing listGoodness of fitTwitterLecture/Conference
00:33
Connected spaceMathematical analysisAreaRight angleUtility softwareQuery languageComputer animationDiagram
01:40
Wrapper (data mining)Level (video gaming)Endliche ModelltheorieSocial classPhysicalismBitComputer animation
02:14
Right angleUtility softwareWell-formed formulaVideo gamePoint (geometry)Real numberNumberDrop (liquid)Form (programming)Proper mapQuicksort
02:52
Power (physics)Data transmissionUniform resource locatorNumberProduct (business)Right anglePower (physics)Coordinate systemInsertion lossMetreLevel (video gaming)Exception handlingArithmetic meanGreen's functionCuboidInformationCore dumpSound effectMachine visionAssociative propertyLocal ringRow (database)WeightComputer animation
05:01
Frequency
05:14
Student's t-testComputer worm40 (number)Computer animation
05:32
MappingArithmetic meanResultantTable (information)Lecture/ConferenceComputer animation
05:55
MetreTable (information)Uniform resource locatorNumberUniqueness quantificationInformationAddress spaceReading (process)
06:16
TimestampNumberInformationUniform resource locatorTable (information)Address spaceMetreReading (process)GeometryRow (database)Uniqueness quantificationAreaRight angleNeighbourhood (graph theory)Block (periodic table)RootBitComputer animation
08:18
Dot productRoutingRootLecture/Conference
08:44
Network topologyMultiplication signMetreGeometryRight angleSource code
09:02
CuboidField (computer science)RootPoint (geometry)Right anglePlanningRoutingMereologyMultiplication signLevel (video gaming)Uniform resource locatorCovering spaceZoom lensMaxima and minimaTimestampAreaMetreLecture/Conference
10:38
Uniform resource locatorMultiplication signInternetworking
10:55
String (computer science)Point (geometry)Line (geometry)Lecture/Conference
11:18
Wave packetElectronic mailing listRoutingPoint (geometry)RootQuery languageRight angleMultiplication signGeometrySource code
11:32
String (computer science)Query languagePoint (geometry)Multiplication signSingle-precision floating-point formatDigitizingAreaRoutingRight angleLine (geometry)Greatest elementGoodness of fitComputer animation
12:18
Line (geometry)Point (geometry)Multiplication signDevice driverMetreTotal S.A.LengthString (computer science)CASE <Informatik>Right angleImpulse responseSurjective functionAreaRootTexture mappingDot productDerivation (linguistics)InterpolationBitLinearizationQuery languageSource code
13:40
Level (video gaming)Point (geometry)CASE <Informatik>NumberRight angleInsertion lossLecture/Conference
14:08
Insertion loss
14:24
Presentation of a groupData miningMultiplication signAddress spaceBitOpen setAreaLevel (video gaming)NumberLocal ringControl flowAutomatic differentiationWebsiteEntire functionSquare numberNetwork topologyLecture/Conference
16:26
Multiplication signControl flowPresentation of a groupComputer animationLecture/Conference
16:43
Lecture/Conference
Transcript: English(auto-generated)
00:12
Good morning, everyone. How are you all doing? Great, all right cool. I'm Reese. This is cooking with post jus
00:24
Yeah So I am an infrequent tweeter and an even more infrequent co-contributor, but I am on GitHub and Twitter as Reese Alistair I along with two other folks are the Entirety of a small company called Octinome and we specialize in data connection and analysis especially for
00:47
utilities alright, so Cooking what do I mean when I say cooking?
01:00
Can I Do this is this I think can I select and I have my stove magically concoct some nice nice dishes You know I'm not speaking about that by the way who can identify what is wrong with this query anyone
01:22
No, is it either correct? I'm disappointed. It's missing That It's a subquery so you have to have a in the back, but yeah still I'm not speaking about cooking like this Although it would be very cool. If there was kind of a
01:43
culinary FDW No I May be appearing in our that's probably food How many of you did chemistry or physics at high school level show of hands Yeah, all right cool
02:01
How many of you had to do? Laboratory laboratory works or experiments to reinforce the models you learned in the class Okay, and how many of those experiments always worked? All Right so when I say cooking I'm really speaking about Fudging the numbers so if you have like a titration you're doing for example and
02:24
You're doing the little drops from the pipettes And it's not really working out how it should be working out at some point We start counting three drops as one four drops as one just to make sure that your your formula works out properly So that's what cooking really is, but then you wouldn't cook in real life
02:41
Would you like if you're a company? What would be the purpose of putting the numbers? Let's find out believe a very short story for utility company in Jamaica Electricity theft was a big deal and
03:01
First of all how many people they can steal lights or steal the power no one Yeah And so how do you quantify the theft of electricity? Well you know much poorer generating at the power plant and
03:21
You know how many meters you have or you know customer meters and customer locations so basically you add up the number of the consumption data You compare it to the production data, and it should be approximately equal right no more than say 2%
03:40
In Jamaica we were finding that we were losing about a quarter of the poll was being stolen So that was money down the drain or I guess a while So How do you come at this? One of the things we well we need to speak at a national level right to know that at the national level there were
04:04
That large amount of theft, but to come at it. We needed more localized information No luckily Luckily The power doesn't go directly from the power plant to your house. There is some infrastructure in between and
04:22
This green box right here is called a substation and each customer is connected to one substation So that was a way to localize the loss we can now calculate the losses at the substation level So it does the art for each of for each substation. We're delivering x amount of power
04:41
And we can now calculate for all the customers on that substation the poor consumed and Get the figure except that we had no idea where the customers were located Meaning we had no coordinates. No nothing at all. We neither were there. They were being built. They were collecting they were
05:02
Paying you know what some of them were paying money somewhere stealing So what you do when you don't know where people are you go and map them So in a two-month period Was a decade ago We mapped approximately a half a million customers and
05:21
All was well with the world Or so we thought because the reality was you can't map a half a million customers in two months using students Especially in the Caribbean when in in the middle of hurricane season So we were left with a problem because we only mapped about 400,000 of the half a million customers so we had a shortfall of a little over a hundred and forty thousand and
05:43
The funding for mapping was out. So we have to try other means Hence we tried to cook so we have the ingredients so We try some tables. I'll show you shortly and I'll get the whole cooking thing now. So
06:00
we have customers and We have people that go to every customer and read the meter Every month, so I'm going to show you three tables that we're using to the cooking today it's info location and MRI data the location table basically contains each customer's premises number that's a unique ID and
06:25
a location or coordinate The info table contains the same unique ID and then name address And some other information and in my data consists of the data that the meter reader
06:40
Collected you know what the meter reader is by the way Someone that actually goes and physically reads your your poem meter So you like walk from place to place go to one house another house and read each meter I'm going to show you what those tables look like. This is the location table So that first column with premises is the unique ID G is the geometry column
07:02
You notice that the second record the G is null. So this is a customer for which you have no location The MRI data table contains The time stamp of when the meter was read the actual meter reading and the premises number for our purposes
07:22
We're interested in the premises number, which again is unique ID and the time stamp and The last table is the info table, which I don't think I use quite a bit but this one contains address information and the premises number It's important to note that the meters are right in what are called roots. So
07:43
each meter reader would Have about 250 to 300 different houses to go to through their meters They were usually in a very small area like a small neighborhood like three or four or five streets what you might call a block So these areas were very localized, so this is what the customer
08:06
Do where did that picture go there should be a picture of the customer base right here. All right, so
08:22
There are no pictures. All right, I'm going to ask you to imagine Jamaica with dots on it So Our first step is to basically get an idea for each route
08:44
how many customers there are and How many customers have no geometry and also at the same time? We're gonna basically see on this day the meter reader started That's right this works I can highlight it. All right, so for each room right here
09:12
At the route ID there were 260 customers with a location and 304 customers
09:20
Overall in that route that's about 86 percent coverage Let me scroll to my right and then min and max are when he started the route the route that day and when he finished We're gonna zoom in on a particular route
09:43
By the way, this SQL making sense to you people. Can you all see it? No, that sucks They should be another picture what I oh Here it is so this is the base map I was telling you earlier with the points that we knew about and
10:03
Okay, so this is These are the boning boxes of the roots clearly some boxes are very very big because there is us very poor data in there to begin with and We would ignore all these boxes for these roots For the smaller boxes are the roots we be concentrating on because that those roots are in a very small area and we can get good
10:24
data from that Alright, so what would the actual plan so we know the meter reader guy or girl walks a Certain path right he goes to every house and collects data with a timestamp. So the thinking was alright cool. He does that
10:40
He's also collecting data at Hoses for which you don't know the location and a small side note. It's like a decade ago before like They were ubiquitous GPS. So he didn't he could have at a time gone and collected our location, but the Devices he used didn't do that. So he just got the timestamp alone. So we're thinking alright, he goes around he walks each house
11:05
Walk to each house and rather data what if we were to build a Line string using the other points to get a routing of where he went We could have that so
11:20
And you can't have a stop, right? So this this query basically ordering all the points in a given route by time And you can see here That there is a blank a blank geometry right here a couple of them in the actual lists, but this route had about 15 I think empty
11:42
We're gonna build a line string By doing this, so this is Another query where I am selecting all the points I'm ordering at my time in the sub query and I'm dumping that into ST make line which accepts
12:03
Well, one of the incarnations is accepts ordered points to build a line string So when you did that, you'll have gotten this Lovely little Picture right here. So you can set the bottom right hand corner where it does one two and next single digits That's where he started and then he basically went along that area to the top at 184
12:23
We have our ordered line string, which is good what the next step and this is The fun bits as far as I'm concerned. All right, they're gonna use linear referencing so
12:42
Get the unknown points so using st line interpolate point we can How many of you have or how many of you have used any kind of linear referencing in post this before? One two few, okay So we're basically saying Given this line string of some length. We know what time here at the meter
13:03
We know the total length of time taken for the entire roots. We can get a percentage of The actual time and using that percentage derive a point based on where he would have been and But we did that using this query here, this is what the data looks like textually
13:24
Which is kind of useless, but then the beauty of it is all these red dots that you're seeing here were the interpolated points and That's what we derived from the line string In some areas of to make likes especially smaller roots you would find that when you actually went back a couple years later to actually
13:43
map with GPS The derived points were like right here and the GPS point was like right here. They were the very very accurate in some cases Not all so we actually did that got our Number of customers from the four hundred ten thousand to about five hundred and like
14:02
fifteen or five hundred thirty and We solved the problem because Yeah, we saw the problem of not having a customers to know generate the local losses for the company So that is how you get to the British after you've cooked with post this
14:22
any questions Well what happened to the customers that was found stealing electricity
14:48
If you're a phone stealing electricity depending on who you are and where you live Usually give you a chance we will what's called a back bill you Legally, we can only go back six months
15:02
So we will ascertain what you're using now, and then just times it by six and you pay out over time That's what happens to those people Mm-hmm some more questions. I have one question
15:22
Just to get to know is open street map data for you. Well you or how is it one more time? You know about open street map data. Can you make use of open street map data? Yeah, we do art the first of all this entire thing was a decade ago It's not anything new. I mean I know we're we have GPS is everything so we can go a map map stuff
15:43
We do use home street map data quite a bit at work and When I was giving it when I was preparing this talk a friend of mine said but why didn't you use geocoding for to find addresses to find like customers and The address data in Jamaica is not very good outside of the city, so you'll find like an entire
16:06
10 square mile area and everybody's address is literally st. Ants Bay st. Anne and that's it no street no tree number so more questions
16:27
Okay, looks like there are no more questions, so thanks Reese for the Presentation and hope you enjoyed the posk is session here and draw the conference everyone and now yes time
16:40
It's time for a break