4

I have around 110000 points data spread around the world. the shapefile size is 116 Mb. I want to select points which are inside asia.

I have been using qgis but this time qgis is not being able to process it (It gives a not responding message, and even after waiting for 10 min, it does not respond). I have tried it with qgis version 1.8 and 2.2.

Is there a method to perform this kind of large operation successfully?

11
  • 2
    What kind of data storage format are you using?
    – AndreJ
    May 12, 2014 at 12:55
  • I second @AndreJoost. It's worth knowing whether you're working off of something like a huge KML..! ;)
    – elrobis
    May 12, 2014 at 13:52
  • If your data have any tabular attributes, those could be useful ..for instance SELECT * WHERE continent = 'ASIA', or even a compound WHERE clause targeting specific countries.
    – elrobis
    May 12, 2014 at 13:58
  • It is in shapefile format.
    – neogeomat
    May 13, 2014 at 3:33
  • It does not have such attributes which can be used for query.
    – neogeomat
    May 13, 2014 at 3:34

2 Answers 2

3

I second using PostGIS, it will make this go much faster and can still be used in QGIS (this is what QGIS was initially written to do according to the author). Then create a spatial index on your points data and add a polygon table for political boundaries of countries. Next, use the blazingly fast BBOX indexing search query that is part of the ST_Intersects function.

create index points_idx on points_shapefile using gist(geom);
create index country_idx on country_shapefile using gist(geom);
select id, geom from points_shapefile 
where 
st_intersects(points_shapefile.geom,country_shapefile.geom);

while you're at it, you might as well also add a column called country to your table and set it to China so you can speed up the process in the future. You could then just do the query like this

alter table points_shapefile add column country varchar;
update points_shapefile 
set country = "China"
where St_Intersects(points_shapefile.geom,country_shapefile.geom)

and from then on you could just query the table as

select column1,column2,... from points_shapefile where country = 'China';

If you are certain the country shapefile BBOX will not intersect any other countries based on where you are looking at within CHINA, you could speed up the process even further by just doing an index search without the more exact search of whether they actually intersect

select id, geom from points_shapefile where points_shapefile.geom && country_shapefile.geom;

the && is the BBOX intersects operator

This bounding box operation is actually performed by the query planner automatically in ST_Instersects, as you will see if you do an explain analyze before running your query.

see http://postgis.net/docs/manual-1.4/ch04.html for description of how to use this functionality. After a little bit of a learning curve, you will probably find working with PostGIS and using QGIS as a viewer of PostGIS tables much faster and more powerful than using the QGIS equivalent (which I believe is based on PostGIS under the hood anyway)

2

I suggest storing the points and outline geometries in a local postGIS database. You'll have no trouble performing a spatial selection this size with it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.