More Geospatial GraphQL, GraphQL Architect

02:14 - Hey folks, welcome to the Neo4j twitch channel. 02:19 My name's Will, and today we're going to pick up 02:23 where we left off last time, 02:26 building a real estate search app with GRANDstack. 02:32 Let's just jump right in. 02:36 So, 02:38 www.github/johnymontana/willow-grandstack. 02:46 Let's paste that in the chat. 02:50 Okay, so there's the code. 02:53 What we did last time, 02:55 so just if this is the first time you're tuning in, 03:01 to give you an idea of what we're working on, 03:04 we're working on a GRANDstack application. 03:07 So that's GraphQL, 03:10 React, Apollo and Neo4j Database. 03:14 Oops, the architecture looks something like this 03:17 where the front-end of our application is 03:21 a React application using Apollo client 03:24 to query a GraphQL API 03:27 that is using this library called neo4j-graphql.js, 03:31 which makes it really easy to build GraphQL APIs 03:34 on top of the Neo4j graph database. 03:37 Inside Neo4j we have some data on 03:41 parcels, it's like properties that are for sale 03:45 and users searching for them, that sort of thing. 03:50 So you can think of this as kind of like a clone of Zillow, 03:53 if you're in the US 03:55 and you've seen real estate search apps like that, 03:57 there's a few different ones 03:58 where we're basically searching for properties 04:00 that are for sale viewing some details about them. 04:04 Cool, so we've been on this journey. 04:07 Let me jump in to the data directory on github. 04:11 So we've been on this journey of importing kind of like 04:15 a base layer for our application 04:17 that consists of property parcels, 04:22 a lot of geospatial data, 04:23 so the boundaries of the properties, 04:26 the latitude, longitude, single points of where they are. 04:29 Information about 04:31 the size of the lot, 04:34 what city it's in that sort of thing. 04:35 So we've, we pulled that data down from the state website. 04:39 Initially we're just doing one county 04:41 just to sort of figure out how this works. 04:43 So we pulled that down as a shapefiles, 04:45 converted that to GeoJSON. 04:48 And we started importing that into Neo4j using the 04:52 apoc.load.json procedure, which lets us stream in 04:56 JSON data and load that into Neo4j. 04:59 So this was nice, 05:00 I thought a really nice way of going from binary shapefiles 05:05 to a more human readable format and importing 05:10 as JSON. 05:13 Okay, cool. 05:14 So then we did some things like figured out 05:17 what city their properties are in. 05:20 We pulled in the latitude and longitude, 05:24 really a list of latitude and longitude 05:28 as a polygon property. 05:31 And then we also set a location property 05:35 it was just a single point. 05:37 So to kinda cheat, we described with the first property 05:41 in our list of points for the polygon. 05:44 And then we were doing some queries in Neo4j, 05:48 so here, so for example, let's say 05:52 given a point 05:55 this is GraphQL architect, we'll come back to that later. 05:58 What I want is Neo4j browser. 06:07 Here we go. 06:08 So this is Neo4j browser, so here's our data. 06:10 Let's do a call DB schema visualization. 06:20 Right. 06:22 And what happened there? 06:23 Oh right, the same thing that happened last time 06:28 where I added some additional 06:31 indexes. 06:36 That's okay, that's fine. 06:38 We can just ignore, we'll talk about in a minute 06:42 where these business category 06:46 review 06:48 and user indexes come from 06:51 'cause this is a good example. 06:53 If you remember last time when I opened up our database 06:56 we had a bunch of extra data and extra constraints in there. 07:02 So, we'll talk about where that comes from, 07:04 but ignore that for now. 07:07 For now we have style reset, we have just this data. 07:11 So, property nodes that are in a city, 07:16 let's just pick some properties kind of at random here. 07:21 So, 07:23 here's one 07:28 that is owned by LB Cattle Company, 07:33 so this is just vacant land 07:34 there's no address attached to it, 07:35 so we don't know what city it's in. 07:40 Let's, 07:42 pick a city, 07:43 here's Big Sky (indistinct) from Big Sky, 07:47 we can see, okay, here's a bunch of properties in Big Sky. 07:54 If we pick one, we can see some more detailed information 07:57 and including the boundaries of the property. 08:01 Okay, cool. 08:02 So that's kind of where we were last time. 08:09 We 08:11 can run some geospatial queries in Cypher. 08:15 So here's, let's say given a point 08:20 with latitudes and longitudes, 08:21 this is how we construct the points in Cypher. 08:25 And we can look at this on a map, 08:28 just so it's clear what we're talking about here. 08:34 Latitude, 08:38 come up longitude. 08:41 Okay, so here's this point, 08:42 this happens to be Montana State University 08:47 is where this point is 08:48 and the city of Bozeman let's zoom out real far 08:51 so we can see exactly where we're talking about here. 08:54 So this is in Montana in the US 08:58 and the County that we pulled in data is Gallatin County, 09:01 which is, I don't know, like roughly, 09:04 roughly this area on the map that we have data for. 09:09 So anyway, so given this point, 09:12 which happens to be in Montana State University, 09:18 let's 09:19 find 09:21 properties that are within a kilometer. 09:24 Well, that looks like this. 09:28 So imagine on a property where we have location 09:32 and the distance from this location point 09:36 and this point from Montana State University 09:39 is less than 1000. 09:40 So distance by default 09:43 is measured in meters. 09:46 So this is where the distance is less than one kilometer. 09:52 Cool here's properties that are less than a kilometer 09:55 from university. 09:58 But we can also work with polygon geometries. 10:03 And so towards the end of the session last time, 10:06 we installed this 10:09 spatial algorithms library. 10:12 And this adds some procedures 10:16 into Neo4j 10:17 that allows us for doing things like 10:20 point in polygon, working with distance between polygons, 10:26 polygon intersections, paste this in the chats as well. 10:32 Cool, so we installed that last time 10:34 and we saw an example query 10:41 like this, 10:47 but now says, okay, so for a given point, 10:50 we wanna know what property that point belongs to. 10:54 So we say match, 10:58 make this a little bigger, there we go, 11:01 match on a property node that has this polygon property 11:07 and spatial.algo.withinPolygon, 11:12 so that takes 11:14 a point 11:16 and the polygon property. 11:18 So this says, 11:20 find where this point is within the polygon property 11:25 on a node and return that node. 11:28 And we should find Montana State University, 11:36 which we did. 11:39 Cool, so this allows us to, 11:42 if we think back to like on the website, 11:45 we eventually wanna build, we're gonna see a map, 11:48 we're gonna click somewhere on the map. 11:50 And what we wanna do is figure out, okay, 11:52 well, what parcel does that point belong to? 11:56 Well on the back-end, this is how we're gonna do it. 11:59 We're gonna use this spatial.algo.withinPolygon 12:04 passing the point that we clicked on in the map, 12:06 figure out what parcel that belongs to, 12:08 and then annotate the map with that information. 12:13 Cool, so that's kinda where we left off last time. 12:16 We were just starting 12:19 to see 12:21 how we can use this in our GraphQL API. 12:25 And to do that, we were using GraphQL architect. 12:31 So GraphQL architect, which we talked about a little bit, 12:35 this is 12:37 a fairly new 12:40 GraphQL architect, 12:41 there we go, 12:42 www.github.com/grand-stack/graphql-architect 12:45 put that in chat. 12:48 So this is a fairly new graph app. 12:50 So a graph app is a single page application 12:54 that we can install within Neo4j desktop. 12:58 And it allows us to build things like developer tools 13:01 that make it easier to use certain features of Neo4j. 13:06 So, there's graph apps for lots of different things. 13:09 I mean Neo4j browser itself is a graph app. 13:12 Bloom, if you've seen 13:15 Neo4j bloom 13:18 for graph data visualization. 13:21 Bloom is a graph app that gives me 13:24 some really cool 13:26 graph visualization 13:30 features, 13:31 drop that in the chat. 13:34 And so GraphQL architect is another one of these graph apps 13:40 that in this case 13:42 allow me to build a GraphQL API 13:46 using sort of call it low code 13:50 features. 13:51 Underneath all of this, 13:52 it's using the same neo4j-graphql.js library, 13:56 so it just makes it easier 13:57 to run these Node.js graphql web servers on top of Neo4j. 14:04 Cool, so to install GraphQL architect 14:07 what we can do is you can either open this 14:10 Neo4j desktop link 14:12 that will deep link 14:12 into Neo4j browser to help you install it. 14:16 Or we can just copy this link 14:19 and then in 14:22 Neo4j desktop, 14:24 let's just close browser for now. 14:26 And Neo4j desktop, if we click on this graph applications 14:30 sidebar item pasted in here and click install, 14:34 but I've already installed it. 14:38 So that's how you install it initially. 14:41 Then to start it in the dropdown here, 14:44 choose GraphQL architect 14:47 and it will spin up 14:50 and connect to the database we have loaded. 14:56 And the Neo4j desktop, 14:58 started off it just gives me 14:59 these kind of dummy GraphQL type definitions, 15:03 but then I can quickly infer the schema, 15:06 this is my saved one from last time, 15:10 so I'm gonna clear that 15:11 and then let's 15:14 infer again, 15:18 there we go. 15:19 Okay, so I cleared out what I had saved last time. 15:23 And so now let's choose a port, 15:26 I think I have something running on 4000, 15:27 so I'll say on port 4007, 15:32 start GraphQL server. 15:34 This is going to now start a local GraphQL API 15:38 for me on local host 4007. 15:44 So I can toggle editor, 15:46 switch over to GraphiQL, 15:49 so GraphiQL allows me to query this GraphQL API. 15:54 I can look at the 15:57 documentations, 15:58 I see I have a property entry point. 16:03 So I can do things like search for 16:08 properties. 16:09 Oh, I don't know it just gives me a sort of 16:10 the first 10 at random. 16:12 And, 16:16 let's look at location, 16:19 oops, what, 16:22 curly braces, 16:24 not square brackets, 16:27 so latitude, longitude and then if we have it 16:33 let's try the address. 16:40 Addresses is null for a lot of our data, that's fine. 16:43 Let's try CityStateZ if we have it, 16:47 that's also null for a lot of things too, but 16:50 that's okay. 16:52 Okay, so one thing that's cool about 16:57 the neo4j-graphql.js integration 17:01 is that it adds these filter arguments. 17:05 So if I look at the query type 17:09 for 17:11 the property 17:14 query field, 17:15 so that's an entry point into the API, 17:18 there's a filter argument which is a property filter. 17:22 And so I have lots of different filters 17:25 that are auto-generated for each property. 17:29 I think we'll clean this up a little bit 17:32 because we have a lot of values 17:34 that aren't very useful here. 17:37 But one thing that we'll notice is 17:41 we have a 17:53 location filter as well. 17:55 So we have location distance 17:59 that it takes a Neo4j point distance filter. 18:04 So it takes a point in a distance, 18:07 and this filter allows me to do something very similar 18:12 to 18:13 jump back, 18:15 to github, 18:18 something very similar to this query 18:21 where I can give it a point 18:25 and a distance 18:26 and say only show me properties 18:28 that are within this distance at this point. 18:32 So let's give that a try. 18:36 I'm gonna copy this, 18:40 this latitude and longitude 18:42 for what was Montana State University. 18:46 If I jump back, 18:48 now I can say 18:51 filter 18:54 location. 19:01 Look at our documentation here. 19:06 New one is location distance. 19:13 Oops, 19:15 we first need a filter, 19:19 so the filter argument 19:22 is location distance 19:24 less than, 19:26 and then this takes an input, that takes a point. 19:33 I have my latitude and longitude, 19:36 oops, looks like too many curly braces there. 19:41 Latitude and longitude and then it takes a distance 19:46 which is in meters, so 1000. 19:49 So this is equivalent to my other query 19:53 that I was running in Cypher, 19:55 which is show me all the properties 19:57 that are within one kilometer of Montana State University, 20:00 but now 20:02 doing this GraphQL. 20:07 Cool, so I'd have to check these to verify, 20:09 but pretty sure. 20:12 Okay, and then we can do other filters, 20:17 for, if I'm interested only in 20:20 houses that have 20:25 so many square feet, so many bedrooms and so on. 20:29 The bedrooms and bathrooms piece, we'll talk about later 20:32 because we didn't have that in the data 20:34 we were working with previously. 20:36 So I wrote a scraper that will pull in some of that data. 20:39 But first let's finish off working with our GraphQL API. 20:47 Cool, so if you remember also last time 20:51 we added some things to 20:54 our 20:56 city type. 20:57 So if you remember last time we had something like 21:02 average value, 21:06 which was a float, 21:08 and then we had a Cypher directive. 21:13 Actually, by the way, let me before I 21:17 Yammer on too far, 21:18 I wanna point out the documentation for neo4j-graphql.js. 21:23 So this is all on 21:25 www.grandstack.io site, which has the documentation 21:30 for neo4j-graphql.js. 21:33 And specifically some of the things we talked about, 21:36 so there's the filtering 21:40 arguments that we add. 21:41 So on the documentation, we we have some examples 21:45 using like a movie schema that has GraphiQL, 21:49 so you can play around with some of the filtering arguments 21:55 that are included there. 21:56 And then also the page on 22:00 working with spatial data 22:03 and the point type. 22:06 There's also some more details in the documentation. 22:09 And what I'm gonna talk about next is 22:12 adding custom logic 22:14 to our GraphQL API. 22:16 So, 22:18 we saw how we can 22:21 sort of use these CRUD operations, 22:24 searching for properties, 22:27 doing things like ordering, pagination, like first offsets, 22:33 that sort of thing. 22:34 How we have these auto-generated filters, 22:37 including distance filters. 22:39 But what if I have some custom logic 22:43 that I wanna add to my API? 22:45 And the example we were starting to look at last time 22:48 is what if I wanna compute the average value for properties 22:53 in a city. 22:54 And we can do that using 22:56 the Cypher GraphQL schema directive, 23:00 specifically 23:02 to compute scalar fields. 23:05 So here's something very similar to what we wanna do, 23:08 where we're adding 23:10 averageStars field 23:13 to this is a schema that has 23:17 business reviews. 23:18 So the Cypher statement that we're binding to this field 23:23 says find all the reviews for this business 23:26 and return average of the stars property on the review node. 23:35 Cool, so we can do things just by kind of 23:39 augmenting the generated GraphQL type definitions, 23:43 which is nice. 23:44 But one thing that's cool in GraphQL 23:46 is this idea of type extensions. 23:50 So type extensions allow us 23:53 to extend types that are defined elsewhere in the schema. 23:58 This is really useful if your GraphQL type definitions 24:01 are split up into multiple files. 24:06 So here's an example 24:12 where we have type movie declared 24:15 and then later on somewhere in the GraphQL type definitions, 24:19 we are extending 24:21 type movie by adding some additional fields. 24:29 So, let's instead of adding this directly 24:33 on 24:35 the type city, 24:38 let's extend 24:40 type city 24:43 and we'll put average values here. 24:46 So the reason I like to do this 24:48 when we're working with neo4j-graphql.js, 24:52 and especially within GraphQL architect, 24:55 is it's a common pattern 24:58 where I'm maybe adding some data to Neo4j 25:01 and then I wanna go through the infer schema process again, 25:05 where it'll overwrite my existing type definitions. 25:09 And so it's nice to be able to have 25:11 any of my custom logic fields kind of defined up above here. 25:17 For now I can just kind of like copy those 25:20 and then paste them in after I run the type definitions. 25:23 But I think in future versions of GraphQL 25:26 architect will add, I think probably multiple tabs here 25:30 so that I can have my type extensions, maybe in one pane 25:34 and then overwrite the inferred schema in another. 25:39 So this allows me to kind of get like the Delta of 25:42 what's changed in the GraphQL, sorry, in the Neo4j database 25:46 kind of sync that with my infer to type definitions 25:50 and then have my custom logic 25:51 that I'm adding on somewhere else. 25:53 So it's nice to be able to keep my GraphQL type definitions 25:56 then in sync with the database that way. 26:00 Cool, so anyway, so for average value 26:03 it's gonna be matched this, where this is the city. 26:06 So I wanna know what are all of 26:09 the property 26:12 spelled that right property nodes 26:16 in the city. 26:18 And we're gonna turn average of p.TotalValue. 26:32 Cool, 26:32 so let's save that 26:37 and restart our GraphQL API. 26:44 And now, instead of querying for properties, 26:48 let's query for cities, 26:54 names, 26:55 and 26:57 their average values, 27:00 we have an error, 27:01 what did we do wrong there expected an Iterable. 27:08 What did we do wrong there, expected Iterable, 27:12 but did not find one or city.averagevalue. 27:17 Oh yeah, 27:18 I said that was a list 27:22 of floats, but it's just a single float, 27:25 single value for each city. 27:29 Save that, 27:32 restart our API. 27:36 Let's try that again, there we go. 27:39 And we can order by average value descending, 27:44 so show me the most expensive city first. 27:49 Okay, cool. 27:50 So that, 27:53 that allows us to add 27:56 computed scalar fields. 27:59 Another thing we can do is add 28:03 custom query fields. 28:06 So every GraphQL schema has 28:12 a query type, 28:14 in the case of neo4j-graphql.js 28:16 we generate this query type from your type definitions, 28:20 and we add a field for each type that you've defined. 28:22 You can configure that, 28:24 you can exclude certain types and so on. 28:29 But we can add custom query fields 28:33 which 28:35 basically just extends the generated 28:37 query fields that we add. 28:41 So for example, 28:49 let's use 28:57 this query where we were using 29:00 the spatial.algo.withinPolygon procedure 29:05 to find 29:07 properties 29:10 where we passed in a point, 29:13 and that point was then used to find 29:18 which property, which parcel belong to that point. 29:23 So let's call this propertyForPoint or something. 29:29 And this time we're going to pass in field arguments. 29:33 So latitude, 29:38 which is gonna be a float 29:41 and longitude which is gonna be a float, 29:45 this is gonna return a list 29:49 of 29:50 property types, 29:53 and we're gonna add the Cypher directive. 30:01 And in this case, 30:05 we're gonna run this Cypher query 30:07 that says, okay, find a property that has polygon property 30:14 and spatial.algo.withinPolygon, 30:18 for a point looking, comparing that point 30:21 to see where it is within a property 30:25 that has 30:27 a polygon property. 30:31 But instead of hard-coded latitude and longitude, 30:38 we are going to use Cypher parameters here, 30:42 latitude 30:43 and 30:45 longitude, okay. 30:47 So where are these latitude 30:49 and longitude Cypher parameters coming from? 30:54 Well, they're just coming from 30:58 the field arguments here. 31:01 So the user will specify a latitude and longitude, 31:04 and then the values will then be passed into 31:09 this Cypher statement. 31:14 Cool, so let's save this 31:17 and refresh our GraphQL API, and we'll see if this works. 31:26 So first off, if we take a look at our query type, 31:31 we can see, we have a new field property for points 31:35 that takes a latitude and longitude. 31:41 So let's start with that property for point latitude. 31:48 Let's use our same Montana State University example. 31:51 So, 31:54 latitude 31:57 45 something 31:59 longitude 32:00 negative 111 something. 32:11 Okay, and then this is going to evaluate to a property. 32:14 So be like CityStateZ up 32:17 and OwnerName. 32:20 And if this works, we should see Montana State University 32:25 as the property that we've found 32:28 and yep, looks like it is. 32:30 So, 32:31 we can get the location, you kind of already know 32:35 what the location is, 32:36 but we can get the boundaries now 32:40 of the latitude 32:42 and longitude for the polygon 32:45 that makes up the boundaries of the parcel. 32:47 So if we're thinking, okay, 32:49 from our 32:51 React web application, 32:54 we have a map, we click somewhere on the map, 32:57 it hits our GraphQL API sends this query that says, 33:01 find the property for that point, 33:03 the latitude and longitude that we're gonna pass in 33:05 is gonna be wherever I clicked on that map. 33:08 And what it's gonna return, 33:10 then what our GraphiQL API is then gonna return 33:12 to the front-end is this, 33:14 it's gonna say, "Hey, that's Montana State University." 33:16 Here's all of the latitude and longitude pairs 33:20 that you need to draw the polygon on the map, 33:23 so we can see, yep, that's where I clicked, 33:25 but now here's the parcel 33:30 that contains the points where you put on the map 33:34 and tell me more about that property. 33:39 Cool, 33:39 so that is good. 33:44 We are missing some important information though. 33:47 So from the shapefiles that we downloaded 33:52 last time 33:55 we got kind of some basic information about each parcel, 34:00 like the latitude and longitude things, 34:04 the owner in some cases, the address in some cases 34:09 and so on. 34:10 But what I really wanna know is for each property, 34:13 how many bedrooms does it have? 34:15 How many bathrooms does it have? 34:17 How many square feet is the house? 34:20 How many square feet is the lot? 34:22 That kind of thing. 34:23 So that information is available. 34:27 I'm sure this varies by state and county, 34:32 but in the case of Montana, 34:35 the state that we're working with, 34:37 this information is available in what's called 34:41 the property record card. 34:43 So there's a website where I can click through 34:48 for a given parcel 34:51 and look at like the history of the appraisals. 34:56 So for the last three years, how much was this house worth? 35:01 What were the property taxes 35:03 for any of the billet buildings on it? 35:06 What year was it built? 35:09 How many bedrooms does it have? 35:12 How many full baths? 35:13 How many half baths? 35:14 And so this is all useful information 35:17 that we need 35:19 for our real estate search application. 35:24 So I wrote a really 35:27 simple scraper in Python 35:31 to fetch this information. 35:35 There's probably some sort of API 35:39 where you can pull this data down, 35:41 I couldn't find it, but I found this. 35:44 So this is easy enough to scrape with beautiful soup. 35:49 So this is checked in on github 35:52 if you wanna see how this works, it's in the data directory. 35:57 It's an ipython notebook for readability. 36:02 And basically what this does 36:04 is first it queries Neo4j 36:08 for each property node 36:10 and grabs the ID 36:14 and just returns a big long list of those IDs, 36:17 those in the terminology 36:20 of the property record card site, 36:23 those are called geo codes. 36:26 So in this case, 36:28 we have almost 50,000 geocodes 36:31 so 50,000 properties we need to scrape information from. 36:35 Then there's a couple of functions here. 36:39 If you look at the URL for this, 36:42 it takes the geocode in a special 36:47 kind of format 36:48 with some dashes on it, 36:49 so we need to convert from the way that we have that stored 36:51 in the database to with some dashes. 36:54 These are the values that I wanna scrape off of this site. 37:00 So things like the category of the property, 37:02 is it residential? 37:03 Is it commercial? 37:04 What subdivision is it in? 37:06 How many square feet is it? 37:07 How many bedrooms does it have? 37:09 And that kind of thing. 37:12 So what we do is we just iterate through this list. 37:16 The URL is this URL, 37:18 plus the geocode 37:21 and then beautiful soup. 37:23 If you're not familiar with beautiful soup, 37:24 it's a great Python library for scraping, 37:28 I'm sure you can use it for other things as well, 37:30 I've only used it for sort of scraping websites. 37:33 It gives you lots of different ways to kind of iterate 37:36 the DOM using either sort of like jQuery style 37:40 or CSS selectors, or you can kind of walk the DOM 37:44 with its own sort of Python API. 37:49 So we do that 37:51 once for the appraisals, 37:55 which 37:56 is this table. 38:01 So in most cases, I think we have three years of appraisals. 38:06 So this is useful in our application, 38:09 if you remember, we want to show not only 38:14 like an estimate of what the house is worth, 38:16 but we wanna show like a chart going back in time 38:19 a little bit to show how the value of this house is, 38:23 has changed over time. 38:24 So this gives us a little bit of that information 38:28 going back in time. 38:30 Oops sorry, where were we? 38:32 Okay, so we do this twice, 38:34 we do it once for the appraisals 38:36 and then once for all the other information, 38:39 like the size of the house, bedrooms, bathrooms, and so on. 38:43 And then we write to two different CSV files. 38:48 So what I wanna do, 38:53 let's take a look at these CSV files 38:58 really quick here 39:01 and zoom in a little bit. 39:01 So one is called appraisals, so this one's really simple, 39:06 it has a bunch of rows, 39:09 the ID of the property, 39:12 the year of the appraisal. 39:14 And then the appraisal consists of the value for the land, 39:17 for any buildings, 39:19 this one I guess doesn't have any buildings 39:20 or maybe it has a worthless building on it. 39:22 And then the total of the appraisal, 39:26 and then there's different methods. 39:29 So they can, I don't know, cost is maybe looking at 39:33 if they have an actual sales record 39:35 or there's various methods for coming up 39:38 with these appraisals, I know what they are. 39:43 So there's that file 39:45 and then there's one called property features. 39:50 This one has a bit more information. 39:52 It has the ID of the property category, 39:56 it is address, subdivision, 39:58 the dates this information was last modified, 40:01 the neighborhood, lot size, 40:04 acres. 40:06 Lot size and acres it's kind of the same information, right? 40:10 I think the way this works in the dataset is if it is, 40:16 I don't know, up to like 10,000 square feet 40:19 or whatever like a normal lot size is 40:22 then you'll have square feet listed for lot size. 40:26 If it's a massive property, that's like, 40:27 I don't know a ranch or something, 40:29 then you'll have a value for acres. 40:32 This is kind of like an either or. 40:36 And an acre is 40:38 like 40:40 20,000 square feet. 40:42 Actually, I have no idea how many square feet an acre is, 40:45 maybe someone can chime in the chat and let me know. 40:49 But yeah, an acre is many, 40:53 many thousands of square feet. 40:56 Anyway, the year the house was built, the style, 41:01 this I think is something like, is it like conventional 41:05 is it split level, that kind of thing, 41:09 what sort of heating system it has, 41:11 this can be important if you live in Montana for the winter 41:15 to know if you have like electric heat or gas heat 41:19 or something. 41:20 A square foot of the house and then bathrooms 41:23 and bedrooms. 41:25 Okay, cool. 41:26 So what I wanna do is import this data into Neo4j now. 41:35 So let's 41:39 go to 41:42 Neo4j desktop 41:45 and open up Neo4j browser. 41:54 Okay, so, 41:58 also by the way, I've already 42:02 copied these in to the right directory. 42:06 If you remember, 42:12 manage, open terminal, 42:18 if you remember when we were doing this for 42:23 with our JSON data, 42:25 we wanted to have this in the import directory. 42:30 So I've already copied 42:34 my appraisals 42:35 except two appraisals_full, 42:37 I guess this is the one I want 42:38 and property_features_full.csv. 42:42 So I've already copied those where they need to be, 42:47 if we do pwd you can see here that open terminal in desktop. 42:51 The nice thing that, that does is it opens a terminal 42:55 specific to this instance of Neo4j that I'm working with. 42:59 So a nice feature there. 43:02 You can also get to it from here in the dropdown 43:05 and just select terminal. 43:07 Okay, anyway, so what we want to do, 43:12 let's take a look at 43:15 LOAD CSV WITH HEADERS FROM 43:18 "file:///property_features_full" 43:29 And let's just look at the first 10 rows. 43:32 So the 43:34 first row is a bunch of nulls kind of useless. 43:40 You can see again, this is not perfect, 43:43 we are missing a lot of data here. 43:47 But that's fine if we're missing data, we just won't use it. 43:52 We just want to set that. 43:56 So I guess the first thing we might wanna think about, 43:58 is there anything in here that we wanna pull out 44:02 into a node rather than just setting 44:07 properties on the property node itself. 44:10 So if we go back 44:12 to github, 44:17 to Willow, to the README. 44:23 Right, we said property, is in a city. 44:28 Is there anything else that we might wanna pull out of here? 44:32 And I think in this case, 44:37 the question I like to ask 44:40 to say, okay, should this be a property on a node? 44:44 Should it be a relationship? 44:45 Or should it be a node itself? 44:48 The question I like to ask is, well, 44:52 if I can pull this out into a node 44:54 and then by traversing through it, 44:57 is that gonna tell me anything useful? 45:02 If yes, then it should probably be a node. 45:04 If no, then it probably should just be a property. 45:08 So let's take acres as an example, 45:12 if I pull acres out and I say, okay, 45:13 here's a node that represents 109.02 acres. 45:21 By traversing through that, am I gonna traverse 45:24 to another property that has exactly 109.02 acres? 45:28 And then is that gonna give me something useful? 45:31 No, it's not. 45:34 But neighborhood on the other hand. 45:37 Okay, well, if I have houses grouped by neighborhood, 45:43 can I traverse from one house to a neighborhood node 45:47 to then more houses connected to that same neighborhood? 45:51 Is that gonna give me something useful? 45:53 Well, it might. 45:55 So for example, one thing we might wanna do 45:57 are find comparable homes. 46:00 This is useful when we're searching 46:03 for maybe to try to find a valuation for the property, 46:07 or if we're trying to just find other houses 46:10 that a user might be interested in. 46:12 Well, here's some other houses in the same neighborhood. 46:15 Yeah, that might be useful. 46:16 So neighborhood is something we might wanna pull out. 46:20 And then there's no value in this case. 46:22 But subdivision is also something we might want to pull out. 46:28 So subdivision 46:30 and neighborhood, we're gonna make those their own nodes. 46:35 So before we start importing data, 46:38 I'm going to create a constraint on 46:42 subdivision, D-I-V-I-S-I-O-N, 46:50 I guess well that right, 46:52 division looks kind of weird, but okay. 46:55 CREATE CONSTRAINT on (s:Subdivision) ASSERT s.name IS 47:01 UNIQUE. 47:04 So this will create a uniqueness constraint for subdivision. 47:07 And then we'll do the same thing for 47:12 neighborhood 47:16 assert 47:18 neighborhood.name is unique. 47:24 Okay, and now 47:26 let's start creating 47:29 some data. 47:30 So first thing we wanna do, and let's go back, 47:34 let's close these panes and go back to our list here. 47:38 So each one of these rows has an ID field. 47:44 That is how we're gonna match on our property. 47:51 So for each row in the CSV file match on the property, 47:55 and then we're gonna set a bunch of values, 48:00 a bunch of properties on this property node, 48:03 I really should've called this parcel 48:05 or something like that, instead of saying property, 48:09 a whole bunch of times, 48:10 property's become a bit overloaded but that's okay. 48:14 If you remember, when we were doing load CSV, we just kinda, 48:21 we had this object 48:23 called value 48:25 and we just did something like set P plus equals value. 48:31 And we didn't have to go through 48:33 and set each property name individually. 48:36 And in that case, that was really handy 48:39 because we didn't have to go through and pull out each field 48:44 and give it a name. 48:45 And we can't quite do that here 48:48 because 48:51 with load CSV, 48:54 we don't really have any types. 48:56 Everything is 48:59 assumed to be a string. 49:01 So here for example, here's acres, 49:04 well acres this is really a float, 49:08 but here it is represented as a string in the CSV file. 49:15 So what we're gonna do is go through each of the fields 49:18 that we wanna set as properties on our property node, 49:23 there's property overloaded again. 49:26 And we're gonna specify the name of the property 49:28 and then we're gonna cast 49:31 the value from our row object that comes from the CSV file. 49:36 We're gonna cast that to the type that we want. 49:40 So for example we have 49:44 square feet is gonna be 49:47 toInteger(row.sqft), 49:52 and 49:53 p.bedrooms. 50:03 What else do we have, full baths, 50:11 same for half baths. 50:20 I like to line up, 50:25 like that, more readable I think. 50:29 Half baths, what else do we have? 50:31 We have lot size, 50:35 lot size 50:37 is also an integer 50:39 as the number of square feet, square feet up the lot 50:44 where square feet is square feet of the house itself. 50:52 What else, we have acres, 50:55 acres 50:56 is 51:00 a float, it's a decimal. 51:07 You have year_built. 51:15 We have an address. 51:27 Subdivision, subdivision we said, we're gonna pull out, 51:33 you have a style. 51:39 We have type of heating. 51:47 And then we have a category, so this is like, 51:49 if it is residential, if it's commercial, 51:54 if it's mixed and so on. 52:00 Press Esc just to get a little bigger view there. 52:03 Okay, so I think that's good. 52:06 Let me get that a little spot check 52:09 to make sure I didn't spell anything wrong. 52:11 Yeah, it looks fine. 52:12 So that's good for the property values 52:15 that we wanna set from the CSV file. 52:19 But we also need to pull out the neighborhood 52:23 and subdivision. 52:25 So for that, we're gonna merge on subdivision. 52:33 So subdivision is gonna have a single property, 52:36 I guess that's gonna be named row.sub. 52:43 You saw it trying to auto-complete there 52:46 capital subdivision and then it was missing. 52:48 And if we remember the data we got from our shapefiles 52:52 that we converted to GeoJSON and imported, 52:54 it had subdivision as well. 52:58 But again was missing in some cases. 53:00 So we'll use this one. 53:02 So first merge on subdivision. 53:03 So, 53:06 if subdivision node exists 53:13 with 53:15 this name 53:16 for subdivision, 53:19 we're going to just match, 53:21 if it doesn't exist, then we're going to merge. 53:25 And livedataconcepts says I misspelled toInteger. 53:32 Thank you. 53:32 Is that this one toInteger 53:37 the other ones look good. 53:39 Cool, thanks for catching that. 53:41 Let me know if you see any other typos there. 53:47 Cool, so subdivision and then 53:55 merge to create a relationship 54:01 where let's just say in subdivision. 54:11 Cool, and then we'll do the same for neighborhood. 54:21 So I merge a neighborhood name 54:26 and then merge on a relationship to say in 54:32 neighborhood. 54:37 And I think I have some arrows backwards here. 54:41 We wanna say 54:45 where 54:47 the property is in 54:48 the subdivision 54:50 and the property is in neighborhood. 54:52 So going that way. 54:56 Okay, cool. 54:57 So I think, 55:00 that looks okay. 55:04 So a question for folks, 55:06 if I run this, are we gonna get an error 55:11 or is this going to work? 55:18 I think we're gonna get an error, 55:23 but can anyone tell me what the error is gonna be? 55:33 So let's go ahead and find out. 55:42 Cannot merge using null property value for name. 55:48 So what's going on here? 55:49 So if we remember, we have a bunch of nulls in our data. 55:53 So like here we don't have subdivision 55:57 and the problem is here. 56:01 We're merging on subdivision 56:05 where this value 56:07 row.subdivision is null. 56:10 And that's not something that we can do in Neo4j, 56:14 Neo4j doesn't know how to handle merging on a null, 56:17 null is not a valid value for a property, 56:22 so we can't set a property value equal to null. 56:28 I mean we can do that, 56:29 but really what happens is it just means 56:31 that, that property is not created. 56:34 So the semantics of merging on null 56:36 don't really make sense in this case. 56:38 So there's a few different ways to handle this case, 56:40 we could like filter out any rows where subdivision is null, 56:47 we'd have to do the same for neighborhood. 56:50 What I like to do in this case is 56:53 just kind of put in a default value, 56:56 which we can do with the function coalesce. 57:02 So coalesce is a function 57:05 that will evaluate to the first non null argument 57:10 that it has passed. 57:12 So in this case, 57:13 we can create like an N/A not applicable default value. 57:19 This will then have the side effect of creating 57:23 a subdivision node for N/A not applicable 57:29 and connect our nodes to it. 57:31 That's fine. 57:33 We just have to be kind of aware of that. 57:38 Anyway different trade offs for how we wanna handle that. 57:42 So we'll do the same for neighborhood 57:44 and let's see what happens. 57:47 Okay, so hopefully 57:48 this will 57:50 create some data for us. 57:54 How are we doing on time here? 57:57 Okay, so let's, I think I could go 58:00 for the rest of today's session is to finish up 58:04 our data import with this data that we scraped 58:09 and maybe see how we can refresh our GraphQL API. 58:16 And then next time 58:18 we will look at 58:20 connecting our React front-end to our GraphQL API 58:24 and starting to update that. 58:27 If you remember, so all this is loading. 58:32 If you remember last time I guess this was the first session 58:38 we started with the GRANDstack starter project 58:41 in our case just connecting it up 58:43 to a Neo4j sandbox instance. 58:45 And so our front-end was this kind of dashboard application 58:50 that used 58:52 some business reviews information. 58:55 So, what we'll start with next time is converting this 59:00 to instead be a dashboard of business reviews, 59:04 to instead each one of these components 59:06 will pull down some information from our GraphQL API, 59:11 using Apollo client and React Hooks. 59:14 And then I think the next step to go from there is 59:19 to add a map component to start to do some 59:25 map based visual analysis of some of this data. 59:33 Okay, so this is still loading here. 59:50 Give it some time. 59:52 I just wanna make sure that I 59:57 created the correct constraints and that we're using them. 60:00 So this isn't going to take forever to run. 60:13 Neighborhood, 60:16 so I see a problem. 60:17 So it looks like for some reason, 60:21 business category, neighborhood, 60:24 for some reason, 60:25 we lost 60:28 our constraint on 60:32 property. 60:35 So I'm gonna stop this, 60:43 and, 60:46 looks like we need to recreate 60:53 our constraint for property, 60:55 asserting p.id is 60:58 unique, 61:02 create constraint on 61:06 property, assert p.id is unique. 61:10 So the reason I did that, 61:15 is now we have an index on property ID. 61:19 And if you can see, 61:25 here we're using this match to match on property by ID. 61:31 And that should be a lot faster if we can take advantage 61:35 of that index. 61:42 One thing we can do, if we add an explain 61:47 to the beginning of our query, 61:53 we get a visual representation of the query plan. 61:59 And so, what I wanna do is make sure that, 62:06 zoom in a bit. 62:13 What we wanna do is make sure that we're using indexes 62:18 when we're doing more large scale data import. 62:23 So if I add an explain to the beginning of my Cypher query, 62:27 I can make sure that I'm not doing 62:30 a scan operation 62:32 and make sure that I'm taking advantage of any indexes 62:34 that I have. 62:35 So here we're using node unique index seek, that's good, 62:39 here we're using another, so this is for property. 62:42 Then for our merge statements 62:46 node unique index seek for subdivision, that's good. 62:50 And here's node unique index seek for neighborhoods. 62:54 So that way we can make sure that we're using the indexes 63:00 for 63:04 creating, or merging, or matching 63:07 when we're doing load CSV or any data import. 63:10 And that's gonna help us with performance. 63:11 And then also since we have those constraints, 63:14 make sure that we're not creating duplicate data. 63:20 Okay, so that was a lot faster this time, 63:24 that took 63:26 27 seconds. 63:30 So now if we, 63:32 let's 63:35 look at 63:38 a neighborhood, just pick one. 63:43 Neighborhoods don't really seem to have a meaningful name, 63:46 let's look at subdivisions, that sounds a bit more fun. 63:51 Okay, so subdivision here's, 63:57 Lazy Acres subdivision, that sounds nice. 64:01 So we can traverse out and see, okay, 64:03 here's some other, 64:05 here's some properties connected to Lazy Acres subdivision. 64:11 If we pick one of these, 64:14 we can see that it is 64:18 in the city of West Yellowstone, 64:23 that 64:25 it may be in 64:28 the subdivision. 64:30 But it's 64:31 also in this neighborhood. 64:35 If we traverse out 64:38 from some of these other nodes, 64:42 we may find that some of these, 64:47 in this case it looks like there's a one to one mapping 64:51 here of neighborhood subdivision. 64:54 But it may be in some cases that we have actually 64:58 some hierarchy here 65:00 where 65:02 a property maybe in 65:05 a subdivision 65:06 and that subdivision is in a neighborhood 65:10 and that neighborhood is in a city. 65:11 So instead of 65:13 kind of 65:15 the flat, 65:17 flatter structure we have now. 65:19 So if we do call DB schema visualization, 65:25 again, ignore review category 65:29 in business style reset. 65:33 Right. 65:34 So here we have kind of like 65:37 I don't know if you wanna call this like a star 65:39 sort of structure 65:41 where a property is in a city, 65:43 property is in a subdivision, 65:44 and a property is in a neighborhood. 65:46 Really, what we actually have is probably 65:50 this property is in a subdivision, 65:52 that subdivision is in a neighborhood, 65:54 that neighborhood is in a city. 65:55 So really we wanna represent 65:57 as some sort of a hierarchical structure. 66:01 We will see how we can fix that later on 66:06 using 66:09 the graph data science library, 66:11 and specifically some similarity metrics. 66:16 But for now we'll leave it. 66:19 Okay, so we imported some data, 66:22 the next thing that we want to do 66:27 is import 66:30 some 66:32 more data 66:34 from what we scraped. 66:35 So if we go back to our property record card, 66:39 we added things like, the living accommodations, 66:45 how big the house is, that kind of thing. 66:47 The other piece we wanna add are the appraisals. 66:52 So if we think about how we wanna represent this, 66:56 maybe let's jump into Arrows 67:03 and 67:05 let's 67:07 go in our README. 67:09 So Arrows, if you remember the last time we used it 67:13 to create this diagram 67:16 for our graph data model, 67:19 one thing that's nice about Arrows 67:21 is we can grab 67:23 the markup for it, 67:25 check that into version control. 67:28 And then if I go to export markup, 67:31 paste that in, I can live my graph. 67:36 Cool, so what I have now is something like this. 67:43 Neighborhood, her name is a string, 67:47 and this is in 67:51 neighborhood 67:57 and this is subdivision. 68:10 And the relationship is in subdivision. 68:15 Okay, so if something like this, 68:17 now I'm ignoring the listing part and the user part, 68:21 we haven't quite added those yet. 68:25 But now I have this data about appraisals. 68:30 And so for a bunch of different years, 68:33 I have 68:35 some values for the appraisal 68:37 that I wanna attach to my property including a year. 68:42 So how do we wanna model this? 68:46 Well, I think what we want to do 68:49 is create 68:51 an appraisal. 68:54 No, that's gonna be connected to our property 68:58 and it's going to have value, property is what year, 69:04 land that's the appraisal for the land value, 69:09 building that's the appraisal for any of the buildings 69:12 and then a total, just the total value of the appraisal. 69:15 And then a method. 69:20 And they'll say that the property has 69:25 appraisal. 69:31 And then each property, will just have multiple appraisals. 69:36 So for each year 69:39 we're gonna have one of these 69:42 appraisal nodes connected to the property. 69:49 Appraisal, 69:59 okay, so something like this. 70:00 So a property will have an appraisal for 2018, 70:04 for 2019, 70:05 2020 70:06 and so on. 70:10 Cool, there's one other property that we're going to need 70:14 to add here and we'll see why in a second. 70:18 So we're actually going to need to include the property ID, 70:25 okay just a string on the appraisals. 70:30 Can anyone tell me why 70:33 we're going to need 70:34 to include the property ID on the appraisal node? 70:49 Well, if we think about this 70:55 when we go to import our data, 70:57 let's take a look at that, first of all. 71:00 So load CSV with headers 71:03 from file:/// 71:08 appraisals_full, I think it was dot CSV 71:12 as row return row 71:15 limit 10. 71:23 So I could go through here and just create an appraisal 71:28 for each one of these rows in spreadsheet, 71:32 in the CSV file rather. 71:35 And, that would then be making the assumption 71:39 that there aren't any duplicates. 71:41 Now I don't really trust the simple Python scraper I wrote 71:47 or the 71:50 property record card sites 71:53 well enough to not have generated any duplicates. 71:56 And also in general, it's a good idea to have 72:01 item-potent data import. 72:04 Item-potent that means 72:05 that I can run it over and over again. 72:08 And I don't 72:10 have any side effects. 72:15 If I have the same data and I run my data imports, 72:18 then I have the same dataset, it doesn't impact my database. 72:23 So to do that, 72:24 I can't just create a node for each one of these rows. 72:28 I need to use a merge, 72:34 so I wanna like merge on the appraisal 72:40 and with merge we always wanna merge on the property 72:44 or properties that identify uniqueness. 72:49 And in this case, what identifies uniqueness 72:54 is a combination 72:56 of the year 72:59 and this value the ID, 73:02 which is the ID of 73:05 the property. 73:06 So what I wanna do is something like this. 73:18 Right. 73:19 And then like set building value equal to, 73:26 great so on. 73:28 I wanna do something like that. 73:30 So now you can see why property ID I included here 73:36 in the data model, 73:38 even though we have it here on the property 73:42 and I don't have it in here. 73:43 So property has an ID, it's a string. 73:49 So that's why we include it as a property on the node here, 73:53 even though we can find that same value by traversing 73:56 this has appraisal relationship. 74:00 Now, as we saw earlier, 74:03 it's really important to have a constraint in place 74:07 before we 74:09 go through our CSV file 74:12 and then start using merge for two reasons. 74:15 One is for performance, 74:17 it's gonna be faster to do an index lookup, 74:19 to see if that appraisal node already exists. 74:23 And then also the uniqueness guarantee is gonna guarantee 74:27 data integrity level at the database, 74:29 not to create any duplicates. 74:32 So, 74:34 how do we create a constraint 74:38 when it spans multiple properties? 74:42 Well, it turns out there's a 74:46 type of constraint 74:49 in Neo4j called the node key, 74:52 paste this in the docs, sorry, in the chat. 74:57 So a node key constraint uses a composite index 75:02 and asserts that a pair of properties, 75:07 zoom in here, 75:08 that a pair of properties both exists on a node. 75:13 And that, that pair 75:16 is unique. 75:18 So that's what we want to do is create this 75:24 node key constraint. 75:26 So, 75:29 create constraint 75:31 on, in this case, appraisal, 75:38 R-A-I-S-A-L 75:42 assert and just 75:44 for clarity here, 75:46 a is our appraisal, we're going to assert a dot 75:51 we're gonna call it property_id 75:57 and a.year. 76:00 So asserting that the combination of year and property ID 76:05 is unique because for each property, for each year, 76:09 we're gonna have a unique appraisal. 76:11 So create constraint 76:14 on 76:16 a appraisal, 76:17 assert a.property_id a.year is a node key. 76:22 Cool, and now 76:26 oops, trying to copy and paste, 76:29 going back to load CSV 76:32 with headers 76:35 from "file:/// 76:39 appraisals_full.csv" as row 76:44 return row in a 10, 76:46 that's what we had, yep. 76:48 Cool, so now we can, 76:51 match 76:56 on our property 76:59 looking at the row 77:01 and then merge on our appraisal 77:06 using both property ID 77:09 which is row.id and the year, 77:11 which is gonna be toInteger row.year. 77:16 Cool, so that'll give us unique appraisals, 77:18 then we can set a value, 77:23 toInteger 77:26 for row.land. 77:31 There's total value 77:35 row.total, 77:39 which is this one, 77:45 building, 77:52 row.building 77:56 line means up. 77:58 And then the last one is 78:02 method 78:07 toInteger row.method. 78:13 Okay, so that gets us the node. 78:14 Then we need the relationship. 78:18 So merge where the property 78:21 has 78:25 appraisal, 78:28 there. 78:30 Cool, let's give this a once-over, 78:33 load CSV with headers appraisals_full is row match, 78:38 or the property ID 78:41 is the ID from 78:43 our CSV 78:45 now merge on the combination of property ID and year 78:52 or appraisal, 78:54 set land, total, building, method, 78:58 merge property has appraisal, that looks good. 79:02 Let's give it a run. 79:04 And let's while this is running again, 79:09 if we add explain 79:12 to our query 79:14 and 79:15 take a look at the execution plan, 79:18 just make sure that yep, we're using an index, 79:20 which means we're gonna be using our, 79:23 in this case node key constraint. 79:25 We can see it here on appraisal property ID 79:28 and year combination. 79:32 Cool, so this, we should have 79:36 like three years of appraisals 79:40 for about 50,000 79:44 property nodes that we have. 79:46 So hopefully we should end up creating about 150,000 79:50 appraisal nodes here. 79:57 And yep, that's what we end up with. 80:00 It took 19 seconds. 80:04 Cool, so now if we do DB schema visualization, 80:10 again ignoring review business category, 80:15 we have now appraisals. 80:17 So a property has an appraisal, it's in a neighborhood, 80:19 it's in a subdivision, it's in a city. 80:24 Cool, let's 80:26 take a look again at one of our subdivisions 80:31 here's Lakeview condos, 80:34 there's only a single property in that. 80:40 You can see three years of appraisals, 80:43 let's do a style reset and see some captions there. 80:47 So Lakeview condo, it has three appraisals 80:53 for 2018. 80:55 The total appraisal was 80:58 668,000, 81:01 the previous year 640640. 81:05 Cool, so that, that looks like what we want. 81:08 And we can see this Lakeview condo is in this neighborhood. 81:15 Here's some other houses that are in that same neighborhood, 81:19 and now we can start to see. 81:20 Oh, here's a different subdivision. 81:23 So here's another timeshare condo 81:28 that is in this neighborhood, 81:34 but these properties are in a different subdivision. 81:38 So both of these subdivisions are in this neighborhood. 81:41 So you can see where that hierarchy starts to emerge. 81:44 So we'll need to start thinking about how we can extract out 81:48 that hierarchy. 81:49 But again, 81:50 that is an exercise 81:53 for a future time. 81:56 Cool, so the last thing I wanna do now 81:58 is jump back to GraphQL architect. 82:03 So let's switch here to GraphiQL architect, 82:08 toggle the editor. 82:11 So what I'm gonna do is copy 82:17 these pieces 82:18 that I added with custom logic, 82:23 going to clear my type definitions, 82:28 let's run in first schema again. 82:30 So now that I've changed data in the database, 82:34 I want to update the type definition 82:37 so now it includes subdivision, neighborhood, appraisal. 82:40 We can see, it also includes things like square foot, 82:46 number of baths and bedrooms and so on. 82:50 We'll add in my type extensions with my custom logic 82:55 and let's restart our GraphiQL API. 83:06 Okay, so now 83:09 what I can do 83:11 is something like 83:14 let's search for 83:18 properties. 83:19 Let's look at 10, but I'm gonna filter 83:23 first 83:24 10 pass in a filter, 83:26 and I'm going to want, 83:30 I don't know, at least four bedrooms, 83:34 let's get a massive house, at least four bedrooms, 83:39 full baths greater than, oh, I don't know, 83:44 I'm gonna need at least three full bathrooms 83:49 and we're gonna get 83:53 address 83:56 and city, 83:57 we're gonna see what city it's in. 84:00 I'm gonna see what subdivision it's in. 84:03 So in subdivision name. 84:09 Right, so this is the type of query I might run 84:14 in my search application where I'm specifying 84:19 some of the filters that I wanna add. 84:24 Remember I can also add in things like 84:29 within a certain section of the map, 84:31 within a certain distance and so on, right. 84:36 Maybe 84:38 I want this to be 84:41 location distance, 84:45 less than 84:48 point 84:53 latitude and longitude 84:59 and oops, 85:02 that's my point. 85:05 So this is Montana State University, 85:08 so I want a massive, 85:12 minimum four bedroom, three bath house 85:16 that is one kilometer or less from Montana State University. 85:22 And I can also 85:27 look at the recent appraisals, 85:33 year, total, and we can order 85:38 our appraisals 85:39 by year descending, 85:41 so it gives me the most recent appraisal first. 85:48 Cool, oh and let's be sure we return number of bedrooms, 85:51 and number of full bathrooms, and square foot, and lot size. 85:56 And maybe it has some acreage. 86:01 Okay, so here's 86:02 a 1600 square foot house 86:06 that is six bedrooms, four baths. 86:09 It is 86:12 worth $440,000. 86:16 Here's the address it's within a kilometer 86:18 from the university where I wanna live. 86:21 Great, so this is a relevant search result for me. 86:27 Okay, cool. 86:28 So I think that, that is 86:31 a pretty good coverage 86:33 for today. 86:35 So just to kind of refresh on what we did today, 86:41 we, 86:42 took a look at 86:45 using the spatial.algo.withinPolygon procedure 86:52 to find properties that belong to a certain point. 86:57 So point in polygon geospatial queries here, 87:02 we added that to our GraphiQL API. 87:05 We took a look at scraping data 87:09 using beautiful soup in Python 87:12 from the property 87:15 record card website. 87:18 We saw how to import that data into Neo4j, 87:22 adding things like neighborhood subdivision appraisals. 87:28 We saw how to use GraphiQL architect 87:32 to keep our GraphQL API in sync with the database 87:35 and also add in some of our custom logic 87:40 and the GraphQL API. 87:41 And then we saw how to structure some GraphQL queries 87:45 to search 87:48 for properties 87:49 using both 87:51 the filters 87:53 for things like a minimum number of bedrooms and bathrooms, 87:57 but also how to use the geospatial 88:02 in this case, 88:03 the point distance filter 88:07 but also how to use our property for point 88:12 query field and our GraphQL API. 88:15 Cool, so that was a lot to cover. 88:17 I think that was some good progress for today. 88:19 So I think we'll call it a day here. 88:22 Next time we will switch to 88:25 the front-end piece of our application again. 88:32 We will start with the skeleton that we have 88:37 from GRANDstack starter. 88:38 And we'll switch this to use 88:41 our 88:43 property GraphQL API 88:45 that we've been working on using Apollo client. 88:49 And then we'll see about adding a map component 88:53 so we can search on a map and annotate the map 88:56 with some of these properties. 88:59 Cool, so hopefully that was useful. 89:00 I'll update the github repo, 89:04 paste the link in one more time. 89:08 So update the GitHub repo with everything we covered today, 89:11 so you can see that 89:13 and then we will see you again 89:15 next Thursday 89:17 at 2:00 p.m. Pacific. 89:20 So thanks for joining today. 89:23 And we'll see you next time. 89:25 Cheers.