layout:true <p class="footer"> <span xmlns:dct="http://purl.org/dc/terms/" property="dct:title">Intermediate GIS with QGIS and PostGIS</span> by <a xmlns:cc="http://creativecommons.org/ns#" href="http://www.datapolitan.com" property="cc:attributionName" rel="cc:attributionURL">Richard Dunks, Eric Brelsford, and Beth Pappas</a> is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">Creative Commons Attribution-ShareAlike 4.0 International License</a>.<a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by-sa/4.0/80x15.png" /></a> </p> --- class: center,middle  - - - # Intermediate GIS with QGIS and PostGIS ## Richard Dunks, Co-Instructor<br>Eric Brelsford, Co-Instructor ## Clayton Hunter, TA ### Follow along: http://bit.ly/intermediate-gis #### See the code at: http://bit.ly/intermediate-gis-code --- # Introductions + Name + Department / Job + One thing you hope to get out of class today + Describe a map you've seen/created/used recently and why it was interesting to you --- # Goals for the class -- + Provide a review of key spatial concepts and GIS functions in QGIS -- + Demonstrate connecting to a spatial database in QGIS -- + Introduce the structured query language (SQL) for querying relational databases -- + Practice writing queries in SQL to accomplish key spatial analytical tasks with real-world data to answer meaningful analytical questions -- + Practice best practices in communicating spatial analysis --- # Outcomes -- + You will feel more comfortable with GIS concepts -- + You will be more familiar with using QGIS -- + You will understand the purpose of spatial databases -- + You will have an understanding of the fundamentals of SQL -- + You will be familiar with advanced styling techniques in QGIS --- # Assumptions -- + You know something about working with spatial data, maps, and a GIS of some kind -- + Each of you has something meaningful to contribute to this class -- + You all have different levels of experience with spatial data, GIS, and technology in general -- + You are here to learn more about how to work with and analyze spatial data -- + You don't want to sit there and watch us have all the fun --- # Housekeeping -- + We're not explicitly going over some basic operations in the interest of time (pan, zoom, inspect, select, etc.) but cover those in our [intro class](http://www.datapolitan.com/DOT_GIS/20160428_Introduction_to_GIS_Fundamentals/# 1) and will be happy to demo if you have questions -- + The workbooks are for your notes and to take with you -- + The slides are available online and will be for the foreseeable future -- + Not all slides are in the workbook -- + The workbook is a guide to the important points of the class, especially the exercises --- # Goals for this morning -- + Review basic geospatial principles -- + Practice performing basic spatial operations in QGIS -- + Discuss spatial databases -- + Demonstrate and practice loading data from a spatial database in QGIS -- + Practice answering an operational question with spatial data -- + Demonstrate and practice more advanced styling techniques in QGIS --- # Let's Review --- class:center,middle # Basic Spatial Elements --- # Points  #### Source: http://docs.qgis.org/2.8/en/docs/gentle_gis_introduction/vector_data.html#overview --- # Lines  #### Source: http://docs.qgis.org/2.8/en/docs/gentle_gis_introduction/vector_data.html#overview --- # Polygons  #### Source: http://docs.qgis.org/2.8/en/docs/gentle_gis_introduction/vector_data.html#overview --- class:center,middle # How do we make maps? --- # Geographic Information System (GIS) > Any system for capturing, storing, checking, and displaying data related to positions on the Earth's surface + [National Geographic Education Encyclopedia](http://education.nationalgeographic.org/encyclopedia/geographic-information-system-gis/) --- # Or more simply > In a GIS, you connect _**data**_ with _**geography**_. + [GISgeography.com](http://gisgeography.com/what-gis-geographic-information-systems/) --- # Geographic Information Systems (GIS) -- + Create interactive queries (user-created searches) -- + Analyze spatial information -- + Edit data in maps -- + Present the results of all these operations --- class:center,middle # Let's Get Started  --- # A quick note on exercises -- + We're going to be working with real-world data -- + Real-world data has issues -- + We're going to be showing you how to deal with these issues -- + The learning in this class happens when you do the steps along with us -- + Try and help each other out as we go -- + We'll make sure not to leave anyone behind -- + If these data problems aren't your data problems, hopefully the techniques will be useful in the work you do --- class:center,middle # Let's make a map  --- # Base Maps -- + Adding base maps will give your map context -- + In QGIS, you need a plugin to use base maps -- + There are several, but we're only going to use one of them --- # Base Map Plugin + From the Plugins menu, select "Manage and Install Plugins"  --- # Base Map Plugin + Search for the QuickMapServices plugin and install it  --- # Setting Up QuickMapServices Plugin + Go to the Settings Menu  --- # Setting Up QuickMapServices Plugin + Get the contributed packs  --- # Setting Up QuickMapServices Plugin  + Revel in all the beautiful base maps --- # Select a base map from the list  --- # Adding Layers  #### Image Source: http://www.geocontrolling.com/co-je-gis.htm --- # Shapefiles -- + Basic file for storing map elements -- + Stores spatial data, like points, lines, and polygons -- + Multiple files comprise a "shapefile" --  --- # Download a shapefile and import 1. [Click this link](data/boros/boros.zip) and download the file to your desktop 2. Unzip the file  --- # Import a shapefile   --- # Import a shapefile  --- class:center,middle # Adding a CSV file --- # What is a CSV? --  --- # Import a CSV + [Right click this link](data/dot_311_20160201_20160207.csv) + Select "Save link as" + Save the file (`dot_311_20160201_20160207.csv`) to your desktop --- # Adding CSV Data   --- # Adding CSV Data  --- # Adding CSV Data  --- class:center,middle # What is a Coordinate Reference System? --- # Projections -- + No one's favorite part of GIS -- + But a necessary part of it nonetheless -- + Convert points on the 3-dimensional Earth (**latitude** and **longitude**) to x and y coordinates on a 2-dimensional map --  [Digital Coast Geozone](https://geozoneblog.wordpress.com/2012/05/22/happy-birthday-mercator/) --- # Projections + Every projection distorts some part of your map  [FlowingData](http://flowingdata.com/2014/01/13/map-projections-illustrated-with-a-face/) --- # Projections + For the most part we will work in **WGS 84** (latitude and longitude) -- + In NYC, we use a more accurate projection **NY State Plane/Long Island Zone**  ###### Source: https://alidade.wikispaces.com/New+York+SPCS+Zones --- # Projections + Identified by unique IDs that make it easier to talk about them -- + WGS 84 is referred to as **EPSG:4326** -- + State Plane Long Island is referred to as **EPSG:2263** -- ## Remember these two and you should be set --- # Adding CSV Data  --- # Adding CSV Data  --- class:center,middle # Styling the map --- # Attribute Table (Right click on layer)   --- <!-- Styling --> # Styling Features + Right-click the layer and select the Properties option + Select "Style," and finally, choose "Categorized":  --- # Styling Features + Select the column that has the data you want to style:   --- # Styling Features  --- # Styling Features  --- # Your turn + Style the points and polygons however you'd like + Try adding labels to the data + Get familiar with the interface --- class:middle,center # 5 Min Break  ### Source: https://www.instagram.com/p/-0QkhTj82O/ --- class:center,middle # Spatial Databases --- class:center,middle # Who's used one before? --- # What is a database? -- > An organized collection of data. ## [Wikipedia](https://en.wikipedia.org/wiki/Database) --- # What is a database? > A set of data that has a regular structure and that is organized in such a way that a computer can easily find the desired information. ## [The Linux Information Project](http://www.linfo.org/database.html) --- # What is a database? --  ##### Source: http://blog.hackerrank.com/new-domain-tuesday-hackerrank-reveals-data-structure-database-sql-challenges/ --- # What is a database? + Another source of data -- + A program that often runs on a computer other than the one you're using (a server) -- + You can connect to databases through many types of software, including QGIS --- # What is a spatial database? -- + A database that stores spatial features -- + Optimized for processing spatial data, especially location-aware queries -- + Both a storage and analysis tool --  #### Source: http://presentations.opengeo.org/2011_IMAK/Workshop_OpenSource_Stack/postgis/spatialdbs.html --- # Let's connect to a spatial database  --  --- # Let's connect to a spatial database  + Name: (Whatever you'd like) + Host: `training.c1erymiua9dx.us-east-1.rds.amazonaws.com` + Port: `5432` + Database: `training` + Username: `dot_student` + Password: `qgis` --  --- # Let's connect to a spatial database   ####   ####   ####   ####   ####   ####   ####   ### Now it's just like any other data source we've been using --- # Exercise 1 - Selecting Injuries in a Borough 1. Add the `injuries` table to your map 2. Right click and select "Filter..."  3. Select `borough` and query for your borough #####    --- # Selecting Injuries in a Borough  --- # Save Selection As Shapefile   --- # Let's talk datatypes --  -- + In the attribute table you will usually see *text*, *numbers*, and *dates* --- # Let's talk datatypes  -- ## Numbers will be right aligned --- # Let's talk datatypes  -- ## Text will be left aligned --- # Database datatypes -- + Like attribute tables, database columns have types -- + Numbers, text (*strings*), and dates are the main types -- + Spatial databases add *geometry* columns -- + Geometry columns let us map our data --- # Why use a spatial database? -- + One authoritative copy of data -- + Multiple people can use and edit it at once -- + Generally is going to be faster than using a shapefile --- # Drawbacks of using a spatial database -- + Not as simple as a CSV file or shapefile -- + It takes some setup to connect to a database -- + Need permission to connect to a database -- + Need to know SQL to get the most out of it --- class:center,middle # 10 Min Break  ### Source: https://www.instagram.com/p/BAVc4Gzj88v/ --- ## Exercise 2 - Let's do some data analysis -- + Which Community Boards in NYC have the highest number of bicyclist injuries? --  --- # First of all + What kind of map is this?  --- # Choropleth  ##### Source: http://www.geogalot.com/myp-humanities/year-9/understanding-hazards/003---choropleth-mapping --- # Choropleth  ##### Source: http://www.floatingsheep.org/2012/07/church-or-beer-americans-on-twitter.html --- # Thematic Maps -- + Focuses on a specific theme or subject area -- + Features on the map represent the phenomenon being mapped -- + Spatial features used for reference --- # General Reference Maps -- + Show important physical features of an area -- + Include natural and man-made features -- + Usually meant to help aid in the navigation or discovery of locations -- + Usually fairly simple -- + Can be stylized based on the intended audience (tourists vs locals) --- class:center,middle  ##### Source: http://nationalmap.gov/small_scale/printable/reference.html --- class:center,middle # Let's make a choropleth --- # Load data + Add the `nyc_cd_4326` table + Add the `injuries` table  --- # Spatial Join  -- ## Point to Polygon + Relate points inside a polygon to that polygon (ex. count the number of points) -- ## Polygon to Point + Points can take on value of enclosing polygon --- # Spatial Join + We'll be joining injuries (points) to community districts (polygons)  --- # Spatial Join   --- # Style the Result  --- # Style the Result  --- # Print Composer -- + How you make exportable and printable maps in QGIS -- + Able to add map elements (legends, scales, text, etc) --  --- # Create a new print composer  --  --- # You are greeted with a blank slate  --- # Add New Map tool will add your current map  --  --- # Customize item properties on the right  --- # Don't forget a title and your sources   --- # There are a few exporting options  --- # Review -- + What did we just do? -- + Why did we do it? --- class:center,middle # Now let's ask something a little more complicated --- # Exercise 3 - Bicyclist Injuries + Which community board has the highest number of bicyclist injuries off of bicycle paths?  -- # How do we answer this? <!--  --> --- # Steps to answer -- 1. Buffer bicyclist injuries (points) -- 2. Select injuries near bike lanes (intersection of buffers with bike lane) -- 3. Invert the selection to find injuries that happened off of bike lanes -- 4. Count the number of injuries off of bike lanes by community boards -- 5. Style the result --- # What we're going to create  --- # Step 0: Load the tables we need  + `injuries` + `bike_routes_2015` + `nyc_cd_2263` --- # Step 1: Buffer bicyclist injuries  --- # Step 1: Buffer bicyclist injuries + Create a 15ft around injury locations  --- # Buffering features -- + Gives "size" to 2-D features -- + Points don't have area -- + Lines have length but don't have width -- + Allows for analysis of overlap and intersection --- # Buffering features in QGIS   --- # Oops  --- # Map Units -- + Each projection has a base unit of distance -- + For WGS 84 it's decimal degrees<br> -- _Know how big 15 decimal degrees are?_ -- + Best to convert to a projection with simple map units -- + For NYC, EPSG:2263 is best (the map units are in feet) --- # Reprojecting Layers -- + We want to project the `injuries` layer to EPSG:2263 --  + Right click the layer and select `Save As...` --- # Reprojecting Layers + We want to project the `injuries` layer to EPSG:2263 + Right click the layer and select `Save As...`  + Select the new projection -- + Give the file a descriptive name -- + Click `OK` -- ### Notice we're saving data from the database locally on our computer when we reproject --- # Buffering features in QGIS  + Make sure you select your reprojected layer + Set the distance to 15 (this time it'll be feet) + Set your output shapefile + Select `OK` --- # Buffering features in QGIS  --- # Buffering features in QGIS  --- # Step 2: Select injuries near bike lanes  --- # Select by location in QGIS + There's a plugin for that   --- # Select by location in QGIS  + Select the buffered injuries layer + Select `Intersects` + Select the bike routes layer + Select `Create new selection` + Click `Apply` --- # Select by location in QGIS  + Click `Close` --- # What did we just do? -- 1. Created a 15ft buffer around bicyclist injuries -- 2. Selected injuries that intersected a bike lane<br> -- (Meaning it happened on or near a bike lane) -- # Still to do -- + Find the injuries that happened off of bike lanes -- + Count injuries off bike lane by Community Board -- + Style the result --- # Step 3: Find injuries off of bike lanes  --- # Step 3: Find injuries off of bike lanes  --- # Step 3: Find injuries off of bike lanes  --- # Step 3: Find injuries off of bike lanes  + Create a layer of the selected features --- # Step 4: Join result to Community Boards  + Join the Community Boards to the bicyclist injuries off of bicycle lanes --- # Step 5: Style the result  --- # By the way... + What percentage of bicyclist injuries happen on bike lanes? -- + What does that number suggest to you? -- ## Maybe stay in bike lanes?  --- # Your Turn + Filter the result for your borough + Style a map of just the community boards in your borough + Develop a hypothesis as to why the injuries are happening in those areas --- # What we've covered -- + Reviewed basic mapping concepts -- + Reviewed projections -- + Added basemaps and layers (shapefile and CSV) -- + Styling features -- + Introduced spatial databases -- + Connected to a spatial database, filtered, and saved data as a shapefile -- + Discussed map types -- + Processed spatial data (joined, buffered, selected, reprojected, and saved) --- class:center,middle # Lunch  ### Source: https://www.instagram.com/p/BB7cKt_D8zv/ --- class:center,middle # Welcome Back <iframe width="700" height="450" src="https://www.youtube.com/embed/KUF_Ckv8HbE" frameborder="0" allowfullscreen></iframe> --- class: center,middle # What's one thing you learned this morning that you found useful, interesting, and/or helpful? --- class:center,middle # What do you think of QGIS so far? --- # Some Other GIS Tools  --- #[ArcGIS](http://www.esri.com/software/arcgis/arcgis-for-desktop)  Source: http://www.esri.com/news/arcuser/1012/a-workflow-for-creating-and-sharing-maps.html --- #[Adobe Illustrator](http://www.adobe.com/products/illustrator.html)/[Mapublisher](http://www.avenza.com/mapublisher)  Source: http://www.avenza.com/resources/blog/2011/06/28/how-get-open-street-map-data-adobe-illustrator-mapublisher --- #[Google Earth](https://www.google.com/earth/)  --- #[Google Fusion Tables](https://sites.google.com/site/fusiontablestalks/stories) <iframe width="100%" height="430" scrolling="no" frameborder="no" src="https://www.google.com/fusiontables/embedviz?q=select+col54%2C+col55+from+1dNtdy7FuBBZ2qTxX7jgZ5Dig8n96oxRrWFjuhYpJ+where+col3+%3E%3D+0+and+col3+%3C%3D+4+and+col26+%3D+2+limit+1000&viz=HEATMAP&h=true&lat=40.844685&lng=-73.915349&t=1&z=12&l=col54&y=2&tmplt=2&hmd=true&hmg=%2366ff0000%2C%2393ff00ff%2C%23c1ff00ff%2C%23eeff00ff%2C%23f4e300ff%2C%23f4e300ff%2C%23f9c600ff%2C%23ffaa00ff%2C%23ff7100ff%2C%23ff3900ff%2C%23ff0000ff&hmo=0.6&hmr=26&hmw=0&hml=TWO_COL_LAT_LNG"></iframe> Source: [NYC Open Data 311 Noise Complaints, 1 Jan - 20 May 2014, Between Midnight and 4 am in the Bronx](https://www.google.com/fusiontables/DataSource?docid=1dNtdy7FuBBZ2qTxX7jgZ5Dig8n96oxRrWFjuhYpJ# map:id=3) --- class:center,middle # Proprietary vs Open-Source --- # What is open-source?  Source: https://www.apertus.org/opensource <a rel="license" href="http://creativecommons.org/licenses/by/3.0/"><img alt="Creative-Commons-License" style="border-width:0" src="https://i.creativecommons.org/l/by/3.0/88x31.png" /></a><br />This work is licensed under a <a rel="license" href="http://creativecommons.org/licenses/by/3.0/">Creative Commons Attribution 3.0 Unported License</a>. --- # What is open-source? -- + Free to download -- + Free to use -- + Able to change and customize -- + Usually no enterprise support -- + Supported by a community -- [](http://michaelminn.com/) --- # Proprietary software -- + Pay to download -- + Pay to use (usually with a license) -- + Not able to change or customize (legally) -- + Usually supported by a team of paid developers --- class:center,middle # How does this affect you? --- class:center,middle # Another example of open-source mapping software --- #[CartoDB](https://cartodb.com/) <iframe width="100%" height="500" frameborder="0" src="https://richard-datapolitan.cartodb.com/viz/84289396-125c-11e6-89ae-0e674067d321/embed_map" allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen></iframe> --- class:center,middle # Some things about QGIS... --- # QGIS - The Good -- + Open-source software that’s freely available -- + Works with lots of different data types, including shapefiles, KML (Google Earth), GeoJSON, and text files -- + Works with various spatial databases -- + Strong community developing features and plug-ins --- # QGIS - The Bad -- + Not an enterprise software like ESRI ArcGIS -- + The visual style is a little clunky -- + Features tend to change between versions (moving around, menu option changes, etc) -- + Major versions tend to have major feature changes -- + Plug-ins tend not to work with newer versions -- + And it crashes (as you've probably already experienced) --- # And of course...  --- # QGIS - The Bug-y -- + Projects with different CRS layers tend to get misaligned when zooming -- + _Zoom in and then back out to fix_ -- + Plug-ins sometimes don't work like they're supposed to -- + _Save your project, close QGIS, re-open, and try again_ -- + Other odd behavior that they’re hopefully working on -- + _File them here: https://hub.qgis.org/wiki/17/Bugreports_ --- # Back to databases  ---  --- # You might ask:  + Which pages refer to properties in Brooklyn? -- + Which pages refer to properties in Brooklyn that have been built on since 1950? -- + What are the addresses of those properties? --- # Databases are great at questions like these -- + It's what they were made for -- + SQL will let us ask these questions --- # SQL -- + Structured Query Language -- + The language databases understand --- # SQL queries -- + Can *view* data -- + and *change* data -- ## We're going to stick to *viewing* data --- # SQL looks like: ```sql SELECT * FROM injuries ``` -- + In english: "select all the injuries" -- ```sql SELECT * FROM injuries WHERE BOROUGH = 'QUEENS' ``` -- + In english: "select all the injuries in Queens" --- # You can combine conditions ```sql SELECT * FROM injuries WHERE borough = 'QUEENS' AND contributi = 'Fatigued/Drowsy' ``` -- ```sql SELECT * FROM injuries WHERE contributi = 'Outside Car Distraction' OR contributi = 'Fatigued/Drowsy' ``` --- # This should feel pretty familiar --  --- class:center,middle # Let's use SQL in QGIS --- # DB Manager -- + One way to see your database *connections* and *tables* -- + Also a way to run SQL queries on your tables --- # DB Manager --  --- # DB Manager  --- # DB Manager  --- # DB Manager  --- # DB Manager  --- # DB Manager  --- # Simple SQL Query  --- # Simple SQL Query  --- # Simple SQL Query  --- # Add Query Result to Map  --- # Add Query Result to Map  --- # Add Query Result to Map  --- # Add Query Result to Map  --- # Your turn + Use DB Manager and SQL to select the injuries for the Bronx + Add the results as a layer in QGIS --- class:center,middle # 5 Min Break  ### Source: https://www.instagram.com/p/_okHsID8xS/ --- # Spatial joins in SQL -- + You can do the same spatial joins you have done in QGIS using SQL -- + They give you more control than QGIS does -- + And you don't have to create new shapefiles --- # Spatial joins in SQL -- + Let's start with a points in polygon join: -- ```sql SELECT nyc_boro.*, COUNT(injuries) FROM nyc_boro JOIN injuries ON ST_Within(injuries.geom, nyc_boro.geom) GROUP BY nyc_boro.gid ``` --- # Line by line: ```sql SELECT nyc_boro.*, COUNT(injuries) ``` ##"Select all of the `nyc_boro` columns and the count of `injuries`" --- # Line by line: ```sql FROM nyc_boro ``` ##"Use the `nyc_boro` table" --- # Line by line: ```sql JOIN injuries ON ST_Within(injuries.geom, nyc_boro.geom) ``` ##"Join with `injuries`, associate `injuries` with the `nyc_boro` they are within" --- # Line by line: ```sql GROUP BY nyc_boro.gid ``` ##"Count by borough" --- ## All together: ```sql SELECT nyc_boro.*, COUNT(injuries) FROM nyc_boro JOIN injuries ON ST_Within(injuries.geom, nyc_boro.geom) GROUP BY nyc_boro.gid ``` -- ## More generally: ```sql SELECT polygons.*, COUNT(points) FROM polygons JOIN points ON ST_Within(points.geom, polygons.geom) GROUP BY polygons.unique_id ``` -- ### Just replace *points* and *polygons* with the table names, *unique_id* with a unique id from your polygons table --- # Joining Tables in SQL  --- # Joining Tables in SQL  --- # Joining Tables in SQL  --- # `ST_Within` -- + A *spatial* SQL function -- + Similar to other SQL conditions (`borough = 'QUEENS'`) -- + ...but we're checking whether the geometries of one table are *within* geometries from another --- # `ST_Within`  ##### Source: https://www.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.spatial.doc/relation1033234.htm%23relation1033234 --- # Spatial SQL functions -- + There are many more you can use -- + For example, `ST_Crosses`:  --- # Spatial SQL functions -- + We're only covering `ST_Within` today -- + But we will give you resources for finding other spatial SQL functions --- # Your turn + Use DB Manager and SQL to do a points in polygon join to count injuries in community districts (table `nyc_cd_4326`) + Add the results as a layer in QGIS + Style the community districts by the number of injuries --- # Putting it all together + Filter DOT 311 requests by your borough and for a type + Join filtered requests to community districts in your borough + Create a choropleth of the number complaints in each CD + Hint: Be thinking about the problems we've run into with joining layers + Create a map and [submit your work](https://script.google.com/a/macros/datapolitan.com/s/AKfycbwMcE5pcJwZHdbSCN_2epwaXnRLSRPaLbHCAhxaZJ79UXaRpQ_l/exec) --- class:center,middle # 10 Min Break  ### Source: https://www.instagram.com/p/9stzddj8-Q/ --- # Heatmaps -- + One way of mapping density when you have many points -- + An alternative to choropleth maps if you want to show a little more nuance --- # Heatmaps  #### Source: https://www.mapbox.com/blog/nyc-taxi/ --- # Heatmaps  #### Source: http://a841-tfpweb.nyc.gov/bikeshare/2011/09/20/station-suggestion-recap/ --- # Heatmaps in QGIS -- + Use the *Heatmap* plugin -- + Create a raster (image) showing the density of the points -- + Style the raster --- # Heatmaps in QGIS  --- # Heatmaps in QGIS  --- # Heatmaps in QGIS  --- # Heatmaps in QGIS  --- # Heatmaps in QGIS  --- # Your turn + Filter DOT 311 requests (`dot_311`) by borough and by type in SQL + Create a heatmap of complaints in your borough --- class:center,middle #[Click to submit your work](https://script.google.com/a/macros/datapolitan.com/s/AKfycbwMcE5pcJwZHdbSCN_2epwaXnRLSRPaLbHCAhxaZJ79UXaRpQ_l/exec) --- class:center,middle # Presentations --- class:center,middle # A few last things --- # Data formats we've covered + CSV  --- # Data formats we've covered + Shapefiles  --- # Data formats we haven't covered + JSON (used in online services)  --- # Data formats we haven't covered  + GeoJSON (used in online services with locations to be mapped) + For more information see Eric Brelsford's [GeoJSON and Github tutorial](http://youtu.be/TQs7fYo9d_M) --- # Data formats we haven't covered + XML (Google Earth's native data format)  --- # Geocoding --  --- # Geocoding  --- # Geocoding  --- # Geocoding in QGIS -- + You'll want a CSV with the *street address*, *city*, and *state* in separate columns -- + And enable the MMQGIS plugin --- # Geocoding in QGIS  --- # Geocoding in QGIS  --- # Geocoding in QGIS  --- # Let's try it together 1. [Click this link](data/Lincoln_Square_BID_Business_List.csv) and download the file to your desktop 2. Open the MMQGIS geocoder and follow along --- # What We've Covered This Afternoon -- + Got started with using SQL to querying databases -- + Practiced using SQL for querying spatial data -- + Made heatmaps with the Heatmap plugin -- + Talked about other data formats -- + Reviewed Geocoding --- # Feedback + What of what we covered was helpful/interesting to you? + Were the problems we worked with similar to the ones you face? + What other skills/techniques/data do you have questions about? --- class:center,middle # Resources --- # GIS Stack Exchange  [GIS Stack Exchange](http://gis.stackexchange.com/) --- # Learning QGIS  [Learning QGIS](https://www.packtpub.com/application-development/learning-qgis-20) --- # QGIS Map Design  [QGIS Map Design](http://locatepress.com/qmd) --- # PostGIS in Action  [PostGIS in Action](http://www.postgis.us/) --- # The PostGIS Documentation  http://postgis.net/documentation --- # The PostGIS Documentation [](http://postgis.net/docs/ST_Within.html) ### They're actually really helpful --- # Other Documentation + [QGIS online manual](http://docs.qgis.org/2.8/en/docs/) + [QGIS Tutorials and Tips](http://www.qgistutorials.com/en/index.html) + [Free and Open Source GIS Ramblings](https://anitagraser.com/) --- # Reminders -- + Know your layer projection -- + Always test your queries when you filter -- + Use descriptive filenames -- + Keep your data organized -- + Save your work often (especially your project file) -- + When in doubt, save, close, and restart --- class:middle  --- class:center,middle # Thank You ##[Please take a moment to share your thoughts on this class](http://goo.gl/forms/X7XDbcpP3M) --- class:center,middle [](https://www.instagram.com/sadtopographies/) https://www.instagram.com/sadtopographies/ --- ## Richard Dunks [](https://scontent-lga3-1.cdninstagram.com/l/t51.2885-15/e35/11260589_1537702966519640_1758757795_n.jpg) + [richard@datapolitan.com](mailto:richard@datapolitan.com) + [@rdunks1](https://twitter.com/rdunks1)/[@datapolitan](https://twitter.com/Datapolitan) ## Eric Brelsford + [ebrelsford@gmail.com](mailto:ebrelsford@gmail.com) + [@ebrelsford](https://twitter.com/ebrelsford) ## Clayton Hunter