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.