[MUSIC] Hello, and welcome to this lesson, which will focus on geometric spatial queries. A set of queries that is more specifically focused on extracting information on the nature of geometric properties, of spatial objects stored in the database. The objective of this lesson, is to explore, this world of SQL queries, that seek to extract geometric characteristic properties from spatial objects stored in a database. So that you are able to retrieve information such as the coordinates of a point, the perimeter or length of a line, the surface of a polygon, from spatial geometries posted in the database. There is the large number of different geometric spatial queries with a wide variety of keywords that we cannot. Obviously I'll review, so we will present you, some situations, some typical queries, which we consider particularly important. It's always a good idea to check out the sites of specialized softwares. In this case, the SpatiaLite site and PostGIS site to get a more complete idea, of all the available functionalities in terms of geometric spatial queries. In general the syntax, is pretty much the same in all cases with the particularity, however, in the PostGIS world, which, when. The queries on spatial objects, preferences the keyword, with the underlined letters, ST for Spatial Type. Which allows, in an SQL query, to clearly distinguish everything that is addressing spatial properties. And that they are specific to the PostGIS world versus PostgreSQL world, on which PostGIS is built. But for the rest, the syntax is the same as in SpatiaLite, with a few rare exceptions. In this lesson, we will first talk about the queries, that allow to select and display geometries, display in a map, or to consult the spatial reference used, [FOREIGN] so the ESPG code. We will then see some syntax of SQL queries, of data conversion to transform geometries in text or binary formats. Queries that allow to extract, to consult the type of geometry, with which we are dealing. And finally we will see a series of queries that allowe to retrieve the specific properties of geometries. [MUSIC] The geometry keyword typically, refers to the geometry attributes of a spatial data table. So the geometry of the spatial object, and it is used in selection queries, to display objects at a map. It could also be used to extract, the reference of the projection system used, or a lot of other information that we will see later. The syntax's based, simply on the SELECT, keyword, geometry. We select the geometries, from the name of the table to indicate the table, from which the geometries are extracted. Then, we can imagine conditional clauses which allowed to sort, in the geometries, those that meet certain criteria. We will see an example later, the SRID key word, with the geometry key word in brackets, to extract the reference of the projection system used, in this case the ESPG code. An example of the application of these elements. First, when it comes to selecting objects, the easiest way is to use the integrated tool in QGIS with a selection module which uses, The SQL language, but the select geometry from, is hidden. And we simply add the criterion in the SQL syntax, here, the name that begins with D. And we will select all the districts whose names begin with D. Second example, this time in the spatial lite model, again with the query in the districts table, to select all districts. To display the results of the query, they must be loaded as a new layer in QGIS, a layer whose name and field that contains the geometries, must be defined. Finally, just as we did with the QGIS query, we can change the spatial lite request, by introducing a conditional clause, to limit the selection of districts whose name begins with D. And again, we have to define the result of the query as a new layer to display the selection of districts in the map. To retrieve the projection system of these objects, we eliminate the conditional part of the query. And we add the SRID function that applies to geometries. By executing this query, we see that all our objects, or let's say a large part of our objects, are in 32 740, which is actually the UTM 40 subsystem. And we see with the district keyword, that the set of objects, have the same reference system. [MUSIC] The conversion functions, which also apply to the geometry attributes, that can transform, a geometry into a text format to make it readable. Or on the contrary, in binary format to store it, so that it takes up little space. These two functions are very useful to export, import, datasets from one database to another and convert them in another storage format. The syntax is always fairly simple, the SELECT keyword, the AsText function with the geometry in brackets, FROM and the name of the table. To illustrate this operation, we take the QGIS spatiaLite interface, And we simply write this request of transformation of the hotel geometries into text. And we give an alias to this column. We call it gg, so it comes from the hotel table. And we see that our column gg contains a series of multipoints, with the coordinates x, y. We can create a table from these results, that we will call table1 and we can add this table in the SpatiaLite database. We can't consult the objects of this table, we see that we find our MULTIPOINTS with the primary key that was added automatically. The equivalent function that transforms the geometry, Into a binary number, allows to observe in fact, That what we create is a geometric object, that we will be able to use for maps. Let's suppose now that we have obtained, imported this table that contains text geometries. We can use a GeomFrom Text function, which allows us to transform these texts geometries. It's a binary geometries, so geometric objects, that we will then be able to use to display them in a map. [MUSIC] A series of functions, that now allows to recover the characteristics of spatial geometry, starting with the type of geometry, the number of points or the number of rings, it could contain in its envelope. So the bounding box, the rectangle that encompasses the geometry, the syntax is always of select type. The function of GeometryType on envelope and the geometry key worded brackets, FROM and the name of the table. So we illustrate this type of queries with the function that allows to extract the type geometry of a layer. In this case we will focus on the district layer, And we see that the SQL syntax here is quite permissive. Since we can simply mention the geometry attributes, the name of the table, removing any ambiguity with the district keyword, we can check that the entire district layer is composed of MULTIPOLYGONS. If we now replace this extraction, A geometry type by the envelope function, we see that we create a series of Geometric Objects. And add this geometric object to the map, we must make it a geometry column to find the table name in which we were to store the To find the geometry field as contain in the geometry and execute the query. So this query creates a layer called on envelope. And we find the envelopes that include the different districts, of the island of Bahia here. [MUSIC] Among the many functions that return specific properties of spatial geometries, we will start simply by those returning the coordinates X Y of a point, with a very simple syntax of SELECT X(geometry), Y(geometry) if we want to extract the two coordinates FROM the name of the table. This syntax is illustrated by selecting the X and Y coordinates of the Seychelles hotels. So here again, simply the geometry keyword, the name of the table, removing any ambiguity. We see that the result of the Greek colony is in scientific notation. And so we can use a CAS-2 function towards an integer to transform the scientific notation into an integer number. To make it more readable. Second series of functions that concern. The polylines more with the extraction of the initial in the final points of the polyline, start point and point, the length, length and the test to check if the polyline is open or closed. And possibly a test to check of it is, a ring or not? So if it is part of a polygon the syntax is of the select type. The function in brackets the geometry, From the name of the table, as usual with the particularity, the fact that the link function that returns the length, of the polyline is preceded by a capital G in SpatiaLite. For the simple reason that the length keyword is a reserve word of spatialite and in QGIS. As I said at the beginning, this function is written ST for spacial type, underline and then the length keyword. As an example of this type of query, we will extract the length from the row sections, of the Seychelles roadmap. And we see that by executing this request, we have taken the length and meters of these road sections. Two functions which now concerned more specifically, the polygons, the centroid function, which returns the polygon barycenter, the center of gravity of the polygon and the area function that returns to its surface. The syntax is always the same with the select keyboard, the function, the geometry in brackets, the FROM keyword and the name of the table. So when our example here, we start by selecting by, extracting the surfaces of Seychelles districts. That's it, so we have again a notation of scientific type. If we now focus on centroid We obtain geometric objects That we may want to represent on the map. So to do this, we must load the result as a layer. In QGIS to find the geometry, the geometry field, with a geometry keyword, give a name to the table. And make sure that the geometry field is called geometry. By executing this request, we see that we create a layer called centroid. And if we display these centroid layers, we see that we have yellow dots that appear in the center of the Seychelles districts. [MUSIC] There you go, in this lesson we have seen that we could use the query integrated in the QGIS. To select spatial objects based on conditional queries, they use an SQL like syntax. And we also saw that we could use the SQL functions either in spatialite, we have not seen any example in postGIS, but it's the same thing. To extract properties or characteristics of geometries, which are stored in a database. So, considering the selection functions of geometric objects, the functions which allowed to extract the reference system used so the ESPG code. From the format conversion functions, to text or the binary to be able to transform, to graphically retrieve spatial objects, functions which allowed to extract proper characteristics of geometries like their type they're envelope, things like that. And finally, a series of functions that allowed to extract more numerical characteristics from space objects, the coordinates X, Y, the length of a line, the surface of a point, etc. [MUSIC]
