Server GIS - An Integration of Municipal Spatial Databases

Loading...

Del curso dictado por Yonsei University

Spatial Data Science and Applications

92 calificaciones

Yonsei University

Spatial Data Science and Applications

92 calificaciones

Spatial (map) is considered as a core infrastructure of modern IT world, which is substantiated by business transactions of major IT companies such as Apple, Google, Microsoft, Amazon, Intel, and Uber, and even motor companies such as Audi, BMW, and Mercedes. Consequently, they are bound to hire more and more spatial data scientists. Based on such business trend, this course is designed to present a firm understanding of spatial data science to the learners, who would have a basic knowledge of data science and data analysis, and eventually to make their expertise differentiated from other nominal data scientists and data analysts. Additionally, this course could make learners realize the value of spatial big data and the power of open source software's to deal with spatial data science problems. This course will start with defining spatial data science and answering why spatial is special from three different perspectives - business, technology, and data in the first week. In the second week, four disciplines related to spatial data science - GIS, DBMS, Data Analytics, and Big Data Systems, and the related open source software's - QGIS, PostgreSQL, PostGIS, R, and Hadoop tools are introduced together. During the third, fourth, and fifth weeks, you will learn the four disciplines one by one from the principle to applications. In the final week, five real world problems and the corresponding solutions are presented with step-by-step procedures in environment of open source software's.

De la lección

Practical Applications of Spatial Data Science

The sixth module is entitled to "Practical Applications of Spatial Data Science", in which five real-world problems are introduced and corresponding solutions are presented with step-by-step procedures in the solution structures and related open source software's, discussed in Module 2. The first lecture presents an example of Desktop GIS, in which only QGIS is used, to find the top 5 counties for timberland investment in the southeastern states of the U.S, in which simple differencing of demand and supply is applied to figure out counties of large deficit of timber supply in comparison with timber demand. In the second lecture, an example of sever GIS, in which QGIS and PostgreSQL/PostGIS are used, will be presented as a solution for a given problem of NYC spatial data center, which required multiple user access and different levels of privileges. The third lecture presents an example of spatial data analytics, in which QGIS and R are used, to find out any regional factors which contribute to higher or lower disease prevalence in administrative districts, for which spatial autocorrelation analysis is conducted and decision tree analysis is applied. The fourth lecture is another example of spatial data analytics, to find optimal infiltration routing with network analysis, in which cost surface is produced and Dijkstra's algorithm is used. The fifth lecture is an example of spatial big data management and analytics, in which QGIS, PostGIS, R, and Hadoop MapReduce are all used, to provide a solution of "Passenger Finder", which can guide to the places where more passengers are waiting for taxi cabs. For the solution, spatial big data, taxi trajectory, are collected, and noise removal and map matching are conducted in Hadoop environment. Then, a series of spatial data processing and analysis such as spatial join in PostGIS, hotspot analysis in R are conducted in order to provide the solution. All in all, learners will realize the value of spatial big data and power of the solution structure with combination of four disciplines.

Server GIS - An Integration of Municipal Spatial Databases9:18

Conoce a los instructores

Joon Heo
Professor
School of Civil and Environmental Engineering

In the previous lecture,

you learned an example of Desktop GIS for finding optimal counties for timber investment.

In this lecture, you will study an example of Server GIS,

which is designed for integrated municipal spatial databases.

New York City combines and manages spatial databases for NYPD,

NYDOT, and other departments.

Each department should have the authority to manage its own spatial data,

at the same time,

they can access to spatial databases of other department.

NYC hired a consulting company,

which can access all the NYC data, though they cannot change them.

In order to support the requirements,

you have to design the solution architecture.

This is a typical database problem, because it requires multiple user access,

different level of authorities,

such as read/write access,

read only, administrator, and so on.

The system must contain administrative information,

which should be securely managed and would require backup and recovery as well.

All in all, a server database management system is

required for supporting the requirements as the figure illustrates.

From the perspective of solution structure of spatial data science problem,

it fits in server GIS.

All the spatial data is managed at the center spatial DBMS

with full support with DBMS features,

such as transaction management,

and each department has client machines,

which is connected to the DBMS

