Location Data Query with SpatiaLite and QGIS

Location Data Query with SpatiaLite and QGIS

Posted by Vivian Zhang

Updated: 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 vivian.zhang@supstat.com 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.

Vivian Zhang

Vivian is the CTO and School Director of NYC Data Science Academy and CTO of SupStat. With her extensive experience working in the data science field, she developed expertise in multiple programming languages, including R, Python, Hadoop, and Spark. In August 2016, Forbes ranked her amongst one of the nine women leading the pack in data analytics. In 2013, she created the NYC Open Data Meetup group, which stands as one of the largest data science communities offering meetups, conferences, and a weekly newsletter. In her spare time, Vivian enjoys meeting people and sharing her motivational stories with our students and other professionals

View all articles

Topics from this blog: data science NYC Meetup data

Interested in becoming a Data Scientist?

Answer 3 Simple Questions and Get Immediate Course Recommendations.