Querying dataset with shapefile?

Question

I will be using a very large dataset (yfcc100m dataset, a dataset containing metadata of Flickr's photos/video) which contains fields longitude and latitude. I'm planning to store the dataset into a MySQL database. Also, I have downloaded shapefiles (.dbf, .prj, .sbn, .sbx, .shp, shx) which contains a country's province borders. I have both shapefiles for the country as a whole and the provinces.

I would like to know if it is possible to query the dataset using the shapefile. What I mean is can I filter out every other data from the dataset which doesn't fall within the borders of the shapefile? If so, can you give me some examples? I'm using Python.

The output is suppose to be a list of all the photos found in the yfcc100m dataset found within the country and grouped by provinces.

The dataset is 14GB while the shapefile is about 9MB. I'm kind of worried since I'm only using my laptop which only has an Intel dual core processor.

I'm new to GIS technology. Please excuse me since I'm not yet familiar into using GIS.

Answer 1

Alternatives in case you don't want to use an expensive commercial product like ArcGIS is using

• Qgis
• or since your dataset is pretty really huge and such operation will definitely will be problematic (if not impossible) you can always switch databases and use PostGIS (an extension of Postgres) load all the data there and query your solution.

Spatial queries are easy.

Answer 2

It does sound like you want to do this on a desktop or let your pc run at night, but it seems like a selection on location. This is a standard function in ERSI Arcmap. (if you use it, As @elasticrash says, Qgis is an alternative). This is possible with a shapefile but my advise is to export it to a geodatabase because geodatabases can handle this better then a simple shapefile.

staps to take:

• (optional) Export the shapefile to a geodatebase;

• Load your data into an .mxd; and open Selection > select by location.

• Select here your input (what layers need to be selected) and the source layer (your borders in this case) and at last your variable: 'are within the source layer feature'

• press ok... and voila....

Answer 3

Sounds like you might be shooting for a spatial join? Where you want to combine attributes of the Flickr's data and bring in the associated country/province, then dumping back out into a dbf...not sure how to with python, but there's other posts like this one discussing this topic. hope this helps!

More Efficient Spatial join in Python without QGIS, ArcGIS, PostGIS, etc