I'm using GQIS 2.2 and PostGIS on Postgresql 9.3.

I have 5 regions in the contiguous United States. Each region has states, and each state has counties. I would like to think that the data I'm given has each region, state, and county line up cleanly, but one can never be sure.

Here is an example:

Regions have States, and States have Counties

I'm at the point in the project where I need to select all counties within a region. Success is getting all the counties within a given region (say, the Upper Midwest). None of my attempts at QGIS or PostGIS give me the results I want. It may either be because my queries are poor (I'm a beginner), or because my data is dirty, or both.

An image with the midwest highlighted. I want to get all its counties

Here's what I've done so far: I've created PostGIS queries and also tried using QGIS's interface.

ST_Within:

ST_Within results Using QGIS's interface, we can see that ST_Within misses the border counties.

ST_Touches:

ST_Touches results Touches seems to fail pretty badly - none of the counties are actually the counties that I want. They seem to be outside the selection! This is what leads me to think that I may have to clean my data somehow.

ST_Intersects:

ST_Intersect Intersects is another step in the right direction, but it overselects.

I also tried to get fancy using centroids, but I hit a few interesting edge cases involving centroids falling outside of county polygons and islands.

Using Centroids

I could always just use manual selection, but this seems like an interesting problem. If the problem is just of selection, then fine. But I think the real problem is that my data is off.

I'm seeking any data cleaning techniques. I assume that I'm missing a general technique. I've read How to select by polygon in PostGIS with misaligned layers?, and while the solution was indeed very cunning, it doesn't strike me as solving the underlying problem.

  • Try ST_CoveredBy postgis.net/docs/ST_CoveredBy.html – user30184 Jun 1 '14 at 16:59
  • This yielded similar results to ST_Within. i.imgur.com/B6LqQKe.png – standers Jun 1 '14 at 17:32
  • For getting correct results the layers must match exactly at every vertex at the borders. If datasets comes from different sources it would be not a surprise if the match is not perfect. In that case you can start from your counties and build the states by making unions of counties which have a common attribute value for the state it belongs to. – user30184 Jun 1 '14 at 17:47
  • Yeah, they came from the same website but that doesn't mean that the data was built consistently or cleanly. I really like your idea of just building the dang things myself from the counties by unioning the attribute names. Before I mark as answer, is there a way to clean this data by snapping the vertices or some other wizard magic? Such a technique would be very useful down the road. – standers Jun 1 '14 at 18:06
  • Does this have to be done by location? Seems like it would be easier to do it by attribute, assuming the data has those required. And you could certainly clean your data to line up, but that would be a lot of work and checking - would it be more feasible to just get an aligned dataset to start with from another source? Counties/states should be pretty easy, and if you couldn't find regions that align it would be easier to create them from states rather than make all those counties align to the ones you do have. And I think you want Dissolve, not Union, for the @user30184 approach. – Chris W Jun 1 '14 at 19:54
up vote 7 down vote accepted

PostGIS has a function that addresses just this centroid problem: use ST_PointOnSurface() to return a point guaranteed to be inside your county, then just find the ST_Intersection() of the county-points and region. Like this:

SELECT * FROM counties c, regions r WHERE
ST_Intersects(ST_PointOnSurface(c.the_geom), r.the_geom);
  • 1
    @standers To expand on what causes the centroid issue in the first place, a centroid is the geometric center of a shape - in other words the center of its extent. Your top example is a multipart-poly, so the centroid is in the middle between the two parts. Your other two are L shapes, and longer legs pull the center out further and further from the crook of the L to the point it's outside the shape. – Chris W Jun 3 '14 at 3:21

Fresh on the QGIS plugin Repository:

http://plugins.qgis.org/plugins/SelectWithin/

This will do a Centroid within query or a Point on Surface within query.

As far as selecting something within a region, I have found the following PostGIS functions work pretty well together:

ST_Within: https://postgis.net/docs/ST_Within.html

ST_Centroid: https://postgis.net/docs/ST_Centroid.html

    SELECT * FROM countries c, regions r
    WHERE ST_Within(ST_Centroid(c.geom),r.geom);

This functionality in PostGIS is similar to the ArcMap GUI process of Select by Location with the spatial selection method being Have their centroid in.

While looking for a QGIS solution, apparently there is a Within plugin that acts similarly to ArcMap: https://plugins.qgis.org/plugins/SelectWithin/

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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