5

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.

Improve this question
9
  • 1
    This yielded similar results to ST_Within. i.imgur.com/B6LqQKe.png
    – standers
    Jun 1, 2014 at 17:32
  • 1
    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, 2014 at 18:06
  • 1
    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, 2014 at 19:54
  • 1
    While this page is ArcGIS help, I think it would help you understand why certain relationships (such as Within) don't work. And possibly find one that would, but I doubt it if your data is misaligned. Selection by attribute would side-step that issue, but of course if you need aligned polygons that's a different question from selecting them.
    – Chris W
    Jun 1, 2014 at 20:05
  • 1
    The lesson here is that we may be able to fall back on traditional relational database-fu to get what we want rather than executing expensive spatial queries.
    – standers
    Jun 1, 2014 at 20:21

3 Answers 3

Reset to default
8

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);
Improve this answer
1
  • 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, 2014 at 3:21
1

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.

Improve this answer
0

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/

Improve this answer

Your Answer

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

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