Location Data Query with SpatiaLite and QGIS

Vivian Zhang
Posted on Nov 15, 2013

[Best_Wordpress_Gallery gallery_type="thumbnails" theme_id="1" gallery_id="28" sort_by="order" order_by="asc" show_search_box="0" search_box_width="180" image_column_number="5" images_per_page="30" image_title="none" image_enable_page="1" thumb_width="200" thumb_height="150" thumb_click_action="undefined" thumb_link_target="undefined" popup_fullscreen="0" popup_autoplay="0" popup_width="800" popup_height="500" popup_effect="fade" popup_interval="5" popup_enable_filmstrip="1" popup_filmstrip_height="70" popup_enable_ctrl_btn="1" popup_enable_fullscreen="1" popup_enable_info="1" popup_info_always_show="0" popup_enable_rate="0" popup_enable_comment="1" popup_hit_counter="0" popup_enable_facebook="1" popup_enable_twitter="1" popup_enable_google="1" popup_enable_pinterest="0" popup_enable_tumblr="0" watermark_type="none" watermark_link="http://web-dorado.com"]

Special thanks go to Neville Roy Singham (Founder) and Jared Hatch (Event Planner) from Thoughtworks. Thoughtworks is a global software company focused on software design and delivery.

Thank Michael W, Richard, John, Elise and Sagar, Elliot, Amir, Michael S, Marvin, Xu, Ritesh, Andrew,Chris, Reymond, Christine, Binu, Marcel, Deena, Devin, Anurag and friend, Sonya and Ryan, unknown beautiful couple for coming to our event tonight!


Professor Lee will teach Sunday and weekday classes at NYC Data Science Academy. Please email [email protected] for more details if you want more intensive classes in location data query.








Meetup Announcement:

Professor Lee from Dartmouth college showed us how to query people in New York City live within a quarter mile of a subway station and query their average income using SpatiaLite and QGIS.


Lee Hachadoorian spent 11 years in tech before going to graduate school in geography, with a focus on GIS and urban analysis. He is now a Visiting Assistant Professor at Dartmouth College, where he teaches courses in GIS, geovisualization, urban geography, and urban policy. His research interests include residential location, urban spatial development, urban inequality, and spatial database optimization.


How many people in New York City live within a quarter mile of a subway station? What is their average income? How does that compare to the population that *don't* live near a subway station? These kinds of questions can be answered using a geographic information system, but an alternative to standard GIS is in-database analysis using spatial query. This workshop introduced you to using spatial functions in SQL (using SpatiaLite), and visualization of the results (using QGIS).


For Everyone:

Download the files and folders from my Dropbox (browse or download all files zipped), which include data, a full tutorial [not yet available], and some software.

The new version of QSpatiaLite is now available through the official QGIS Plugins Repository. After installing QGIS using instructions for your OS, go to Plugins→Manage and Install Plugins… to install QSpatiaLite.


Install QGIS using the OSGeo4W installer (http://trac.osgeo.org/osgeo4w/). The express install will install several other desktop GIS applications in addition to QGIS. Use the advanced install to avoid installing the additional packages, or to browse and add more.

Binaries (32- and 64-bit) of SpatiaLite GUI 1.7.1 are included in the Dropbox. This is a standalone binary, you can run it from anywhere in your file system (including a networked or removable drive). Source is available from Gaia-SINS website (http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/).


Install the precompiled QGIS binaries hosted at KyngChaos (http://www.kyngchaos.com/software/qgis). You need to install the GDAL framework first (listed under "Requirements"), before installing QGIS itself.

Binaries (Intel and PowerPC) of SpatiaLite GUI 1.4 are included in the Dropbox. This is an old version that will lack some functionality. If you want a more recent version, you will have to compile from source (available at the Gaia-SINS website http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/). I have old instructions for compiling from source for Ubuntu at http://freecity.commons.gc.cuny.edu/2012/01/31/spatialite-gui/. They may be useful in helping to identify missing dependencies. If any participant is able to compile 1.7.1 before the workshop, I will copy the binary to the Dropbox for other participants to use.


For Ubuntu-based distros, add the ubuntugis-unstable PPA and install QGIS and SpatiaLite 1.6 with the following commands:

sudo apt-get install python-software-properties
sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable
sudo apt-get update
sudo apt-get install qgis python-qgis qgis-plugin-grass spatialite-gui

For other distros, install QGIS following the instructions at http://www.qgis.org/en/site/forusers/alldownloads.html#linux. For SpatiaLite GUI you will have to check your distro's repositories, or compile from the source at http://www.gaia-gis.it/gaia-sins/spatialite-gui-sources/. I have old instructions for compiling from source for Ubuntu at http://freecity.commons.gc.cuny.edu/2012/01/31/spatialite-gui/. They may be useful in helping to identify missing dependencies.


Other Useful Info Link:

All the materials(including sources code, tutorial, data) can be downloaded from github repo.

About Author

Vivian Zhang

Vivian Zhang

Vivian Zhang is the founder of the NYC Data Science Academy and the NYC Open Data meetup. She earned her M.S. in Computer Science and Statistics and B.S. in Computer Science. She is ranked as one of the...
View all posts by Vivian Zhang >

Related Articles

Leave a Comment

No comments found.

View Posts by Categories

Our Recent Popular Posts

View Posts by Tags

#python #trainwithnycdsa 2019 airbnb Alex Baransky alumni Alumni Interview Alumni Reviews Alumni Spotlight alumni story Alumnus API Application artist aws beautiful soup Best Bootcamp Best Data Science 2019 Best Data Science Bootcamp Best Data Science Bootcamp 2020 Best Ranked Big Data Book Launch Book-Signing bootcamp Bootcamp Alumni Bootcamp Prep Bundles California Cancer Research capstone Career Career Day citibike clustering Coding Course Demo Course Report D3.js data Data Analyst data science Data Science Academy Data Science Bootcamp Data science jobs Data Science Reviews Data Scientist Data Scientist Jobs data visualization Deep Learning Demo Day Discount dplyr employer networking feature engineering Finance Financial Data Science Flask gbm Get Hired ggplot2 googleVis Hadoop higgs boson Hiring hiring partner events Hiring Partners Industry Experts Instructor Blog Instructor Interview Job Job Placement Jobs Jon Krohn JP Morgan Chase Kaggle Kickstarter lasso regression Lead Data Scienctist Lead Data Scientist leaflet linear regression Logistic Regression machine learning Maps matplotlib Medical Research Meet the team meetup Networking neural network Neural networks New Courses nlp NYC NYC Data Science nyc data science academy NYC Open Data NYCDSA NYCDSA Alumni Online Online Bootcamp Online Training Open Data painter pandas Part-time Portfolio Development prediction Prework Programming PwC python python machine learning python scrapy python web scraping python webscraping Python Workshop R R language R Programming R Shiny r studio R Visualization R Workshop R-bloggers random forest Ranking recommendation recommendation system regression Remote remote data science bootcamp Scrapy scrapy visualization seaborn Selenium sentiment analysis Shiny Shiny Dashboard Spark Special Special Summer Sports statistics streaming Student Interview Student Showcase SVM Switchup Tableau team TensorFlow Testimonial tf-idf Top Data Science Bootcamp twitter visualization web scraping Weekend Course What to expect word cloud word2vec XGBoost yelp