2

I'm using ST_SubDivide() to speed up some complex calculations to answer a question. Yet I'm noticing there is a different boundary on the geometry that results from ST_SubDivide(). Essentially, I have a query that works reasonably well until ST_SubDivide(). Then it intersects with other polygons that it wouldn't otherwise intersect with. I'm running an query and joining with ST_Intersects in my actual app.

# SELECT gid, ST_Area(ST_Union(geom)) - ST_Area(state.geog::geometry) 
FROM census.state
JOIN (
  SELECT
    gid,
    ST_SubDivide(geog::geometry,10) AS geom
  FROM census.state
) AS t
  USING (gid)
GROUP BY gid
HAVING NOT ST_Equals(state.geog::geometry, ST_Union(geom));

 gid |       ?column?        
-----+-----------------------
  34 |  1.03028696685215e-13
  43 | -1.18529221992958e-08
  25 | -3.64934146901419e-07
  32 |  8.88178419700125e-16
   8 | -8.51020365288946e-09
  12 |  5.55111512312578e-16
   1 | -7.74611432774464e-08
  10 | -1.11022302462516e-14
  26 | -5.37694003810429e-06
... contd.

For reference, I'm trying this with 2016 TIGER state date. I loaded it with

shp2pgsql -DIGc -s 4326 census/tiger_state/*.shp census.state | psql`

I got the idea from a video cast by Regina Obe

For any geometry you give it, it will chop it up into smaller bits. And this is useful for performance reasons. For example, if you have a huge state it's often useful to chop that into smaller polygons. So when you do intersects the number of points that it has to be dealt with are a lot fewer. So here is an example where I take four states, so I have four rows and then I pass it through the SubDivide function. [...] So you have four states converted to 186 polygons.

3
  • SnapToGrid postgis.net/docs/ST_SnapToGrid.html may help. However, intersection between polygons from different layers is sensible. If both polygons do not share exactly the same vertices and their borders are curved there are always either gaps or overlaps. You may have success if original layers fit topologically and if you split all the layers in a similar way by using a fixed regular polygon grid for splitting. ST_SubDivide is not good tool for that because it tries to reduce geometries to fixed number of vertices and therefore it splits different layers at different places.
    – user30184
    Jan 5, 2017 at 9:05
  • Hrm. I got the idea from Regina Obe above is the link to the youtube video. How would you go about doing this then -- optimizing large polygons for intersections queries? Jan 5, 2017 at 9:16
  • 2
    It is a great tool for point-in-polygon queries because then nanometers do not count and the whole area of the original geometry anyway belongs to one subdivision. If you compare polygons to polygons I would create a grid layer and use that same grid for splitting all the polygon layers into smaller polygons. See the image of the processed World in gaia-gis.it/spatialite-3.0.0-BETA1/WorldBorders.pdf
    – user30184
    Jan 5, 2017 at 9:27

0

Your Answer

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

Browse other questions tagged or ask your own question.