Step 7: Analyzing Data using Advanced Queries

PostGIS has many spatial operators that allow you to do everything from simple to the most complex analysis using simple SQL entries. 

To show how to use InfraMarker to do analysis we can use the School Points and the Traffic Calming Points to do some simple analysis.

In this example, we'll show how a transit authority project manager can use them to find all the traffic calming points within a few blocks of an elementary school. This way they can review them during the summer and make sure they are working well before children start heading to school in the fall. 

First open up the Project and Go to the Advanced Queries. Once there, click on "Create Query" to create a new query. It will open up the query page. 

Click "Create a New Query"

Default Query Page

First start off with something very simple: type "SELECT * FROM " then click on the "Browse Datasets" button to select the schools_public_pt dataset.

Browse Datasets Dialog

When you select the dataset it will add the dataset's name into the query prompt. You will see a name like "dataset_XXXXX" where XXXXX is a number. When data is uploaded to InfraMarker, all datasets are assigned unique id's and given a similar name. This is to ensure that any data imported by the user can be inserted into the Postgres Database irregardless of where the data came from. 

With the query now saying "SELECT * FROM dataset_XXXXX", click the "Execute" button and see the results appear below the query prompt.

Results from "SELECT * FROM dataset_XXXXX"

Now that you can create a simple query, you can add some spatial operators into it. Next run the query:

SELECT facility_n AS name, ST_BUFFER(wkb_geometry,0.005) AS geom FROM dataset_XXXXX where school_typ = 'Elementary'

This query will tell the database to select all the elementary school's geometries, buffer them a small amount, and then return the buffered geometry and the name of the school.

Now save the query by clicking "Save Query" and give it the name, "Elementary School Zones". After that create a new dataset with the query's results by clicking "Create Permanent Dataset". After that, return to the dataset list and you will see a dataset named "SELECT facility_n AS name, ST_BUFFER(wkb_geometry, 0.005)..." added to your list. 

Save the query

See the new dataset in your dataset list

As with any dataset in your dataset list, if you hover over the name of the dataset, you will see an edit icon appear. Click on that icon to rename the dataset to "Elementary School Zones"

Edit icon appearing when hovering over name of the dataset

Now that we've used standard sql to select all the elementary schools and buffer them, return to the Advanced Queries Page and create a new query. This time execute this query:

SELECT dataset_XXXXX_1.* FROM dataset_XXXXX_1, dataset_XXXXX_2 where ST_INTERSECTS( dataset_XXXXX_1.wkb_geometry, dataset_XXXXX_2.geom)

where dataset_XXXXX_1 is the dataset for the traffic calming features, and dataset_XXXXX_2 is the dataset for the Elementary School Zones. 

This is the list of all the traffic calming features that are within a small area around an elementary school. You can adjust the buffer in in order to increase or decrease the area. 

Save this query as "School Traffic Calming Features" and Create a permanent dataset with it.

Bonus: 

A great feature here is that we have all the data from the traffic calming features, so we can filter them to. For example, what if you need to check all the bulb-outs to make sure they are safe you can use the query. 

SELECT dataset_XXXXX_1.*, dataset_XXXXX_2.name FROM dataset_XXXXX_1, dataset_XXXXX_2 where ST_INTERSECTS( dataset_XXXXX_1.wkb_geometry, dataset_XXXXX_2.geom) and dataset_XXXXX_1.descript = 'Bulb-Out' 

If you want to inspect a specific school, you can add the school's name back into the query by inserting it into the selected fields with this query: 

SELECT dataset_XXXXX_1.*, dataset_XXXXX_2.name FROM dataset_XXXXX_1, dataset_XXXXX_2 where ST_INTERSECTS( dataset_XXXXX_1.wkb_geometry, dataset_XXXXX_2.geom) and dataset_XXXXX_2.name = 'Jefferson Elementary School'