and on which, GIS softwares are installed for daily works such as querying,

basic spatial data analysis,

and visualization for administrative purpose.

A workflow to build such a solution structure is following.

First of all we should set up a spatial DBMS,

such as PostgreSQL and PostGIS.

In the DBMS environment,

the next steps are to create users with

different level of privileges to upload the datasets,

to connect local machines equipped with GIS tools,

and to make each department to manage,

visualize and analyze spatial data for their regular work.

Now, let's take a step-by-step workflow with PostgreSQL and PostGIS.

PostgreSQL was installed in

the first place and then PostGIS was installed on top of it.

The screenshot is PostgreSQL console, pgAdmin,

and it shows that server names 'NYC1' and two database named 'NYC',

and 'postgres' are created.

With respect to NYC database,

four users are created,

where 'NYC' is a superuser,

in other words, administrator,

which has all authorities of NYC server.

NYC DOT, NYC PD, and consulting company are also created as users.

With respect to NYC database,

we can set privilege level to each user.

In PostgreSQL, there are seven different privileges,

which are insert, select, update,

delete, truncate, references, and trigger.

a, r, w, small d,

large d, x and t represent each privilege respectively.

So the administrator NYC has all the privileges, NYC PD,

NYC DOT has four privileges of insert,

select, update, and delete.

Consulting company, COM, has only privilege to select.

By the way, the asterisk means, grant option,

which can allow the user to give his privilege to other users.

Server, database, users, and privileges are created.

Now, it is ready to upload the dataset.

Assume that each department has a list of their own data as described in the slide.

In fact, you can download

the whole set of spatial data of New York City from the given link.

For uploading spatial dataset to PostgreSQL,

we can use PostGIS shapefile Import/Export manager,

each user can upload their spatial data to the database.

QGIS has a function to list the datasets in PostGIS as a layer,

and the data can be accessed by using PostgreSQL DB connection.

Alternatively, you can utilize DB manager plugin from

QGIS to connect PostgreSQL and to manage the database from QGIS.

As mentioned in the problem statement and solutions structure,

each department and consulting company need to use

GIS tool for visualizing and analyzing spatial data.

So, QGIS is installed and you can use,

"Add PostGIS Tables" function to connect

QGIS to database NYC and to manage it from QGIS as well.

The screenshot shows a visualization of bike route,

bike parking shelter, and boundary of borough,

which actually stored in PostgreSQL.

You can see the geometry of the datasets,

and they can be managed and analyzed in QGIS.

Let us assume a scenario of using the given solution framework.

NYPD wants to have a solution for optimal distribution of patrol unit resources.

So, NYPD hired a consulting company,

and they will find areas with higher complaint rates for a better resource allocation.

The consulting company has their user ID, 'COM',

which only has a privilege, select.

Using their ID, they can get access to NYC spatial data through QGIS,

and the screenshot shows that they retrieved

NYPD complaints data from NYC database to QGIS.

The figure shows NYPD complaints cases in

the South Manhattan of which attribute data covers time of occurrence,

location, crime type, violation,

misdemeanor, and felony, and so on.

Some analysis can be conducted in GIS environment.

The figure shows the total complaints in each NYPD district,

which can be done with "Count Points in Polygon" function in QGIS.

Then the complaint counts are categorized into five places.

Now, you're looking at spatial distribution of complaints with respect to NYPD districts.

Perhaps you can take a further step to analyze complaints

per 1,000 residents, or complaints per square kilometers and so on.

The same analysis was conducted for only violation,

and the results are visualized.

Again, the same analysis for misdemeanor is conducted,

and the result is visualized again here.

This time, the same analysis and visualizations are

conducted for felony cases with respect to NYPD districts.

In this lecture, an example of server GIS was

presented as a solution for given problem of NYC spatial data center,

which required multiple user access and different levels of privileges.

We use PostgreSQL and PostGIS for spatial DBMS and QGIS for GIS solution,

and you learned how to connect spatial DBMS in

GIS and had a short taste of power of the server GIS.

This will be the first application to make the best use of spatial DBMS and GIS.

All right.

This is the end of this lecture.

See you all in the next lecture.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play