SQL Queries
Database inconsistencies and anomalies have a very retrogressive effect on the performance of our databases ranging from insertion of data to query retrieval (Chakraborty and Doshi, 2018). Therefore all information in our databases needs to be stored well in our databases to ensure that there is the optimization of queries and ensure information is retrieved effectively at all times. Ideally, a well-structured and well-kept database has the ability to ensure the security of the data. A disorganized database is susceptible to attack simply because digital assailant has the ability to predict patterns of what is contained in the database. This poses a great to our customer data and finally makes customers lose trust in our companies.
Moreover considerably checking for the data demonstrated being provided for analysis a lot of anomalies have been exhibited by the datasets.
- Duplication: This happens when we have multiple records for a similar object but with a different syntax. For a particular mention there a lot of duplication inconsistencies have been identified in various cells of the excel for the data which will be imported into our database. When the excel column name depictedAltLabel Victoria is a duplicate record in different cells. To remedy this is to ensure that there are unique records for each and every entry into our database.
- Null values inconsistency: From the excel provided there are some columns in excel which does have values. The ultimate effect of this, when imported into the database, is to occupy unnecessary space making it difficult for the query parser (Kirkham et al., 2019). Essentially, when performing a select statement it becomes very difficult as the result set is unknown values. The only approach is to sanitize the data or use IS NULL to ensure that you’re not reading null values.
- Multiple values in a single cell. All records in the database should be stored as a single value in database tables. Therefore, multiple values affect the performance of our database hence normalization needs to be enhanced for our data. More so, we need the best way to ensure that you have your data stored in one cell, you need to ensure your tables are in first and 2nd normal form.
- Inconsistency in what is represented in the columns: for instance when you check the column depicted and statue in our excel provided, it is evident that the column represents ID. The records which were stored there varchar meaning the data stored is not consistent. To eradicate these issues validate your records to be stored in the database to only accept storing integers and not variable characters.
SQL Queries
SQL>CREATE TABLE creator_table (creator INT NOT NULL PRIMARY KEY,
CreatorLabel_Name varchar (255));
SQL>CREATE TABLE Statue_depiction_table(depicted INT NOT NULL PRIMARY KEY,depicted_Label varchar (255),depictedAltLabel varchar (255),depicted_Description varchar (255));
SQL>CREATE TABLE statue_table (statue INT NOT NULL CHECK(statue>0),
2 statueLabel varchar (255),
3 inception TEXT,
4 latitude varchar (255),
5 longitude varchar(255));
Query insertion
SQL>Insert into creator_table(creator,creatorLabel)VALUES(Q2267710,’Victoria’);
SQL>INSERT INTO statue__depiction(depicted, depictedLabel, depictedAltLabel,depicted_description)VALUES(Q25311639,’Nightingale’,’Lady with the Lamp’,’statistician’);
SQL>INSERT INTO statue_table(statue,statueLabel,inception, latitude,longitude)VALUES(18,’Victoria’,’1926-05-05’,’40.56’,’-0.234567’);
Part 2
Spatial Analysis.
Geospatial analytics makes use of data from all places to help identify information of significance and value. The dataset can be that of Gps, locations, restaurants, and mountains. In spatial terms, they are referred to as points of special interest. More essential our point of interest which will be represented or visualized is that one of fast food. In our analysis question, we are trying to establish the relationship between the available environment and fast food. The marker at this point is the first food. Which will identify a particular place in that particular environment.
Therefore geospatial dataset can be used to provide information that will contain an association of data measurement which can be mapped onto the map to act as a representation or a marker of a certain place.
The real world can be represented as discrete data, stored by its exact geographic location (called “feature data”), or continuous data represented by regular grids (called “raster data”) (Whitman, 2020). Of course, the nature of what you’re analyzing influences how it is best represented.
Assuming now we want to represent fast food as point interest using a dataset from greater_london_osm_point. When a query is performed the following representation is achieved.
Part 2: Spatial Analysis
The SQL responsible for the kind of representation above in the map is given by the following SQL statements.
Since the table greater_london_osm_point does not contain a geographical location for fast food we had to choose among many places where to achieve the information. Essentially, what happens is that marker that will be associated with our food is identified and marked as demonstrated by the map above.
When checking some other dimensionality of concerns that depict special analysis, we have to check things such as areas. Our point of interest ought to take an approximate area of acres. These will be our description import feature which will help us locate fast food in our maps during spatial analysis when it is visualized. When checking the dataset present in greater_london_osm_line the following data is depicted.
The information represented by the map shown above is output from the above SQL query.
Select*from greater_london_osm_line glol;
From the screenshot above when can attest that fast food business can be along the major highway which is represented as either residential primary or tertiary.
When we perform spatial analysis along which fast_food can be located along with one of our highways it is demonstrated that it is at the length of 0.0027106831. This distance is measured and it is easily visualized on the map where exactly such a place can be and it can enhance navigation.
The figure above represents various areas in which fast_food can be identified and located. In total, these areas can be visualized in spatial view and provide formidable approaches in locating the exact places. It has been identified, that fast_food can be located along the highway within the distance specified above.
SELECT ST_Area(geom)
FROM greater_london_loac gll
WHERE lad11nm LIKE ‘Enfield%’;
Spatial analysis has become one of the common concepts of interest which have captured the attention of many surveyors, cartographers, and geologists so as to represent their point of interest in a very dignified way(Rim, 2021). Therefore they make use of available dataset to point out what are some of the point of interest which needs to be articulated as essentially and harness the area they are interested in.
On a lighter note the measures we collected including location, how many distances it stretches from the highway, and some adjacent areas, give us a basis upon which we carry spatial analytics and emphasis on what exact areas our marker is likely to be located. Spatial analysis is very advantageous in the sense that, data of significant value can demonstrate and testify with exactitude (Whitman, 2020). For instance, with the Spatial Sql function, it is very easy to tell the area of a specific place using predefined functions such as ST_Area(). This function takes parameters which takes parameters such as coordinates. These functions compute and return the area by default in meters of the specified coordinate point you have provided.
Geographical representation on the map is very crucial helps us to document our information in the right manner (Wang,2017). With the development of SQL functions that can handle the situation with ease visualization of such data is made salient prerequisites at all times. In the classical sense, data representation has been amplified through the use of the available suitabilities.
Therefore designing a marker for a particular point of interest is very important. Essentially important areas such as food have become one of the competent areas which should stipulate the exactness of place without any much struggle. Therefore, when the measure presented is well presented it fit the requirement and the proper place of interest will have been enhanced.
References
Chakraborty, S. and Doshi, J., 2018. Data Retrieval from Data Warehouse Using Materialized Query Database. International Journal of Computer Sciences and Engineering, 6(1), pp.280-284.
Kirkham, A., Beaudry, R., Paterson, D., Mackey, J. and Haykowsky, M., 2019. Curing breast cancer and killing the heart: A novel model to explain elevated cardiovascular disease and mortality risk among women with early stage breast cancer. Progress in Cardiovascular Diseases, 62(2), pp.116-126.
Rim, N., Choe, K., Scrivner, C. and Berman, M., 2021. Introducing Point-of-Interest as an alternative to Area-of-Interest for fixation duration analysis. PLOS ONE, 16(5), p.e0250170.
Wang, J. and Worboys, M., 2017. Ontologies and representation spaces for sketch map interpretation. International Journal of Geographical Information Science, 31(9), pp.1697-1721.
Whitman, M., 2020. “We called that a behavior”: The making of institutional data. Big Data & Society, 7(1), p.205395172093220.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
